Re: PL/SQL Stored Procedure and ASP ADO calls

From: Finn Ellebaek Nielsen <fen_at_changegroup.dk>
Date: Wed, 31 Mar 1999 02:29:15 +0100
Message-ID: <7drq8h$au9$1@news.inet.tele.dk>

Rodgers, Tony (CEI-Atlanta) wrote in message <529B47EE34C4D1118D0800A0C99ABC98630A18_at_EATL0S04>...
>I'm trying to create some stored procedures to return full recordsets of
>SELECT statements in stored procedures to ADO calls in ASP. I don't
>know much about the web end of all this, and I know how I could return
>single rows of data back to ADO when called, but not sure how I can
>return a recordset. My impression is that I might be able to use
>something like a cursor variable, but then it appears that only other
>Oracle client products know how to utilize a cursor variable.
>
>Has anyone done this and wouldn't mind sharing the technique from the
>PL/SQL side? (Wouldn't hurt to see how the procedure is called in ADO
>too I suppose).
>
>Thanks!
>

Here's an example using RDO to call a stored procedure returning a REF CURSOR. You will have to use Oracle's ODBC driver 8.0.5 or later (I'm quite sure it's not supported by Microsoft's ODBC for Oracle) and I've only tested it with Oracle 8.0.5.

The following is the stored procedure:

create or replace package ref_cur_demo as

• Reference cursor type. type ref_cur is ref cursor return emp%rowtype;
• Function returning reference cursor. function func(ename in varchar2) return ref_cur;
• Procedure returning reference cursor
• in IN OUT parameter. procedure proc(ename in varchar2, result in out ref_cur); end; /

create or replace package body ref_cur_demo as   function func(ename in varchar2) return ref_cur is

    result ref_cur;

  begin

• Reuse functionality of procedure. proc(ename, result);

    return(result);
  end;

  procedure proc(ename in varchar2, result in out ref_cur) is

  begin

• Open reference cursor for all
• employees with names matching
• the ENAME parameter. Notice use of
• PROC.ENAME for scoping. open result for select * from emp where ename like proc.ename order by ename; end; end; /

And here we have the VB5 RDO code:

Option Explicit

Dim ResultSet As rdoResultset
Dim DataBase As rdoConnection
Dim Query As rdoQuery
Dim RDOEnv As rdoEnvironment

Private Sub Form_Load()
  Set RDOEnv = rdoEnvironments(0)

                                        ' Set username, password and

connect.
  RDOEnv.UserName = "scott"
  RDOEnv.Password = "tiger"
  Set DataBase = RDOEnv.OpenConnection(dsName:="Oracle 8.0", _

      prompt:=rdDriverNoPrompt)

                                        ' Create call to stored procedure.
  Set Query = DataBase.CreateQuery("Query", _
      "{ ? = call ref_cur_demo.func('S%') }")
                                        ' Call stored procedure and open

result.
  Set ResultSet = Query.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)

  If Not ResultSet Is Nothing Then

                                        ' We have a result, iterate all

rows.

    While Not ResultSet.EOF

      Debug.Print CStr(ResultSet!EMPNO), ResultSet!ENAME, ResultSet!JOB, _
          ResultSet!MGR, CStr(ResultSet!HIREDATE), CStr(ResultSet!SAL), _
          CStr(IIf(IsNull(ResultSet!COMM), 0, ResultSet!COMM)), _
          CStr(ResultSet!DEPTNO)
      ResultSet.MoveNext

    Wend
  End If
  ResultSet.Close
End Sub

Private Sub Form_Unload(Cancel As Integer)   DataBase.Close
End Sub

Hope this helps.

Finn Received on Tue Mar 30 1999 - 19:29:15 CST