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(OracleRDBMSClient.java:214)
        at oracle.soda.rdbms.OracleRDBMSClient.getDatabase(OracleRDBMSClient.java:118)
        at oracle.dbtools.raptor.newscriptrunner.commands.SODACommand.handleEvent(SODACommand.java:111)
        at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:346)
        at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:226)
        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)
        at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:410)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:421)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1292)
        at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:502)
Caused by: java.lang.ClassNotFoundException: javax.json.JsonException
        at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:355)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
        ... 11 more

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

https://docs.oracle.com/en/cloud/paas/exadata-express-cloud/csdbp/use-soda-java.html#GUID-79A14FCD-D0D0-4113-9455-A862755F0B3D
java.json-1.0.4 jar (JSR353) found here: http://search.maven.org/remotecontent?filepath=org/glassfish/javax.json/1.0.4/javax.json-1.0.4.jar

[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 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> soda list
There are no existing collections.

SQL> soda create myDocuments;
Successfully created collection: myDocuments

SQL>
SQL> soda list
List of collections:

        myDocuments

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.