Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2005
    Posts
    9

    Unanswered: Get return values from recordset

    I posted this message from this site, but I was using the
    Oracle forum. I hope that this ASP category is more
    appropriate for my problem. So I re-post this here and have
    an additional question related to ASP only.

    I have the following ASP code. Call an Oracle procedure
    named myprocedure(), in package "pkg" belongs to schema
    "myschema", and this procedure returns a recordset. The
    Oracle procedure has been working for years. The Oracle
    procedure returns a recordset specified in the second
    parameter called my_recordset.

    --------------------- ASP code in myfile.asp --------------------------
    Set Recordset1 = Server.CreateObject("ADODB.Recordset")
    Recordset1.ActiveConnection = MM_xyz_STRING

    Recordset1.Source = "{call myschema.pkg.myprocedure('" + Replace(Recordset1__session_user, "'", "'
    '") + "',?)}"

    Recordset1.CursorType = 0
    Recordset1.CursorLocation = 2
    Recordset1.LockType = 1
    Recordset1.Open() ' error points to this line - line 100
    Recordset1_numRows = 0
    ------------ Oracle procedure myprocedure ---------

    procedure myprocedure(user_in in varchar2, my_recordset out types.cursortype)
    as
    begin
    open my_recordset for
    select name, addr from employee
    .......
    end myprocedure;

    --------Following is the error shown on the web browser -------
    Error Type:
    ADODB.Recordset.1 (0x80004005)
    SQLState: 07001 Native Error Code: 0 [DataDirect][ODBC Oracle Wire Protocol driver]Value has not been specified for parameter 1.
    /dir1/myfile.asp, line 100

    ------------------ ENd of browser error ------------------------

    Note: Line 100 is Recordset1.Open() in myfile.asp

    1. Did I not handle the return recordset properly or something wrong with the ODBC driver?
    2. I used response.end to stop the execution just before the "myprocedure()" got called. The I used this code response.write("user=" & Recordset1__session_user) to display the Recordset1__session_user value. This value was not empty. Please let me know how I can display the values returned from the recordset.

    Thanks you so much!!!

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I haven't gone to oracle from asp so I'm not going to be much help I'm afraid,... but to me it looks like it is complaining because the parameter values are not getting set properly here...
    Code:
    Recordset1.Source = "{call myschema.pkg.myprocedure('" + Replace(Recordset1__session_user, "'", "'
    '") + "',?)}"
    You might want to try using a command object rather then a recordset to call the query (you can pass the result of the command back to a recordset of course).

  3. #3
    Join Date
    Jul 2005
    Posts
    9

    Return value from recordset

    Thanks for your time!

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Did using the command object work??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •