DevOps, CI/CD and Automation

Immersed in an SQL-Server to Oracle conversion project with many SQL-Server stored procedures that return record sets. I am

attempting to establish the proper method of returning record sets to the .ASP code so as to minimize the changes required to

that code. Conversion of SQL-Server stored procedures to Oracle PL/SQL is progressing smootly.

PROBLEM: OraOLEDB error '80004005' when calling Oracle stored procedures that return result sets from .ASP

Anyone have any ideas what I can do to fix this?

TRIED:
1) Granting Read/Write/Execute on Oracle Home directory on Web Server
2) Modifying Windows Registry to point to proper Oracle Home .dll's for latest 10g Client
3) Moving the REF CURSOR parameter in the stored procedure to the end of the parameter list


ENVIROMENT:
Oracle 10.1.0.2 Server
Oracle Provider for OLE DB release 10.1
Microsoft IIS Server 5.0 (Win 2000)

CODE:

<%@LANGUAGE="VBSCRIPT"%>
<%


SysCatConnString = "Provider=OraOLEDB.Oracle;" & _
"Data Source=BPDB;" & _
"User ID=utst;" & _
"Password=cat;" & _
"PLSQLRSet=1;"


Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open SysCatConnString

If objConnection.Errors.Count = 0 Then
Response.Write "<br>NO connection errors"
End If

connState = objConnection.State
Response.Write "<br>connState = " & connState

set cmd = Server.CreateObject("ADODB.Command")

cmd.ActiveConnection = objConnection

cmd.Parameters.Append cmd.CreateParameter("p_Application_ID", adSmallInt, adParamInput, , 2)
cmd.Parameters.Append cmd.CreateParameter("p02_Userid", adBSTR, adParamInput, 4, "test")
cmd.Parameters.Append cmd.CreateParameter("p03_Password", adBSTR, adParamInput, 4, "test")
cmd.Parameters.Append cmd.CreateParameter("spRetVal", adSmallInt, adParamOutput)

cmd.CommandText = "{ CALL dbo.up_sc888UserLogin2(?,?,?,?) }"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 0
cmd.Prepared = true


set rsUser = server.CreateObject("ADODB.Recordset")
Set rsUser = cmd.Execute


ERROR MESSAGE:

At cmd.Execute I continuously receive:

NO connection errors
connState = 1

OraOLEDB error '80004005'

Unspecified error

/testing.asp, line 39

NOTES:

I can execute other stored procedures successfully, but the problem is that I cannot get past this issue with calling

stored procedures that return record sets (as REF CURSORs).

ORACLE 10g Windows STORED PROCEDURE CODE:

PACKAGE dbo AS

TYPE ref_Return IS REF CURSOR;

PROCEDURE up_sc888UserLogin2 (o_RetVal OUT ref_Return, p_Application_ID IN NUMBER, p_Userid IN VARCHAR2, p_Password

IN VARCHAR2, p_ErrorCode OUT NUMBER);

END dbo;
/

CREATE OR REPLACE PACKAGE BODY dbo AS

PROCEDURE up_sc888UserLogin2(

o_RetVal OUT ref_Return,
p_Application_ID IN NUMBER,
p_Userid IN VARCHAR2,
p_Password IN VARCHAR2,
p_ErrorCode OUT NUMBER)

IS
BEGIN

INSERT INTO testing (p_UserID, p_Password) VALUES (p_UserID, p_Password);
INSERT INTO testing (p_UserID, p_Password) VALUES ('x', 'x');
COMMIT;

OPEN o_RetVal FOR
SELECT Person_ID, AccessControl
FROM tPerson, tAccessRights
WHERE UserID = 'dherrin'
AND tAccessRights.Value = tPerson.AccessRights;

p_ErrorCode := 0;


END up_sc888UserLogin2;

END dbo;
/