An Oracle blog about Oracle Text Index

  • November 13, 2019

Using SODA in Oracle Autonomous Database

Roger Ford
Product Manager

(Please note that the authentication issue described below is now solved in Oracle Autonomous Database. It is left here for reference only)

Oracle provides an API for dealing with document content. It's called SODA - Simple Oracle Document Access.

The API is primarily intended for JSON documents - it allows you to use Oracle Database as a "NoSQL" store - you can store structured data in the form of JSON documents and utilize simple "Query-By-Example" (QBE) searches on them.  But of course, since the JSON documents are in the database, and Oracle provides powerful SQL to JSON and JSON to SQL capabilities, we can still use of the power of SQL to search our JSON data store.

SODA is implemented as part of Oracle's REST server ORDS (Oracle REST Data Services).

However, in ORDS 19.2 and earlier (as currently implemented in Oracle Autonomous Database, there's a permissions issue which will prevent normal users from accessing SODA. This can be fixed by running the following short script as an admin user.  For Autonomous Database (including Autonomous Transaction Processing and Autonomous Data Warehouse) you can run this from SQL Developer Web, which is available through the "Developer" section.

   l_patterns owa.vc_arr;
begin ords.delete_privilege_mapping('oracle.soda.privilege.developer', '/soda/*');
   l_patterns(1) := '/soda/*';
   ords.create_privilege(p_name => 'MySodaPrivilege', p_role_name => 'SQL Developer');
   ords.create_privilege_mapping(p_privilege_name => 'MySodaPrivilege', p_patterns => l_patterns);

That should be run as the ADMIN user for your database.
This issue is fixed in ORDS version 19.3.  In order to determine which ORDS version you are running, you can check the "About" dialog box, from the pull-down menu on the right of SQL Developer Web.



Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.