Unspecified error when running Oracle 9i package via ASP

From: s_gemberling <s_gemberling_at_yahoo.com>
Date: 12 Sep 2003 12:22:47 -0700
Message-ID: <1e64ee35.0309121122.4f73c22c_at_posting.google.com>


Here is the code that calls the Oracle package:

Function b_GetRecords(sTableName, sRecordName)

on error resume next

dim cmd, objParameter         

Set conn = Server.CreateObject("ADODB.Connection") conn.Open cst_Tasks_ConnectionString
conn.cursorlocation = adUseClient                 

set cmd = server.CreateObject("ADODB.Command") Set cmd.ActiveConnection = conn
cmd.commandtype = adCmdText
cmd.CommandText = "{CALL PRJMGMT.pkgShared.procGetRecords(?, ?)}"                 

Set objParameter = cmd.CreateParameter("vtable_name", adVarChar, adParamInput, 75)
cmd.Parameters.Append objParameter
Set objParameter = cmd.CreateParameter("vrecord_name", adVarChar, adParamInput, 75)
cmd.Parameters.Append objParameter
set objParameter = nothing

cmd.Parameters("vtable_name").Value = sTableName cmd.Parameters("vrecord_name").Value = sRecordName         

set rsRecords = Server.CreateObject("ADODB.Recordset")

rsRecords.CursorLocation = adUseclient
rsRecords.locktype = adLockReadOnly
rsRecords.cursortype = adOpenForwardOnly
	

set rsRecords = cmd.execute         

if err.number = 0 then

	if not(rsRecords.BOF and rsRecords.EOF) then
		b_GetRecords = true
	else
		b_GetRecords = false
	end if
else
	Response.Write err.number & ", " & err.Description
	Response.end
	b_GetRecords = false

end if

'Cleanup
set cmd.ActiveConnection = nothing
set rsRecords.activeconnection = nothing conn.close

End Function

Here is the Package:

CREATE OR REPLACE PACKAGE PRJMGMT.pkgShared AS TYPE CURSOR_TYPE IS REF CURSOR;
        PROCEDURE procGetRecords(vtable_name IN VARCHAR2, vrecord_name IN END pkgShared;
/

CREATE OR REPLACE PACKAGE BODY PRJMGMT.pkgShared AS        

PROCEDURE procGetRecords(vtable_name IN VARCHAR2, vrecord_name IN VARCHAR2, RESULT_SET_1 OUT CURSOR_TYPE)
AS
v_SQL VARCHAR2(500);
BEGIN
--Create the SQl string
v_SQL := 'SELECT * FROM PRJMGMT.' || vtable_name || ' order by ' || vrecord_name;

OPEN RESULT_SET_1 FOR v_SQL;         

END procGetRecords;
END pkgShared;
/

GRANT EXECUTE ON PRJMGMT.pkgShared TO PRJMGMT;
/

When I execute my package in SQL Plus i get the appropriate recordset returned. When I run the procedure thru my ASP page, I get the following error:

-2147467259, Unspecified error, Unspecified Error Received on Fri Sep 12 2003 - 21:22:47 CEST

Original text of this message