1 Reply Latest reply on Aug 3, 2005 4:53 PM by 238679

    OraOLEDB error '80004005' (more)

    238679
      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;
      /
        • 1. Re: OraOLEDB error '80004005' (more)
          238679
          FIXED

          1) use cmd.CommandText = "dbo.up_sc888UserLogin2" rather than the Oracle Provider documentation that states that one must use ODBC calling structures (cmd.CommandText = "{call dbo.up_sc888UserLogin2}")

          2) Declare the result set object...
          Set rsUser = Server.CreateObject("ADODB.Recordset")

          3) so that one can set the following properties:
          rsUser.CursorLocation = adUseClient
          rsUser.CursorType = adOpenDynamic

          These changes fixed my problem, now on to the SYNONYM problem...