Oracle Function with IN type as parameter mode.
=============== =============== ==========
[CODE=oracle]create or replace function mydatefun(dt date) return varchar2
as
mdate varchar2(50);
begin
select to_char(dt,'dds pth month year') into mdate from dual;
return mdate;
end;[/CODE]
To call the above Oracle Function from VB.
=============== =============== ==
[CODE=vb]
'general declaration
Dim CON As New ADODB.Connectio n
Dim PR As New ADODB.Parameter
Dim PR1 As New ADODB.Parameter
Private Sub Command1_Click( )
CON.Open "Provider=MSDAO RA.1;Password=D EBASIS;User ID=DEBASIS;Data Source=OM;Persi st Security Info=True"
Dim CMD As New ADODB.Command
Dim RS As ADODB.Recordset
CMD.ActiveConne ction = CON
CMD.CommandType = adCmdText
'DT---This is the datepicker control
CMD.CommandText = "select mydatefun(to_da te('" & DT.Value & "','mm/dd/yyyy')) from dual"
Set RS = CMD.Execute
If RS.EOF = False Then
If Not IsNull(RS(0)) Then
Text2.Text = RS(0)
Else
Text2.Text = "Invalid date"
End If
End If
CMD.Cancel
CON.Close
End Sub[/CODE]
=============== =============== ==========
[CODE=oracle]create or replace function mydatefun(dt date) return varchar2
as
mdate varchar2(50);
begin
select to_char(dt,'dds pth month year') into mdate from dual;
return mdate;
end;[/CODE]
To call the above Oracle Function from VB.
=============== =============== ==
[CODE=vb]
'general declaration
Dim CON As New ADODB.Connectio n
Dim PR As New ADODB.Parameter
Dim PR1 As New ADODB.Parameter
Private Sub Command1_Click( )
CON.Open "Provider=MSDAO RA.1;Password=D EBASIS;User ID=DEBASIS;Data Source=OM;Persi st Security Info=True"
Dim CMD As New ADODB.Command
Dim RS As ADODB.Recordset
CMD.ActiveConne ction = CON
CMD.CommandType = adCmdText
'DT---This is the datepicker control
CMD.CommandText = "select mydatefun(to_da te('" & DT.Value & "','mm/dd/yyyy')) from dual"
Set RS = CMD.Execute
If RS.EOF = False Then
If Not IsNull(RS(0)) Then
Text2.Text = RS(0)
Else
Text2.Text = "Invalid date"
End If
End If
CMD.Cancel
CON.Close
End Sub[/CODE]