2 Replies Latest reply on Jan 18, 2005 3:09 PM by 82532

    Problem call stored procedure from asp using oo4o

    436386
      I am having a problem calling a pl/sql stored procedure from ASP.

      Attached is the code. Any help would be appreciated. There are no errors returned on the page as far as I can tell.

      Sub add_cc_rec(p_location_rental_object_num, p_start_date, p_end_date, p_rate_code, p_quantity)
           'On Error Resume Next     

           Dim msg, stat, p_available, p_free_sell, p_cap_level, p_status_code, p_error_code
           Dim OraDatabase, PlSqlStmt

           Response.Write "In Function add_cc_rec<br>"

           msg = ""
           stat = ""
           p_available = "Y"
           p_free_sell = "Y"
           p_cap_level = "0"
           p_status_code = 2
           p_error_code = ""

           Set OraSession = Server.CreateObject("OracleInProcServer.XOraSession")
           Response.Write "Created OraSession<br>"
           Set OraDatabase = OraSession.OpenDatabase(ORADB, RUUID &"/"& RUPWD, 0)
           'Set OraDatabase = OraSession.DbOpenDatabase(ORADB, RUUID &"/"& RUPWD,cint(0))
           'Set OraDatabase = OraSession.DbOpenDatabase(ORADB, UID &"/"& PWD,cint(0))
           Response.Write "Created OraDatabase with the following parameters<br>"
           Response.Write "Database is " & ORADB & "<br>"
           Response.Write "UserId is " & RUUID & "<br><br>"

           OraDatabase.Parameters.Add "p_location_rental_object_num", p_location_rental_object_num, ORAPARM_INPUT, ORATYPE_VARCHAR2
           OraDatabase.Parameters.Add "p_available", p_available, ORAPARM_INPUT, ORATYPE_VARCHAR2
           OraDatabase.Parameters.Add "p_start_date", p_start_date, ORAPARM_INPUT, ORATYPE_VARCHAR2
           OraDatabase.Parameters.Add "p_end_date", p_end_date, ORAPARM_INPUT, ORATYPE_VARCHAR2
           OraDatabase.Parameters.Add "p_free_sell", p_free_sell, ORAPARM_INPUT, ORATYPE_VARCHAR2
           OraDatabase.Parameters.Add "p_quantity", p_quantity, ORAPARM_INPUT, ORATYPE_VARCHAR2
           OraDatabase.Parameters.Add "p_cap_level", p_cap_level, ORAPARM_INPUT, ORATYPE_VARCHAR2
           OraDatabase.Parameters.Add "p_rate_code", p_rate_code, ORAPARM_INPUT, ORATYPE_VARCHAR2
           OraDatabase.Parameters.Add "p_status_code", p_status_code, ORAPARM_OUTPUT, ORATYPE_NUMBER
           OraDatabase.Parameters.Add "p_error_code", p_error_code, ORAPARM_OUTPUT, ORATYPE_VARCHAR2

           Response.Write "Calling PL/SQL procedure cc_insert <br>"
           Set PlSqlStmt = OraDatabase.CreateSQL("Begin RATELINK_CAPACITY_CONTROL.cc_insert(:p_location_rental_object_num, :p_available, :p_start_date, :p_end_date, :p_free_sell, :p_quantity, :p_cap_level, :p_rate_code, :p_status_code, :p_error_code); end;", ORASQL_DEFAULT)

           Response.Write "PL/SQL procedure cc_insert has been called <br>"

           Response.Write "Status code from PL/SQL procedure cc_insert (" & OraDatabase.Parameters("p_status_code").value & ")<br>"
           Response.Write "Error code from PL/SQL procedure cc_insert (" & OraDatabase.Parameters("p_error_code").value & ")<br>"
           'Response.Write "Error code from PL/SQL procedure cc_insert (" & p_error_code & ")<br>"

           OraDatabase.Parameters.Remove "p_location_rental_object_num"
           OraDatabase.Parameters.Remove "p_available"
           OraDatabase.Parameters.Remove "p_start_date"
           OraDatabase.Parameters.Remove "p_end_date"
           OraDatabase.Parameters.Remove "p_free_sell"
           OraDatabase.Parameters.Remove "p_quantity"
           OraDatabase.Parameters.Remove "p_cap_level"
           OraDatabase.Parameters.Remove "p_rate_code"
           OraDatabase.Parameters.Remove "p_status_code"
           OraDatabase.Parameters.Remove "p_error_code"

           Set OraSession = Nothing
           Set OraDatabase = Nothing
      End Sub
        • 1. Re: Problem call stored procedure from asp using oo4o
          436386
          I found some code on the forum to check for errors for the Oracle server and print the info.

          Here is the last error message from the Oracle Server.

          (ORA-06550: line 1, column 7: PLS-00201: identifier 'RATELINK_CAPACITY_CONTROL.CC_INSERT' must be declared ORA-06550: line 1, column 7: PL/SQL:

          Now, two questions

          1 - Can I clear errors on the OraDatabase class. How do I do that?

          2. Is this a client side or server side error? If this is a client side error, how do I resolve it?

          • 2. Re: Problem call stored procedure from asp using oo4o
            82532
            1 - Can I clear errors on the OraDatabase class. How do
            I do that?
            They store the last error, should clear itself.
            2. Is this a client side or server side error? If this
            is a client side error, how do I resolve it?
            Server side error only. Make sure the user you connect as can see 'RATELINK_CAPACITY_CONTROL.CC_INSERT' and has execute permissions on it. You may need to qualify it with a schema name if the connected user is not the schema owner.