3 Replies Latest reply: Jun 22, 2012 1:19 PM by gdarling - oracle RSS

    OraOLEDB error '80040e14' ORA-06550 PLS-00201: stored procedure with REFCUR

    595043
      Hi All,

      We are replacing MSDAORA with OraOLEDB while upgrading Win 2003 to Win 2008 R2 (Oracle version is 11g). But our classis ASP application, that uses stored procedures with REFCURSOR, throwing the following error.

      OraOLEDB error '80040e14'

      ORA-06550: line 1, column 47: PLS-00201: identifier 'CUR_RESULTS' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

      /cntsearch/default.asp, line 100

      The same ASP code is working fine with MSDAORA & Win 2003.

      I really appreciate your help.

      Please post your suggestions.

      Thanks is advance...
        • 1. Re: OraOLEDB error '80040e14' ORA-06550 PLS-00201: stored procedure with REFCUR
          gdarling - oracle
          Can you provide more info?

          This vbs works for me..
          ' create or replace procedure sysdaterefcur(v1 out sys_refcursor)
          ' as begin open v1 for select sysdate from dual; end;
          ' /
          set con = createobject("adodb.connection")
          set cmd = createobject("adodb.command")
          set rst = createobject("adodb.recordset")
          
          con.Open "provider=oraoledb.oracle;data source=orcl;user id=scott;password=tiger;plsqlrset=true"
          cmd.CommandText = "{call sysdaterefcur}"
          Set cmd.ActiveConnection = con
          Set rst = cmd.Execute
          msgbox rst.fields(0).value
          'cleanup
          Greg
          • 2. Re: OraOLEDB error '80040e14' ORA-06550 PLS-00201: stored procedure with REFCUR
            595043
            Thanks Greg.


            Here is the ASP code...
            ----------------------------------------------------------
            Set dbConn = Server.CreateObject("ADODB.Connection")
            dbConn.Open "Provider=OraOLEDB.Oracle;Data Source=DWHOUSE;User Id=XXXXX;Password=XXXXX;PLSQLRSet=True"
            Dim SQL
            Dim SQLORG
            SQL = "{call MND.MY_SEARCH.P_BUS_UNIT_DROP_DOWN({resultset 0, PO_DROP_DOWN_RESULTS})}"
            SQLORG = "{call MND.MY_SEARCH.P_ORG_DROP_DOWN(?,{resultset 0, PO_DROP_DOWN_RESULTS})}"

            set cmd = server.createObject ("ADODB.Command")
            set cmdOrg = server.createObject ("ADODB.Command")

            with cmd
                 set .ActiveConnection = dbConn
            .CommandText = SQL
            .CommandType = 1
            end with

            set businessUnitRs = server.CreateObject ("ADODB.Recordset")
            set businessUnitRs = cmd.execute

            with cmdOrg
                 set .ActiveConnection = dbConn
            .CommandText = SQLORG
            .CommandType = 1
            cmdOrg.Parameters("pi_bus_unit_h_org_cd") = busUnit
            end with

            set orgCodeRs = server.CreateObject ("ADODB.Recordset")
            set orgCodeRs = cmdOrg.execute

            ---------------------------------------------------------------
            And database package code....
            ---------------------------------------------------------------

            MY_SEARCH is a package created in MND schema with the following procedures

            PROCEDURE P_BUS_UNIT_DROP_DOWN(po_drop_down_results IN OUT SYS_REFCURSOR);
            PROCEDURE P_ORG_DROP_DOWN(pi_bus_unit_h_org_cd dw.acctg_tmpl.org_cd%type DEFAULT NULL,PO_DROP_DowN_results IN OUT SYS_REFCURSOR);

            Thanks again...
            • 3. Re: OraOLEDB error '80040e14' ORA-06550 PLS-00201: stored procedure with REFCUR
              gdarling - oracle
              The *{resultset 0, PO_DROP_DOWN_RESULTS}* syntax is specific to Microsoft's OLEDB and ODBC, so you'll need to change the code.

              http://docs.oracle.com/cd/E11882_01/win.112/e17726/using.htm#autoId23

              Hope it helps,
              Greg