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

From: Klaus Zeuch <KZeuch_at_nospamhotmail.com>
Date: 2000/08/08
Message-ID: <8mptuj$jil$10$1_at_news.t-online.com>#1/1

[Quoted] With 8.1.6 the following example is provided by Oracle (requires the well-known Scott schema):

1. 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 Tue Aug 08 2000 - 00:00:00 CEST