This thread contains some of the sample code showing the method of executing Oracle stored procedures and functions from VB .
Hope the user finds them useful.
Oracle Procedure with only IN type as parameter mode.
========================================
- CREATE OR REPLACE PROCEDURE DEPTINS
-
(
-
DNO DEPT.DEPTNO%TYPE,
-
DN DEPT.DNAME%TYPE,
-
LC DEPT.LOC%TYPE
-
)
-
AUTHID CURRENT_USER
-
IS
-
BEGIN
-
INSERT INTO DEPT VALUES(DNO,DN,LC);
-
DBMS_OUTPUT.PUT_LINE('ONE ROW INSERTED......!');
-
EXCEPTION
-
WHEN DUP_VAL_ON_INDEX THEN
-
RAISE_APPLICATION_ERROR(-20002,'DUPLICATE VALUE......!');
-
WHEN OTHERS THEN
-
RAISE_APPLICATION_ERROR(-20001,'SOME OTHER ERROR ......!');
-
END;
To call the above Oracle procedure from Vb.
================================
-
'general declaration
-
Dim CON As New ADODB.Connection
-
Dim RS As New ADODB.Recordset
-
Dim PR As New ADODB.Parameter
-
Dim PR1 As New ADODB.Parameter
-
Dim PR2 As New ADODB.Parameter
-
Dim CMD As New ADODB.Command
-
-
Private Sub Command1_Click()
-
On Error GoTo MYERR
-
CON.Open "Provider=MSDAORA.1;Password=DEBASIS;User ID=DEBASIS;Data Source=DAS;Persist Security Info=True"
-
CMD.ActiveConnection = CON
-
CMD.CommandType = adCmdStoredProc
-
CMD.CommandText = "DEPTINS"
-
Set PR = CMD.CreateParameter("DNO", adNumeric, adParamInput, 2, Val(T1.Text))
-
CMD.Parameters.Append PR
-
Set PR1 = CMD.CreateParameter("DN", adVarChar, adParamInput, 14, Trim(T2.Text))
-
CMD.Parameters.Append PR1
-
Set PR2 = CMD.CreateParameter("LC", adVarChar, adParamInput, 13, Trim(T3.Text))
-
CMD.Parameters.Append PR2
-
CMD.Execute
-
CON.Close
-
Exit Sub
-
MYERR:
-
I = Val(Mid(Err.Description, 4, 6))
-
If I = -20002 Then
-
MsgBox "DUPLICATE ENTRY"
-
ElseIf I = -20001 Then
-
MsgBox "SOME OTHER ERROR"
-
End If
-
End Sub
