10 Replies Latest reply on Aug 12, 2009 11:23 AM by 716927

    ORA-06550/PLS-00201 error while configuring data source


      My development platform is Visual Studio 2005, ODAC11g Release 1 (, and Oracle 10g Release 2 database.

      I tried to setup a sqlDatasource in my web page using drag and drop features
      When I configure the sqlDatasource to use a stored procedure inside a package,
      I notice that odp.net uses #0# as separator (instead of .) between package name and procedure name in Configure Data Source dialog box. Then when I clcik the Test Query button, I got following error.

      There was an error executing the query. Please check the syntax of the command and if present, the types and values of the parameters and ensure they are correct.
      ORA-06550: line 1, column 7:
      PLS-00201: identifier 'PK_LOOKUP_TABLE#0#P_LIST' must be declared
      ORA-06550: line 1, column 7
      PL/SQL: Statement ignored

      It looks like odp.net cannot locate PK_LOOKUP_TABLE#0#P_LIST.

      Is there any workaround ?

      Below is the stored procedure code. It compiles and runs without problem.

      PACKAGE BODY pk_lookup_table
      /* Get a list of lookup table name */
      O_CURSOR OUT sys_refcursor )
      l_cur sys_refcursor;
      BEGIN -- executable part starts here
      OPEN l_cur FOR
      SELECT table_name
      FROM all_tab_comments u
      WHERE u.comments LIKE '%Lookup Table%'
      ORDER BY 1;

      o_cursor := l_cur;
      END P_LIST;

      On the sqlDataSource property sheet, if I click the Select Query property, the Command and Parameter Editor will open, then I can change the select command name to PK_LOOKUP_TABLE.P_LIST. Then if I run the application, I get another error.

      Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

      Exception Details: Oracle.DataAccess.Client.OracleException: ORA-06550: line 1, column 7:
      PLS-00306: wrong number or types of arguments in call to 'P_LIST'
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored

      Seems that it can locate the procedure.

      In the Command and Parameter Editor dialog, I can add output parameter, but data type is restricted. There is no REF CURSOR datatype in the dropdown list.
      How can I add output parameter with REF CURSOR type via IDE ?

      Thanks for the time looking into this issue.