Oracle stored procedures returning ADO recordsets

Calling stored procedures from ADO

Calling stored procedures from Visual Basic (or VBA or VBScript) is preferable to embedding SQL in directly into the Visual Basic Code. In addition, being able to process the output from a stored procedure as an Active Data Objects (ADO) recordset adds considerable flexibility. The main aim of this example is to show a means of handling the stored procedure call, including a mix of input and output parameters of various types.

This example shows a Visual Basic program calling a stored procedure contained within an Oracle PL/SQL package. The VB code could equally well be embedded as VBScript in an Active Server Page in order to access the stored procedure from a web application. The VB code uses the Microsoft OLE_DB provider for Oracle to connect to the database. This component is included in the Microsoft Data Access Components (MDAC) distribution which may be downloaded from the Microsoft Web Site.

The example was created and run with VB V6.0 and Oracle V8.1.5.

To see an example using java and jdbc click here

Cut and paste the text from each of the text areas below. An easy way to do this is to click in the text area, use Ctrl-a to highlight all the text, then Ctrl-c to copy it to the clipboard.

First .. create some test data in the database
/* ** Create a table for testing */ --DROP TABLE EMPLOYEE CASCADE CONSTRAINTS ; CREATE TABLE EMPLOYEE ( EMPLOYEE_ID NUMBER (4) NOT NULL, LAST_NAME VARCHAR2 (15), FIRST_NAME VARCHAR2 (15), MIDDLE_INITIAL VARCHAR2 (1), JOB_ID NUMBER (3), MANAGER_ID NUMBER (4), HIRE_DATE DATE, SALARY NUMBER (7,2), COMMISSION NUMBER (7,2), DEPARTMENT_ID NUMBER (2), PRIMARY KEY ( EMPLOYEE_ID ) ) ; CREATE INDEX EMPLOYEE_IX01 ON EMPLOYEE(HIRE_DATE); INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 7369, 'SMITH', 'JOHN', 'Q', 667, 7902, TO_Date( '12/17/1984', 'MM/DD/YYYY') , 800, NULL, 20); INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 7499, 'ALLEN', 'KEVIN', 'J', 670, 7698, TO_Date( '02/20/1985', 'MM/DD/YYYY') , 1600, 300, 30); INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 7505, 'DOYLE', 'JEAN', 'K', 671, 7839, TO_Date( '04/04/1985', 'MM/DD/YYYY') , 2850, NULL, 13); INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 7506, 'DENNIS', 'LYNN', 'S', 671, 7839, TO_Date( '05/15/1985', 'MM/DD/YYYY') , 2750, NULL, 23); INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 7507, 'BAKER', 'LESLIE', 'D', 671, 7839, TO_Date( '06/10/1985', 'MM/DD/YYYY') , 2200, NULL, 14); INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 7521, 'WARD', 'CYNTHIA', 'D', 670, 7698, TO_Date( '02/22/1985', 'MM/DD/YYYY') , 1250, 500, 30); INSERT INTO EMPLOYEE ( EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID ) VALUES ( 7555, 'PETERS', 'DANIEL', 'T', 670, 7505, TO_Date( '03/31/1985', 'MM/DD/YYYY') , 1250, 300, 13); COMMIT;

Next ... create the PL/SQL package


