Sunday, September 27, 2020

Encountered errors during first time SQLcl execution for Simple Oracle Document Access (SODA)

 Error 1: java.lang.NoClassDefFoundError: javax/json/JsonException

SQL> soda list
Exception in thread "main" java.lang.NoClassDefFoundError: javax/json/JsonException
        at oracle.soda.rdbms.OracleRDBMSClient.getDatabase(
        at oracle.soda.rdbms.OracleRDBMSClient.getDatabase(
        at oracle.dbtools.raptor.newscriptrunner.commands.SODACommand.handleEvent(
        at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(
Caused by: java.lang.ClassNotFoundException: javax.json.JsonException
        at java.lang.ClassLoader.loadClass(
        at sun.misc.Launcher$AppClassLoader.loadClass(
        at java.lang.ClassLoader.loadClass(
        ... 11 more

How to fix: Download javax.json-x1.0.4.jar based on following document and put in SQL Developer/lib/ext directory.

[oracle@ol8 ext]$ ls -l /u01/sqldeveloper-20.2/sqldeveloper/sqldeveloper/lib/ext/javax.json-1.0.4.jar
-rw-r--r-- 1 oracle oinstall 85147 Nov 19  2013 /u01/sqldeveloper-20.2/sqldeveloper/sqldeveloper/lib/ext/javax.json-1.0.4.jar

Error 2: PLS-00201: identifier ‘DBMS_SODA_ADMIN’ must be declared

SQL> soda list
ORA-06550: line 2, column 3:
PLS-00201: identifier 'DBMS_SODA_ADMIN' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

Failed to execute: soda list

How to fix: Grant soda_app privilege using sys.

grant soda_app to donghua;

Sample output: working Soda example with document API

[oracle@ol8 ext]$ sql donghua/Password_xxxx@pdb1

SQLcl: Release 20.2 Production on Sun Sep 27 22:05:15 2020

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Sun Sep 27 2020 22:05:16 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release - Production

SQL> soda list
There are no existing collections.

SQL> soda create myDocuments;
Successfully created collection: myDocuments

SQL> soda list
List of collections:


SQL> soda insert myDocuments {"mykey":"My Value"}
Json String inserted successfully.

SQL> soda count myDocuments

 1 row selected.

SQL> soda get myDocuments -all;
        KEY                                             Created On

        6C1917D2E3B94FC9899E6FD87E4E6B46                2020-09-27T14:07:00.717168000Z

 1 row selected.

SQL> soda get myDocuments -klist 6C1917D2E3B94FC9899E6FD87E4E6B46

Key:             6C1917D2E3B94FC9899E6FD87E4E6B46
Content:         {"mykey":"My Value"}

 1 row selected.

No comments:

Post a Comment