8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Simple Oracle Document Access (SODA) for PL/SQL in Oracle Database 18c
The Simple Oracle Document Access (SODA) functionality was introduced with Oracle REST Data Services (ORDS) against Oracle 12.1, as described here. Oracle 18c introduced a PL/SQL API for interacting directly with SODA collections and documents. This article gives an overview of the Simple Oracle Document Access (SODA) for PL/SQL functionality in Oracle Database 18c.
Related articles.
- Simple Oracle Document Access (SODA) for PL/SQL
- Oracle REST Data Services (ORDS) : Simple Oracle Document Access (SODA) for REST
- Oracle REST Data Services (ORDS) : All Articles
- JSON Articles
Create a Test Database User
We need a new database user for our testing.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER=pdb1; DROP USER sodauser CASCADE; CREATE USER sodauser IDENTIFIED BY sodauser1 DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE TABLE TO sodauser; GRANT SODA_APP TO sodauser;
Notice the grant for the SODA_APP
role.
Enable ORDS and SODA
We don't need to enable ORDS for the schema to use SODA for PL/SQL, but it makes sense to do this as it will allow the collection to be accessible from SODA for REST also.
Enable REST web services for the test schema. We could use any unique and legal URL mapping pattern for the schema, so it is not necessary to expose the schema name as we have done here.
CONN sodauser/sodauser1@pdb1 BEGIN ORDS.enable_schema( p_enabled => TRUE, p_schema => 'SODAUSER', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'sodauser', p_auto_rest_auth => FALSE ); COMMIT; END; /
We are now ready to start.
Collections
As the name suggests, collections are a way of grouping documents. It probably makes sense to define separate collections for different types of documents, but there is nothing to stop you keeping a variety of document types in a single collection.
Check Collection Exists
We can check if a collection exists by attempting to open it. If the DBMS_SODA.OPEN_COLLECTION
function returns a NULL, we know the collection doesn't exist. If the collection does exist, a reference to it will be returned as the SODA_COLLECTION_T
type.
SET SERVEROUTPUT ON DECLARE l_collection SODA_COLLECTION_T; BEGIN l_collection := DBMS_SODA.open_collection('TestCollection1'); IF l_collection IS NOT NULL THEN DBMS_OUTPUT.put_line('collection: ' || l_collection.get_name()); ELSE DBMS_OUTPUT.put_line('Collection does not exist.'); END IF; END; / Collection does not exist. PL/SQL procedure successfully completed. SQL>
Create a Collection
The DBMS_SODA.CREATE_COLLECTION
function creates a new collection and returns the collection reference as the SODA_COLLECTION_T
type.
SET SERVEROUTPUT ON DECLARE l_collection SODA_COLLECTION_T; BEGIN l_collection := DBMS_SODA.create_collection('TestCollection1'); IF l_collection IS NOT NULL THEN DBMS_OUTPUT.put_line('Collection ID : ' || l_collection.get_name()); ELSE DBMS_OUTPUT.put_line('Collection does not exist.'); END IF; END; / collection: TestCollection1 PL/SQL procedure successfully completed. SQL>
The table has been created in the test schema. The table name is case sensitive, so you will have to double-quote the table name.
DESC "TestCollection1" Name Null? Type ----------------------------------------------------- -------- ------------------------------------ ID NOT NULL VARCHAR2(255) CREATED_ON NOT NULL TIMESTAMP(6) LAST_MODIFIED NOT NULL TIMESTAMP(6) VERSION NOT NULL VARCHAR2(255) JSON_DOCUMENT BLOB SQL>
This is essentially a table holding key-value pairs, with the key being the ID
column and the value being the JSON_DOCUMENT
column.
List All Collections
The DBMS_SODA.LIST_COLLECTION_NAMES
function returns a list the available collections as the SODA_COLLNAME_LIST_T
type.
SET SERVEROUTPUT ON DECLARE l_coll_list SODA_COLLNAME_LIST_T; BEGIN l_coll_list := DBMS_SODA.list_collection_names; IF l_coll_list.COUNT > 0 THEN FOR i IN 1 .. l_coll_list.COUNT LOOP DBMS_OUTPUT.put_line(i || ' : ' || l_coll_list(i)); END LOOP; END IF; END; / 1 : TestCollection1 PL/SQL procedure successfully completed. SQL>
Drop a Collection
The DBMS_SODA.DROP_COLLECTION
function drops the specified collection and returns "1" if it is successful, or "0" if it fails.
SET SERVEROUTOUT ON DECLARE l_status NUMBER := 0; BEGIN l_status := DBMS_SODA.drop_collection('TestCollection1'); DBMS_OUTPUT.put_line('status : ' || l_status); END; / status : 1 PL/SQL procedure successfully completed. SQL>
The table has been removed from the schema.
DESC "TestCollection1" ERROR: ORA-04043: object "TestCollection1" does not exist SQL>
The remaining examples assume the "TestCollection1" collection is present, so if you deleted it previously, recreate it.
Documents
A document is a combination of a JSON document you wish to persist in a collection, along with some document metadata, including a document identifier/key (ID). The document key can be assigned manually, or automatically is the key presented is null.
Create a Document
A new document is created using the SODA_DOCUMENT_T
constructor. There are overloads to create documents from VARCHAR2
, CLOB
and BLOB
content. The document key and media type are optional. If the key is not set manually, a system generated key is used once the document is inserted into a collection.
SET SERVEROUTPUT ON DECLARE l_varchar2_doc SODA_DOCUMENT_T; l_clob_doc SODA_DOCUMENT_T; l_blob_doc SODA_DOCUMENT_T; BEGIN DBMS_OUTPUT.put_line('=========='); DBMS_OUTPUT.put_line('Key and VARCHAR2 Content.'); l_varchar2_doc := SODA_DOCUMENT_T( key => '1234', v_content => '{"employee_number":7369,"employee_name":"SMITH"}' ); DBMS_OUTPUT.put_line('key : ' || l_varchar2_doc.get_key); DBMS_OUTPUT.put_line('content : ' || l_varchar2_doc.get_varchar2); DBMS_OUTPUT.put_line('media_type: ' || l_varchar2_doc.get_media_type); DBMS_OUTPUT.put_line('=========='); DBMS_OUTPUT.put_line('CLOB Content and Media Type.'); l_clob_doc := SODA_DOCUMENT_T( c_content => '{"employee_number":7499,"employee_name":"ALLEN"}', media_type => 'application/json' ); DBMS_OUTPUT.put_line('key : ' || l_clob_doc.get_key); DBMS_OUTPUT.put_line('content : ' || l_clob_doc.get_clob); DBMS_OUTPUT.put_line('media_type: ' || l_clob_doc.get_media_type); DBMS_OUTPUT.put_line('=========='); DBMS_OUTPUT.put_line('BLOB Content.'); l_blob_doc := SODA_DOCUMENT_T( b_content => UTL_RAW.cast_to_raw('{"employee_number":7521,"employee_name":"WARD"}') ); DBMS_OUTPUT.put_line('key : ' || l_blob_doc.get_key); DBMS_OUTPUT.put_line('content : ' || UTL_RAW.cast_to_varchar2(l_blob_doc.get_blob)); DBMS_OUTPUT.put_line('media_type: ' || l_blob_doc.get_media_type); END; / ========== Key and VARCHAR2 Content. key : 1234 content : {"employee_number":7369,"employee_name":"SMITH"} media_type: application/json ========== CLOB Content and Media Type. key : content : {"employee_number":7499,"employee_name":"ALLEN"} media_type: application/json ========== BLOB Content. key : content : {"employee_number":7521,"employee_name":"WARD"} media_type: application/json PL/SQL procedure successfully completed. SQL>
Notice the KEY
is null unless it is specified manually. A unique system generated key will be assigned once the document is inserted into a collection.
The following examples will use a default BLOB
collection. If you want to use a different collection type, check out the custom collections section.
Insert a Document
A new document is added to the collection using the INSERT_ONE
or INSERT_ONE_AND_GET
member functions of the SODA_COLLECTION_T
type. If you don't care about retrieving a system generated key use the INSERT_ONE
member function.
SET SERVEROUTPUT ON DECLARE l_collection SODA_COLLECTION_T; l_document SODA_DOCUMENT_T; l_status NUMBER; BEGIN l_collection := DBMS_SODA.open_collection('TestCollection1'); l_document := SODA_DOCUMENT_T( b_content => UTL_RAW.cast_to_raw('{"employee_number":7521,"employee_name":"WARD"}') ); l_status := l_collection.insert_one(l_document); DBMS_OUTPUT.put_line('status : ' || l_status); COMMIT; END; / status : 1 PL/SQL procedure successfully completed. SQL>
If you need to retrieve the system generated key you should use INSERT_ONE_AND_GET
member function.
SET SERVEROUTPUT ON DECLARE l_collection SODA_COLLECTION_T; l_document SODA_DOCUMENT_T; l_document_out SODA_DOCUMENT_T; BEGIN l_collection := DBMS_SODA.open_collection('TestCollection1'); l_document := SODA_DOCUMENT_T( b_content => UTL_RAW.cast_to_raw('{"employee_number":7521,"employee_name":"WARD"}') ); l_document_out := l_collection.insert_one_and_get(l_document); DBMS_OUTPUT.put_line('key : ' || l_document_out.get_key); DBMS_OUTPUT.put_line('content : ' || UTL_RAW.cast_to_varchar2(l_document_out.get_blob)); DBMS_OUTPUT.put_line('media_type: ' || l_document_out.get_media_type); COMMIT; END; / key : 6D9566A935014FE7BF1D0630B7E44313 content : media_type: application/json PL/SQL procedure successfully completed. SQL>
Notice the document content is not present in the document returned by the INSERT_ONE_AND_GET
function. This is intentional, as it would represent a waste of resources if we were dealing with large document.
We can see rows containing the documents have been added to the associated table.
SELECT COUNT(*) FROM "TestCollection1"; COUNT(*) ---------- 2 SQL>
Retrieve Document
You retrieve a document using the FIND_ONE
member function of the SODA_COLLECTION_T
type.
SET SERVEROUTPUT ON DECLARE l_collection SODA_COLLECTION_T; l_document SODA_DOCUMENT_T; BEGIN l_collection := DBMS_SODA.open_collection('TestCollection1'); l_document := l_collection.find_one('6D9566A935014FE7BF1D0630B7E44313'); DBMS_OUTPUT.put_line('key : ' || l_document.get_key); DBMS_OUTPUT.put_line('content : ' || UTL_RAW.cast_to_varchar2(l_document.get_blob)); DBMS_OUTPUT.put_line('media_type: ' || l_document.get_media_type); COMMIT; END; / key : 6D9566A935014FE7BF1D0630B7E44313 content : {"employee_number":7521,"employee_name":"WARD"} media_type: application/json PL/SQL procedure successfully completed. SQL>
Update a Document
An existing document in the collection is updated using the REPLACE_ONE
or REPLACE_ONE_AND_GET
member functions of the SODA_COLLECTION_T
type. The REPLACE_ONE
member function returns "1" if the replace is successful and "0" if isn't.
SET SERVEROUTPUT ON DECLARE l_collection SODA_COLLECTION_T; l_document SODA_DOCUMENT_T; l_status NUMBER; BEGIN l_collection := DBMS_SODA.open_collection('TestCollection1'); l_document := SODA_DOCUMENT_T( b_content => UTL_RAW.cast_to_raw('{"employee_number":7499,"employee_name":"ALLEN"}') ); l_status := l_collection.replace_one('6D9566A935014FE7BF1D0630B7E44313', l_document); DBMS_OUTPUT.put_line('status : ' || l_status); COMMIT; END; / status : 1 PL/SQL procedure successfully completed. SQL>
The REPLACE_ONE_AND_GET
member function is similar to the INSERT_ONE_AND_GET
member function, in that it returns a document minus the content.
SET SERVEROUTPUT ON DECLARE l_collection SODA_COLLECTION_T; l_document SODA_DOCUMENT_T; l_document_out SODA_DOCUMENT_T; BEGIN l_collection := DBMS_SODA.open_collection('TestCollection1'); l_document := SODA_DOCUMENT_T( b_content => UTL_RAW.cast_to_raw('{"employee_number":7499,"employee_name":"ALLEN"}') ); l_document_out := l_collection.replace_one_and_get('6D9566A935014FE7BF1D0630B7E44313', l_document); DBMS_OUTPUT.put_line('key : ' || l_document_out.get_key); DBMS_OUTPUT.put_line('content : ' || UTL_RAW.cast_to_varchar2(l_document_out.get_blob)); DBMS_OUTPUT.put_line('media_type: ' || l_document_out.get_media_type); COMMIT; END; / key : 6D9566A935014FE7BF1D0630B7E44313 content : media_type: application/json PL/SQL procedure successfully completed. SQL>
Delete a Document
An existing document in the collection is removed using the REMOVE_ONE
member function of the SODA_COLLECTION_T
type.
SET SERVEROUTPUT ON DECLARE l_collection SODA_COLLECTION_T; l_status NUMBER; BEGIN l_collection := DBMS_SODA.open_collection('TestCollection1'); l_status := l_collection.remove_one('6D9566A935014FE7BF1D0630B7E44313'); DBMS_OUTPUT.put_line('status : ' || l_status); COMMIT; END; / status : 1 PL/SQL procedure successfully completed. SQL>
Custom Collections
By default collections have a BLOB payload and the IDs are handled automatically. Srikrishnan Suresh pointed out this default behaviour can be modified by specifying metadata for the collection. The following CREATE_COLLECTION
call includes the metadata to allow the key column to be set manually, and alters the document data type to a CLOB
data type.
DECLARE l_collection SODA_COLLECTION_T; l_metadata VARCHAR2(32767); BEGIN l_metadata := '{ "keyColumn":{ "assignmentMethod": "CLIENT" }, "contentColumn": { "sqlType": "CLOB" } }'; l_collection := DBMS_SODA.create_collection('TestCollection2', l_metadata); IF l_collection IS NOT NULL THEN DBMS_OUTPUT.put_line('Collection ID : ' || l_collection.get_name()); ELSE DBMS_OUTPUT.put_line('Collection does not exist.'); END IF; END; / Collection ID : TestCollection2 PL/SQL procedure successfully completed. SQL>
The associated table structure looks different now, matching the metadata.
SQL> desc "TestCollection2" Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL VARCHAR2(255) JSON_DOCUMENT CLOB SQL>
We can add a document to the collection using the CLOB
overload of the SODA_DOCUMENT_T
object. In this case we're manually setting the key.
SET SERVEROUTPUT ON DECLARE l_collection SODA_COLLECTION_T; l_clob_doc SODA_DOCUMENT_T; l_document_out SODA_DOCUMENT_T; BEGIN l_collection := DBMS_SODA.open_collection('TestCollection2'); l_clob_doc := SODA_DOCUMENT_T( key => '1234', c_content => '{"employee_number":7499,"employee_name":"ALLEN"}', media_type => 'application/json' ); l_document_out := l_collection.insert_one_and_get(l_clob_doc); DBMS_OUTPUT.put_line('key : ' || l_document_out.get_key); DBMS_OUTPUT.put_line('content : ' || l_document_out.get_clob); DBMS_OUTPUT.put_line('media_type: ' || l_document_out.get_media_type); COMMIT; END; / key : 1234 content : media_type: application/json PL/SQL procedure successfully completed. SQL>
We can drop the collection in the normal way.
SET SERVEROUTOUT ON DECLARE l_status NUMBER := 0; BEGIN l_status := DBMS_SODA.drop_collection('TestCollection2'); DBMS_OUTPUT.put_line('status : ' || l_status); END; / status : 1 PL/SQL procedure successfully completed. SQL>
For more information see:
- Using SODA for PL/SQL
- SODA Types
- DBMS_SODA
- Simple Oracle Document Access (SODA) for PL/SQL
- Oracle REST Data Services (ORDS) : Simple Oracle Document Access (SODA) for REST
- Oracle REST Data Services (ORDS) : All Articles
- JSON Articles
Hope this helps. Regards Tim...