Here is the code that calls the Oracle package:
Function b_GetRecords(sT ableName, sRecordName)
on error resume next
dim cmd, objParameter
Set conn = Server.CreateOb ject("ADODB.Con nection")
conn.Open cst_Tasks_Conne ctionString
conn.cursorloca tion = adUseClient
set cmd = server.CreateOb ject("ADODB.Com mand")
Set cmd.ActiveConne ction = conn
cmd.commandtype = adCmdText
cmd.CommandText = "{CALL PRJMGMT.pkgShar ed.procGetRecor ds(?, ?)}"
Set objParameter = cmd.CreateParam eter("vtable_na me", adVarChar,
adParamInput, 75)
cmd.Parameters. Append objParameter
Set objParameter = cmd.CreateParam eter("vrecord_n ame", 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.CreateOb ject("ADODB.Rec ordset")
rsRecords.Curso rLocation = adUseclient
rsRecords.lockt ype = adLockReadOnly
rsRecords.curso rtype = adOpenForwardOn ly
set rsRecords = cmd.execute
if err.number = 0 then
if not(rsRecords.B OF 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.ActiveConne ction = nothing
set rsRecords.activ econnection = nothing
conn.close
End Function
Here is the Package:
CREATE OR REPLACE PACKAGE PRJMGMT.pkgShar ed AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE procGetRecords( vtable_name IN VARCHAR2, vrecord_name IN END
pkgShared;
/
CREATE OR REPLACE PACKAGE BODY PRJMGMT.pkgShar ed 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.pkgShar ed 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
Function b_GetRecords(sT ableName, sRecordName)
on error resume next
dim cmd, objParameter
Set conn = Server.CreateOb ject("ADODB.Con nection")
conn.Open cst_Tasks_Conne ctionString
conn.cursorloca tion = adUseClient
set cmd = server.CreateOb ject("ADODB.Com mand")
Set cmd.ActiveConne ction = conn
cmd.commandtype = adCmdText
cmd.CommandText = "{CALL PRJMGMT.pkgShar ed.procGetRecor ds(?, ?)}"
Set objParameter = cmd.CreateParam eter("vtable_na me", adVarChar,
adParamInput, 75)
cmd.Parameters. Append objParameter
Set objParameter = cmd.CreateParam eter("vrecord_n ame", 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.CreateOb ject("ADODB.Rec ordset")
rsRecords.Curso rLocation = adUseclient
rsRecords.lockt ype = adLockReadOnly
rsRecords.curso rtype = adOpenForwardOn ly
set rsRecords = cmd.execute
if err.number = 0 then
if not(rsRecords.B OF 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.ActiveConne ction = nothing
set rsRecords.activ econnection = nothing
conn.close
End Function
Here is the Package:
CREATE OR REPLACE PACKAGE PRJMGMT.pkgShar ed AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE procGetRecords( vtable_name IN VARCHAR2, vrecord_name IN END
pkgShared;
/
CREATE OR REPLACE PACKAGE BODY PRJMGMT.pkgShar ed 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.pkgShar ed 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
Comment