With Database Actions (SQL Developer Web), it's quite easy to click on the 'New JSON Document' button to add a new JSON document to the collection.
The collection of course is actually a table in Oracle, and the table itself has a number of columns:
I have created modules in ORDS with PL/SQL handlers. While I am able to update JSON documents here by using
UPDATE "Collection" SET json_document = '{"key": "value"}' WHERE JSON_VALUE(json_document, '$.id') = :id'
I am not able to add a new document easily with
INSERT INTO "Collection" (json_document) VALUES ('{"key": "value"}')
because the id
is set as a PK column and must be specified. How might I use PL/SQL to add a new document with auto generated fields elsewhere? Or should I use SODA for PL/SQL to achieve this only?
Thanks!
CodePudding user response:
You use the dbms_soda
package to access the collection. Then use the methods on soda_colletion_t
to manipulate it.
For example:
soda create students;
declare
collection soda_collection_t;
document soda_document_t;
status number;
begin
-- open the collection
collection := dbms_soda.open_collection('students');
document := soda_document_t(
b_content => utl_raw.cast_to_raw (
'{"id":1,"name":"John Blaha","class":"1980","courses":["c1","c2"]}'
)
);
-- insert a document
status := collection.insert_one(document);
end;
/
select * from students;
ID CREATED_ON LAST_MODIFIED VERSION JSON_DOCUMENT
-------------------------------- ------------------------ ------------------------ -------------------------------- ---------------
A92F68753B384F87BF12557AC38098CB 2021-12-22T14:15:12.831Z 2021-12-22T14:15:12.831Z FE8C80FED46A4F18BFA070EF46073F43 [object Object]