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

MT0 · Accepted Answer · 2023-09-03 23:53:52Z

0

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

answered Sep 3, 2023 at 23:53

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

virtual transfer Over a year ago

Thank you. But I have a need to fetch some values in the payload from database cursor.

virtual transfer Over a year ago

Also, I would have to put each child payload (instance number and serial number) into different clob and than concatenate bot clobs into one clob and pass that into webservice, correct?

MT0 Over a year ago

@virtualtransfer Use a JOIN if you want to fetch things from other tables. However, you do not show that in your question so we are not able to know that that is what you want to achieve.

virtual transfer Over a year ago

Thank you, your reply did help me figure it out.

Collectives™ on Stack Overflow

JSON arrays into CLOB - Oracle

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related