8 Replies Latest reply on Mar 13, 2015 9:21 PM by Josh-Oracle

    PLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared.

    Marco Gralike

      Attempting a "dryrun" of ORDS. More or less got it now working in 12.1.0.2 but hit a hurdle after attempting

       

      http://localhost:8080/ords/marco/dbjson/latest/

       

      (where "marco" is my database schema "MARCO")

       

      Error message posts in JSON format

       

      {"type":"http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.1","status":500,"title":"ORA-06550: Regel 2, kolom 3:\nPLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared.\nORA-06550: Regel 2, kolom 3:\nPL/SQL: Statement ignored.\n","details":"begin\n DBMS_SODA_ADMIN.LIST_COLLECTIONS(\n P_START_NAME => ?,\n P_RESULTS => ?);\nend;","o:errorCode":"SQL-06550"}

       

      Aka

       

      INFO: Running JSON/REST request GET

      dec 15, 2014 1:48:14 PM oracle.soda.rdbms.impl.OracleDatabaseImpl getMaxLengths

      SEVERE: java.sql.SQLException: ORA-06550: Regel 2, kolom 3:

      PLS-00201: identifier 'DBMS_SODA_ADMIN.GET_PARAMETERS' must be declared.

      ORA-06550: Regel 2, kolom 3:

      PL/SQL: Statement ignored.

       

      dec 15, 2014 1:48:14 PM oracle.soda.rdbms.impl.OracleDatabaseImpl callListCollec

      tions

      SEVERE: java.sql.SQLException: ORA-06550: Regel 2, kolom 3:

      PLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared.

      ORA-06550: Regel 2, kolom 3:

      PL/SQL: Statement ignored.

       

      dec 15, 2014 1:48:14 PM oracle.json.rest.SodaRestHandler runRequest

      SEVERE: oracle.soda.rdbms.impl.OracleRDBMSException

      dec 15, 2014 1:48:14 PM oracle.json.web.RestRequest sendError

      INFO: Sending error code 500

       

       

      Any idea what not has been installed (guess on my part) yet? Package DBMS_SODA_ADMIN is not installed in the database.

       

      Marco

        • 1. Re: PLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared.
          Josh-Oracle

          Hi Marco,

           

          This error usually means that MLR bundle patch 20080249 is not installed.  If you haven't installed it yet, go to support.oracle.com, search for "20080249" and follow the install instructions.  After the patch is installed, it should start working (you don't need to reinstall ORDS).

           

          Note, the patch was initially released with step 6 missing from the instructions.  This was fixed recently. 

           

          Thanks,

          Josh

          1 person found this helpful
          • 2. Re: PLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared.
            Marco Gralike

            I have seen the reference in the manual a few days ago. The problem is that I only can find a bundle for Linux (at least that's what in the readme). I am currently trying ORDS on Windows 7... Please confirm that I could apply it on Windows as well.

             

            Thanks

             

            Marco

            1 person found this helpful
            • 3. Re: PLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared.
              Josh-Oracle

              Unfortunately the patch is platform specific and the Windows version is not yet available.  I am sorry that you got this far only to find out you can't continue without switching to Linux.  When the Windows version become available, I will let you know. 

               

              Josh

              1 person found this helpful
              • 4. Re: PLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared.
                Marco Gralike

                No worries. Also attempting to load the two needed jar's into the XMLDB XDB Repository environment, to see if I get the REST API working via the XDB Listener protocol (aka (incorrectly) known as the PL/SQL Gateway) accessing the servlet as a "standalone" solution directly.

                 

                Would welcome a hint when the Windows patch will be available.

                 

                Regards

                 

                Marco

                1 person found this helpful
                • 5. Re: Re: PLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared.
                  Marco Gralike

                  Any updates regarding OS specific solution/patch?

                   

                  {"type":"http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.1","status":500,"title":"ORA-06550: line 2, column 3:\nPLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared\n
                  ORA-06550: line 2, column 3:\nPL/SQL: Statement ignored\n","details":"oracle.soda.rdbms.impl.OracleRDBMSException\r\n\tat oracle.soda.rdbms.impl.OracleDatabaseImpl.callListCollections(OracleDatabaseImpl.java:1125)\r\n\
                  tat oracle.soda.rdbms.impl.OracleDatabaseImpl.loadCollections(OracleDatabaseImpl.java:165)\r\n\tat oracle.soda.rdbms.impl.OracleDatabaseImpl.getCollectionNames(OracleDatabaseImpl.java:450)\r\n\tat
                  oracle.soda.rdbms.impl.OracleDatabaseImpl.getCollectionNames(OracleDatabaseImpl.java:443)\r\n\tat oracle.soda.rdbms.impl.OracleDatabaseImpl.getCollectionNames(OracleDatabaseImpl.java:418)\r\n\tat
                  oracle.soda.rdbms.impl.OracleDatabaseImpl.access$400(OracleDatabaseImpl.java:64)\r\n\tat oracle.soda.rdbms.impl.OracleDatabaseImpl$OracleDatabaseAdministrationImpl.getCollectionNames(OracleDatabaseImpl.java:1279)\r\n\tat
                  oracle.json.rest.SodaRestHandler.listCollections(SodaRestHandler.java:341)\r\n\tat oracle.json.rest.SodaRestHandler.runRequest(SodaRestHandler.java:1522)\r\n\tat oracle.json.web.RestRequest.runRequest(RestRequest.java:1107)\r\n\tat
                  oracle.json.web.RestServlet.runRequest(RestServlet.java:228)\r\n\tat oracle.json.web.RestServlet.doGet(RestServlet.java:240)\r\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:740)\r\n\tat
                  oracle.json.web.RestServlet.service(RestServlet.java:311)\r\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:853)\r\n\tat oracle.xdb.servlet.XDBServletContainer.handleRequest(XDBServletContainer.java:103)\r\n
                  Caused by: java.sql.SQLException: ORA-06550: line 2, column 3:\nPLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared\nORA-06550: line 2, column 3:\nPL/SQL:
                  Statement ignored\n\r\n\tat oracle.jdbc.driver.T2SConnection.check_error(T2SConnection.java)\r\n\tat oracle.jdbc.driver.T2SCallableStatement.checkError(T2SCallableStatement.java:123)\r\n\tat
                  oracle.jdbc.driver.T2SCallableStatement.executeForRows(T2SCallableStatement.java)\r\n\tat oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)\r\n\tat
                  oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java)\r\n\tat oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java)\r\n\tat
                  oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java)\r\n\tat oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java)\r\n\tat
                  oracle.soda.rdbms.impl.OracleDatabaseImpl.callListCollections(OracleDatabaseImpl.java:1073)\r\n\t... 15 more\r\nCaused by: oracle.jdbc.driver.OracleSQLException: O
                  RA-06550: line 2, column 3:\nPLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared\nORA-06550: line 2, column 3:\nPL/SQL: Statement ignored\n\r\n","o:errorCode":"SQL-06550"}
                  
                  1 person found this helpful
                  • 6. Re: Re: PLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared.
                    Josh-Oracle

                    As far as I know, only Linux and Solaris so far.  If I hear something about Windows, I will update this thread.  Sorry for the inconvenience. 

                     

                    Thanks,

                    Josh

                    1 person found this helpful
                    • 7. Re: Re: Re: PLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared.
                      Marco Gralike

                      On my TEST system  !!! 

                       

                      • Downloaded the patch
                      • Unzipped it
                      • Copied needed SQL & packages etc to RDBMS\admin
                      • Ran the patch main script after altering the define variables 1,2,3,4 (not really needed here afaik/could see)

                       

                      Rerun my test via http://localhost:8080/DBJSON/

                       

                      Now it seems to work (the basis that is via the XDB Listener servlet environment).

                       

                      {"type":"http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.1","status":400,"title":"A schema must be specified.","o:errorCode":"REST-02017"}
                      
                      

                       

                       

                      1 person found this helpful
                      • 8. Re: PLS-00201: identifier 'DBMS_SODA_ADMIN.LIST_COLLECTIONS' must be declared.
                        Josh-Oracle

                        It appears the windows version of the patch is now available.

                         

                        Thanks,

                        Josh