Re: Calling Stored Proc with Cursor from ASP

From: Finn Ellebaek Nielsen <fen_at_changegroup.dk>
Date: Wed, 21 Apr 1999 10:37:08 +0100
Message-ID: <7fk2ph$k38$1@news.inet.tele.dk>

Marek Suchomski wrote in message <7fighv$jos$1_at_autumn.news.rcn.net>...
>I'm trying to use ASP to call several stored procedures that return
>ref_cursors. I created a function to call the procedure and return the
>cursor, but I have no idea what to do with it in ASP. What I've got so far
>looks like this:
>
>cmd.CommandText = "{ ? = call cur_pkg.www_func('" & userid & "')}"
>
>This appears to be correct, but how do I get the results into a recordset?
>Please help.
>
>
>
>Marek Suchomski
>suchomski_at_bsr.com
>
>
>

Using RDO 2.0 from Visual Baisc and the Oracle ODBC 8.0.5 driver (the first version to support this) you would have the following code, first the PL/SQL package, then the Visual Basic code:

create or replace package ref_cur_demo as

• Reference cursor type. type ref_cur is ref cursor return emp%rowtype; type ref_cur_weak is ref cursor;
• 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; /

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

I'm sure you can easily adapt the code above to VBScript.

HTH. Finn Received on Wed Apr 21 1999 - 04:37:08 CDT