JSON arrays into CLOB - Oracle

Question

I have to build a following child json payload to supplment the main payload. My question is how can I put these arrays into a clob to pass through as a PSLQL parameter.

Code:

Declare

l_children json_array_t;
p_clob CLOB

cursor getrec is select * from sometable;

begin

for i in getrec loop

    select TO_CHAR(SYSTIMESTAMP,'mmddyyyyhhmiss') into l_doc_id from dual;
 l_children.append(json_object_t('
            {
             
             "AttachedDocumentId": "'||l_doc_id||'",
             "DatatypeCode": "TEXT",
             "CategoryName": "INSTANCE_NUMBER",
             "UploadedText": "'||i.instance_number||'",
             "UploadedFileContentType": "text",
             "ContentRepositoryFileShared": "false",
             "Title": "'||i.instance_number||'",
             "Description": "'||i.instance_number||'"
             }'));
             
        select TO_CHAR(SYSTIMESTAMP,'mmddyyyyhhmiss') into l_doc_id from dual;
        
        l_children.append(json_object_t('
            {
             
             "AttachedDocumentId": "'||l_doc_id||'",
             "DatatypeCode": "TEXT",
             "CategoryName": "SERIAL_NUMBER",
             "UploadedText": "'||i.attachment_serial_number||'",
             "UploadedFileContentType": "text",
             "ContentRepositoryFileShared": "false",
             "Title": "'||i.attachment_serial_number||'",
             "Description": "'||i.attachment_serial_number||'"
             }'));
       
     end loop;
     p_Clob := l_children.to_clob;  /* this won't work */

Thank you Darsh

Answer 1

You do not need cursors or loops; instead just use Oracle JSON functions:

Declare
  p_clob CLOB;
begin
  SELECT JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'AttachedDocumentId'          VALUE TO_CHAR(SYSTIMESTAMP,'mmddyyyyhhmiss'),
             KEY 'DatatypeCode'                VALUE 'TEXT',
             KEY 'CategoryName'                VALUE 'INSTANCE_NUMBER',
             KEY 'UploadedText'                VALUE instance_number,
             KEY 'UploadedFileContentType'     VALUE 'text',
             KEY 'ContentRepositoryFileShared' VALUE 'false' FORMAT JSON,
             KEY 'Title'                       VALUE instance_number,
             KEY 'Description'                 VALUE instance_number
           )
           RETURNING CLOB
         )
  INTO   p_clob
  FROM   sometable;

  DBMS_OUTPUT.PUT_LINE(p_clob);
END;
/

Which, for the sample data:

CREATE TABLE sometable(instance_number) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3;

Outputs:

[{"AttachedDocumentId":"09042023125446","DatatypeCode":"TEXT","CategoryName":"INSTANCE_NUMBER","UploadedText":1,"UploadedFileContentType":"text","ContentRepositoryFileShared":false,"Title":1,"Description":1},{"AttachedDocumentId":"09042023125446","DatatypeCode":"TEXT","CategoryName":"INSTANCE_NUMBER","UploadedText":2,"UploadedFileContentType":"text","ContentRepositoryFileShared":false,"Title":2,"Description":2},{"AttachedDocumentId":"09042023125446","DatatypeCode":"TEXT","CategoryName":"INSTANCE_NUMBER","UploadedText":3,"UploadedFileContentType":"text","ContentRepositoryFileShared":false,"Title":3,"Description":3}]

fiddle