This discussion is archived
3 Replies Latest reply: Jun 22, 2012 11:19 AM by gdarling - oracle RSS

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

595043 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points