/* ** Create packaged procedure */ CREATE OR REPLACE PACKAGE Adodemo_Pkg IS TYPE resultset_typ IS REF CURSOR; -- Procedure to show the return of a ref cursor appearing as an ADO recordset in vb PROCEDURE GetEmployees(i_id IN EMPLOYEE.employee_id%TYPE -- number input ,i_hiredate_s IN EMPLOYEE.hire_date%TYPE -- date input ,i_hiredate_f IN EMPLOYEE.hire_date%TYPE -- date input ,o_resultset OUT resultset_typ -- 'recordset' output ,o_error OUT VARCHAR2 -- error output ); END Adodemo_Pkg; / CREATE OR REPLACE PACKAGE BODY Adodemo_Pkg IS -- Procedure to show the return of a ref cursor appearing as an ADO recordset in vb PROCEDURE GetEmployees(i_id IN EMPLOYEE.employee_id%TYPE -- number input ,i_hiredate_s IN EMPLOYEE.hire_date%TYPE -- date input ,i_hiredate_f IN EMPLOYEE.hire_date%TYPE -- date input ,o_resultset OUT resultset_typ -- 'recordset' output ,o_error OUT VARCHAR2 -- error output ) IS BEGIN -- If inputs are NULL then all rows are returned OPEN o_resultset FOR SELECT employee_id, last_name, hire_date, salary, commission FROM EMPLOYEE WHERE employee_id = NVL(i_id,employee_id) AND hire_date <= NVL(i_hiredate_s,hire_date) AND hire_date >= NVL(i_hiredate_f,hire_date); o_error := NULL; EXCEPTION WHEN OTHERS THEN o_error := SQLERRM; END GetEmployees; END Adodemo_Pkg; /

*If necessary use this script to test the package from SQLPlus**
/* Test script ** In SQLPlus: SET SERVEROUTPUT ON before running / DECLARE i_id NUMBER; i_hiredates DATE; i_hiredatef DATE; o_resultset Adodemo_Pkg.resultset_typ; o_error VARCHAR2(256); TYPE employee_rec_typ IS RECORD ( employee_id EMPLOYEE.employee_id%TYPE, name EMPLOYEE.LAST_NAME%TYPE, hire_date EMPLOYEE.hire_date%TYPE, salary EMPLOYEE.salary%TYPE, commission EMPLOYEE.commission%TYPE ); employee_rec employee_rec_typ; BEGIN --i_id := 7555; i_hiredates := TO_DATE('01011985','DDMMYYYY'); i_hiredatef := TO_DATE('31031985','DDMMYYYY'); Adodemo_Pkg.getemployees ( i_id, i_hiredates, i_hiredatef, o_resultset, o_error ); dbms_output.put_line('Error:' \|\| NVL(o_error,'NULL')); LOOP FETCH o_resultset INTO employee_rec ; EXIT WHEN o_resultset%NOTFOUND; dbms_output.put_line(employee_rec.employee_id \|\| ':' \|\| employee_rec.name \|\| ':' \|\| employee_rec.hire_date); END LOOP; CLOSE o_resultset; END;

Step through the following VB code and view the results in the immediate window


Option Explicit Sub Main() Dim adoConn As ADODB.Connection Dim adoCmd As ADODB.Command Dim adoRS As ADODB.Recordset Dim adoField As ADODB.Field Dim adoParameter As ADODB.Parameter Dim strError As String Set adoConn = New ADODB.Connection adoConn.ConnectionString = "PROVIDER=MSDAORA;USER ID=demo;PASSWORD=demo;DATA SOURCE=HOME8I;" adoConn.Open Set adoCmd = New ADODB.Command With adoCmd .ActiveConnection = adoConn .CommandText = "{CALL ADODEMO_PKG.GetEmployees(?,?,?,{resultset 0,o_resultset},?)}" .CommandType = adCmdText ' Not adCmdStoredProc !!! .Parameters.Append .CreateParameter("ID", adInteger, adParamInput, , Null) ' 1st parameter is NULL .Parameters.Append .CreateParameter("HiredateS", adDate, adParamInput, , DateSerial(1985, 1, 1)) ' Avoid locale issues .Parameters.Append .CreateParameter("HiredateF", adDate, adParamInput, , DateSerial(1985, 3, 31)) ' Avoid locale issues .Parameters.Append .CreateParameter("Error", adVarChar, adParamOutput, 256) Set adoRS = .Execute ' Dump the input and output parameters Debug.Print "Parameter count:" & .Parameters.Count For Each adoParameter In .Parameters Debug.Print adoParameter.Name & ":" & adoParameter.Value Next adoParameter End With ' Dump the recordset While Not adoRS.EOF For Each adoField In adoRS.Fields Debug.Print adoField.Value; " "; Next adoField adoRS.MoveNext Wend adoRS.Close Set adoRS = Nothing Set adoCmd = Nothing adoConn.Close Set adoConn = Nothing End Sub