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
