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

Question

I am using SODA for Java to store and retrieve documents from oracle 12c DB. I am following this example.

I am getting exception after the execution of the code.

oracle.soda.rdbms.impl.SODAUtils$1OracleSQLException at oracle.soda.rdbms.impl.SODAUtils.makeExceptionWithSQLText(SODAUtils.java:112) at oracle.soda.rdbms.impl.SODAUtils.makeExceptionWithSQLText(SODAUtils.java:75) at oracle.soda.rdbms.impl.OracleDatabaseImpl.loadCollection(OracleDatabaseImpl.java:1338) at oracle.soda.rdbms.impl.OracleDatabaseImpl.openCollection(OracleDatabaseImpl.java:410) at oracle.soda.rdbms.impl.OracleDatabaseImpl.createCollection(OracleDatabaseImpl.java:348) at oracle.soda.rdbms.impl.OracleDatabaseImpl.createCollection(OracleDatabaseImpl.java:332) at oracle.soda.rdbms.impl.OracleDatabaseImpl.createCollection(OracleDatabaseImpl.java:321) at oracle.soda.rdbms.impl.OracleDatabaseImpl.access$100(OracleDatabaseImpl.java:62) at oracle.soda.rdbms.impl.OracleDatabaseImpl$OracleDatabaseAdministrationImpl.createCollection(OracleDatabaseImpl.java:1798) at com.cisco.salesconnect.lms.TestSoda.main(TestSoda.java:26) Caused by: java.sql.SQLException: ORA-06550: line 2, column 3: PLS-00201: identifier 'DBMS_SODA_ADMIN.DESCRIBE_COLLECTION' must be declared ORA-06550: line 2, column 3: PL/SQL: Statement ignored at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204) at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1041) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584) at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685) at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714) at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376) at oracle.soda.rdbms.impl.OracleDatabaseImpl.loadCollection(OracleDatabaseImpl.java:1317) ... 7 more

Java Code

try(OracleConnection conn = (OracleConnection) DriverManager.getConnection(/*connection url*/)) {

        conn.setImplicitCachingEnabled(true);
        conn.setStatementCacheSize(50);

        OracleRDBMSClient cl = new OracleRDBMSClient();

        OracleDatabase db = cl.getDatabase(conn);

        db.admin().createCollection("MyFirstJSONCollection");

    } catch (SCExceptions | SQLException | OracleException e) {
        e.printStackTrace();
    }
}

What will be the reason behind this exception?

Is the package [installed properly](https://support.oracle.com/knowledge/Oracle%20Database%20Products/2142385_1.html), and has the user you are connected as been granted privileges on it - presumably via the SODA_APP role, if you haven't skipped any steps? — Alex Poole, Aug 09 '19 at 11:17

score 3 · Answer 1 · answered Aug 11 '20 at 05:56

3

Did you grant SODA_APP role to the user under which you're creating the collection? e.g.:

grant SODA_APP to userNameHere;

The error you're getting would happen if you didn't.

answered Aug 11 '20 at 05:56

Max Orgiyan

131
4

score 0 · Accepted Answer · answered Aug 09 '19 at 11:53

Somewhere in the code which gets executed when you call db.admin().createCollection("MyFirstJSONCollection"); a reference is being made to an object called DBMS_SODA_ADMIN.DESCRIBE_COLLECTION which does not exist in the database. You should check and see if the schema DBMS_SODA_ADMIN exists, and if there is an object called DESCRIBE_COLLECTION in that schema. One way to do that would be to execute a query such as

SELECT *
  FROM DBA_USERS u
  WHERE u.USERNAME = 'DBMS_SODA_ADMIN'

SELECT *
  FROM DBA_OBJECTS o
  WHERE o.OWNER = 'DBMS_SODA_ADMIN' AND
        o.OBJECT_NAME = 'DESCRIBE_COLLECTION'

If either is missing it indicates that the installation of the Soda database components was either not performed, or did not complete successfully. If these things do exist it indicates that the user your codes connects as does not have the permissions needed to access this object.

You've misread the error `PLS-00201: identifier 'DBMS_SODA_ADMIN.DESCRIBE_COLLECTION' must be declared` The DBMS_SODA_ADMIN is not a user, but it is a package name and DESCRIBE_COLLECTION is a procedure name within that package. — V P, Jun 02 '23 at 13:49

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

2 Answers2