how do i call stored procedures held in an oracle database using VB i have an adodb connection and can use sql to access data but cannot call the procedures.
Cheers mark
Printable View
how do i call stored procedures held in an oracle database using VB i have an adodb connection and can use sql to access data but cannot call the procedures.
Cheers mark
Hi,
use this:
PL/SQL Package:
----------------
CREATE OR REPLACE PACKAGE PKG_EMP
IS
TYPE EMP_CUR IS REF CURSOR;
PROCEDURE GetEmpRecords(emp_cursor OUT EMP_CUR);
END PKG_EMP;
/
CREATE OR REPLACE PACKAGE BODY PKG_EMP
IS
PROCEDURE GetEmpRecords(emp_cursor OUT EMP_CUR)
IS
BEGIN
OPEN emp_cursor FOR
SELECT *
FROM emp
ORDER BY empno;
END GetEmpRecords;
END PKG_EMP;
/
VB Code:
----------
Dim Con As New ADODB.Connection
Dim Rst As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Con.Provider = "OraOLEDB.Oracle"
Con.ConnectionString = "PLSQLRSet=1;Data Source=MyOraDb;" & _
"User ID=scott;Password=tiger;"
Con.Open
Cmd.ActiveConnection = Con
Cmd.CommandText = "{CALL PKG_EMP.GetEmpRecords(?)}"
Set Rst1 = Cmd.Execute
Refer to this description of how to call Oracle stored procedures using ADODB
It is written for VBScript executed under IIS, but the principles are the same for VBScript for VB6.