JSON数组到CLOB - Oracle

yhived7q 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(221)

我必须构建一个following child json payload来补充main payload。我的问题是如何将这些数组放到一个clob中作为PSLQL参数传递。
代码：

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 */

感谢Darsh

oracle

来源：https://stackoverflow.com/questions/77034391/json-arrays-into-clob-oracle

1条答案

按热度按时间

4uqofj5v1#

不需要游标或循环;使用Oracle JSON函数：

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;
/

其中，对于样本数据：

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

输出：

[{"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

赞(0）回复(0）举报 2023-10-16

我来回答

JSON数组到CLOB - Oracle

1条答案

相关问题

热门标签

最新问答