Re: OLE ,Oracle, ans Stored Procedures returning result sets

Okay, looking at your example, you are using the Oracle OLEDB provider, as opposed to the microsoft ADO provider that we are using( Currently version 2.0).

This no doubt works and should work on Oracle 7.3.4.3. Can you also return a result set base don pl/sql tables. Obviously, you can't do this with a Ref Cursor.

Thanks for the Info

Klaus Zeuch wrote in message <8mptuj$jil$10$1_at_news.t-online.com>...
>With 8.1.6 the following example is provided by Oracle (requires the
>well-known Scott schema):
>
>a) PL/SQL Package
>
>CREATE OR REPLACE PACKAGE Employees AS
> TYPE empcur IS REF CURSOR;
>
> PROCEDURE GetEmpRecords(p_cursor OUT empcur,
> indeptno IN NUMBER,
> p_errorcode OUT NUMBER);
>
> FUNCTION GetDept(inempno IN NUMBER,
> p_errorcode OUT NUMBER)
> RETURN empcur;
>END Employees;
>
>/
>
>CREATE OR REPLACE PACKAGE BODY Employees AS
>
> PROCEDURE GetEmpRecords(p_cursor OUT empcur,
> indeptno IN NUMBER,
> p_errorcode OUT NUMBER) IS
> BEGIN
> p_errorcode := 0;
> OPEN p_cursor FOR
> SELECT *
> FROM emp
> WHERE deptno = indeptno
> ORDER BY empno;
>
> EXCEPTION
> WHEN OTHERS THEN
> p_errorcode:= SQLCODE;
>
> END GetEmpRecords;
>
>
>
>
> FUNCTION GetDept(inempno IN NUMBER,
> p_errorcode OUT NUMBER)
> RETURN empcur IS
> p_cursor empcur;
> BEGIN
> p_errorcode := 0;
> OPEN p_cursor FOR
> SELECT deptno
> FROM emp
> WHERE empno = inempno;
> RETURN (p_cursor);
>
> EXCEPTION
> WHEN OTHERS THEN
> p_errorcode:= SQLCODE;
>
> END GetDept;
>
>END Employees;
>
>/
>
>
>b) Code for VB 6 (adjust the network alias db816!):
>
>Private Sub Command1_Click()
>
>Dim Oracon As ADODB.Connection
>Dim recset As New ADODB.Recordset
>Dim cmd As New ADODB.Command
>Dim param1 As New ADODB.Parameter
>Dim param2 As New ADODB.Parameter
>Dim objErr As ADODB.Error
>Dim Message, Title, Default, EmpNoValue
>
>Message = "Enter an employee number (5000 - 9000)"
>Title = "Choose an Employee"
>Default = "7654"
>
>On Error GoTo err_test
>
>EmpNoValue = InputBox(Message, Title, Default)
>If EmpNoValue = "" Then Exit Sub
>
>If EmpNoValue < 5000 Or EmpNoValue > 9000 Then EmpNoValue = 7654
>
>Set Oracon = CreateObject("ADODB.Connection")
>Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
> "Data Source=db816;" & _
> "User ID=scott;" & _
> "Password=tiger;" & _
> "PLSQLRSet=1;"
>Oracon.Open
>
>Set cmd = New ADODB.Command
>Set cmd.ActiveConnection = Oracon
>
>Set param1 = cmd.CreateParameter("param1", adSmallInt, adParamInput, ,
>EmpNoValue)
>cmd.Parameters.Append param1
>Set param2 = cmd.CreateParameter("param2", adSmallInt, adParamOutput)
>cmd.Parameters.Append param2
>
>cmd.CommandText = "{CALL Employees.GetDept(?, ?)}"
>Set recset = cmd.Execute
>MsgBox "Number: " & EmpNoValue & " Dept: " & recset.Fields("deptno").Value
>
>Exit Sub
>
>err_test:
> MsgBox Error$
> For Each objErr In Oracon.Errors
> MsgBox objErr.Description
> Next
> Oracon.Errors.Clear
> Resume Next
>
>End Sub
>
>
>Command1.caption = "Get Employee Records by Dept"
>
>Hth
>
>Klaus
>"Keith Jamieson" <Keith.Jamieson_at_phoenix.ie> schrieb im Newsbeitrag
>news:8mpfge$hce$1_at_kermit.esat.net...
>> We are currently using Microsoft ADO 2.0 to Connect to Oracle 7.3.4.3.
>> We have established that we can call a procedure or package using OLE or
>> ODBC, using PL/SQL tables.
>>
>> However, if we want to return a result set, from a package, then
>> we at present can only use the ODBC driver, as in all the examples
>> provided in the MSDN, or on various Web Sites.
>>
>> I need a mechanism to enable me to connect to a stored procedure using
 OLE
>> which returns a result set.
>> Does anyone have any examples of how I can do this. I am aware that I may
>> have to change the procedures to use Ref Cursors, but this should be a
 minor
>> issue, unless of course anyone else knows better.
>>
>> TIA
>>
>> Keith
>>
>>
>>
>>
>>
>
>
Received on Wed Aug 09 2000 - 00:00:00 CDT