return BOTH a recordset and an OUT par from Oracle Stored Proc using ASP and ADO

From: Carlo <carlogol_at_virgilio.it>
Date: 25 Aug 2004 08:01:49 -0700
Message-ID: <98778b95.0408250701.2adc08dd@posting.google.com>

Hi all

I followed the sample code in paragraph "Retrieving ADO Recordsets using Reference Cursors" (taken from "Professional ASP Data Access" - see

and it works fine.

The only thing is: what if I would like to have also an Output parameter? I tried in several ways but I was not able to retrieve its value.

I mean: the stored procedure compiled and worked if called by anonymous PL/SQL code. Nor I got any error from my asp page. Simply: I could loop thru the recordset, but I was not able to retrieve the value of this output parameter.

Can you help me please?

Here is the procedure:
CREATE OR REPLACE PACKAGE PKG_LID_GUI_UTILS AS

	TYPE t_REF_CURSOR IS REF CURSOR;
	PROCEDURE PRC_TEST(
		o_numRet_code OUT varchar2,
		o_curTest     OUT t_REF_CURSOR
		);

END PKG_LID_GUI_UTILS;
/
CREATE OR REPLACE PACKAGE BODY PKG_LID_GUI_UTILS AS

	PROCEDURE PRC_TEST(
		o_numRet_code OUT varchar2,
		o_curTest     OUT t_REF_CURSOR
		)
	IS
	BEGIN	
			OPEN o_CurTest FOR
				select error_log_id 
				from lid_transaction_error_log 
				where group_id = 33 and trunc(error_date) = to_date('25-00-2004',

'dd-mm-yyyy');                         

                        o_numRet_Code := 'back from proc';

        END;
END PKG_LID_GUI_UTILS;
/

• asp file
  ...
  Dim objConnection Dim objCommand Dim objRecordSet Dim objNameParam Dim o_numRet_Code Set objConnection = Server.CreateObject("ADODB.Connection") objConnection.ConnectionString = Application("CONN_STRING") &_ ";PLSQLRSet=1;" objConnection.Open
  ...
  Set objCommand = Server.CreateObject("ADODB.Command") Set objCommand.ActiveConnection = objConnection o_numRet_Code = "-1"

objCommand.CommandText = "{call PKG_LID_GUI_UTILS.PRC_TEST(?)}" Set objNameParam = objCommand.CreateParameter("o_numRet_Code", adBSTR, adParamOutput, 10, o_numRet_Code)
objCommand.Parameters.Append objNameParam Set objRecordSet = objCommand.Execute()

Response.Write "<p>1 out + 1 ref cursor: o_numRet_Code:+++" & objCommand.Parameters(0) & "----------"
...
Received on Wed Aug 25 2004 - 10:01:49 CDT