OLE ,Oracle, ans Stored Procedures returning result sets 
Author Message
 OLE ,Oracle, ans Stored Procedures returning result sets

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



Wed, 18 Jun 1902 08:00:00 GMT
 OLE ,Oracle, ans Stored Procedures returning result sets

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


Quote:
> 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



Wed, 18 Jun 1902 08:00:00 GMT
 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

Quote:

>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


>> 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



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Returning result sets in oracle stored procedure (OLEDB)

2. Using oracle stored procedures/functions to return result sets

3. Oracle stored procedure returning a result set

4. Returning result sets in oracle stored procedure (OLEDB)

5. MSSQLServer->Oracle: Identity and returning a result set using oracle procedure or function

6. returning result sets from stored procedures

7. return multiple result sets from a stored procedure

8. Can't update result set returned from stored procedure using RDO

9. Returning result set from function to stored procedure is very slooooooooow

10. best way to return a paged result set from a stored procedure

11. Returning a result set from a Stored Procedure

12. returning result sets from stored procedures


 
Powered by phpBB® Forum Software