soda-for-plsql-18c

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
[46]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.
* [47]Create a Test Database User
* [48]Enable ORDS and SODA
* [49]Collections
+ [50]Check Collection Exists
+ [51]Create a Collection
+ [52]List All Collections
+ [53]Drop a Collection
* [54]Documents
+ [55]Create a Document
+ [56]Insert a Document
+ [57]Retrieve Documents
+ [58]Update a Document
+ [59]Delete a Document

Related articles.
* [60]Oracle REST Data Services (ORDS) : Simple Oracle Document
Access (SODA)
* [61]Oracle REST Data Services (ORDS) : All Articles
* [62]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

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

You can check if a collection exists by attempting to open it. If the
DBMS_SODA.OPEN_COLLECTION function returns a NULL you 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 ID = ' || 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 ID = 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('l_status=' || l_status);
END;
/
l_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":"SMI
TH"}'
);
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,"emp
loyee_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.

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,"emp
loyee_name":"WARD"}')
);

l_status := l_collection.insert_one(l_document);
DBMS_OUTPUT.put_line('l_status=' || l_status);
COMMIT;
END;
/
l_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,"emp
loyee_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,"emp
loyee_name":"ALLEN"}')
);

l_status := l_collection.replace_one('6D9566A935014FE7BF1D0630B7E44313', l_doc
ument);

DBMS_OUTPUT.put_line('l_status=' || l_status);
COMMIT;
END;
/
l_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,"emp
loyee_name":"ALLEN"}')
);

l_document_out := l_collection.replace_one_and_get('6D9566A935014FE7BF1D0630B7
E44313', 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('l_status=' || l_status);
COMMIT;
END;
/
l_status=1

PL/SQL procedure successfully completed.

SQL>

For more information see:
* [63]Using SODA for PL/SQL
* [64]SODA Types
* [65]DBMS_SODA
* [66]Oracle REST Data Services (ORDS) : Simple Oracle Document
Access (SODA)
* [67]Oracle REST Data Services (ORDS) : All Articles
* [68]JSON Articles

Hope this helps. Regards Tim...

[69]Back to the Top.
[INS: :INS]


[70]0 comments, read/add them...

[71]Home | [72]Articles | [73]Scripts | [74]Blog | [75]Certification |
[76]Misc | [77]About
--------------------------
Source .... : https://oracle-base.com/articles/18c/soda-for-plsql-18c
--------------------------