如何从apex\u json转换为使用sql/json函数

5ktev3wc 于 2021-08-01 发布在 Java

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

让我解释一下我的设想，这样你可能会更好地理解我的问题。我们需要从一组具有特定格式的表中创建一个json文件。我有使用apexèujson的想法，因为它非常简单，但是我有一个内存问题，因为json文档生成了500万条记录。实际上，当我通过应用rownum过滤器来减少记录数时，这个过程就起作用了。但是，当查询尝试创建记录总数为的json时，我得到了一个plsql数值错误，其背后基本上是内存溢出。
我最初的json查询如下：

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN

  for l_hdr_row in (select FILENAME, REPORT_DATE, DOMAINCODE, LEGALENTITYCODE from RDM_OUT.JSON_NKEY_REP_HDR where DOMAINCODE = '00001') 
  loop

     APEX_JSON.INITIALIZE_CLOB_OUTPUT;
     APEX_JSON.OPEN_OBJECT;
     APEX_JSON.OPEN_OBJECT(l_hdr_row.FILENAME);

     APEX_JSON.OPEN_OBJECT;
     APEX_JSON.WRITE('Date',l_hdr_row.REPORT_DATE);
     APEX_JSON.WRITE('DomainCode',l_hdr_row.DOMAINCODE);
     APEX_JSON.WRITE('LegalEntityCode',l_hdr_row.LEGALENTITYCODE);
     APEX_JSON.OPEN_ARRAY('Keys');

     FOR dtl IN (SELECT NATIVEKEY, MASTERKEY, ENDDATE
               FROM RDM_OUT.JSON_NKEY_REP_DTL  DTL
               WHERE DTL.FILENAME = l_hdr_row.FILENAME) LOOP

               APEX_JSON.OPEN_OBJECT;
               APEX_JSON.WRITE('NativeKey',dtl.NATIVEKEY);
               APEX_JSON.WRITE('MasterKey',dtl.MASTERKEY);
               APEX_JSON.WRITE('EndDate',dtl.ENDDATE);
               APEX_JSON.CLOSE_OBJECT;
     END LOOP;
     APEX_JSON.CLOSE_ARRAY;
     APEX_JSON.CLOSE_OBJECT;
     APEX_JSON.CLOSE_ALL;

     DBMS_OUTPUT.PUT_LINE(APEX_JSON.GET_CLOB_OUTPUT);
     APEX_JSON.FREE_OUTPUT;  
  end loop;
END;
/

第一个循环实际上只恢复一行，这在第二个循环中处理，我得到了第一个循环中第一个也是唯一一个循环中出现的550万条记录。我试着使用json_对象和json_数组，但是我不能得到apex_json给我的输出格式。
我使用的是oracle12.2，因此我不能像18c和19c中的一些json函数那样进行改进。
让我向您展示我使用apex\ujson的原始查询的内容

SQL> set serveroutput on size unlimited echo on timing on
SQL> DECLARE
  2    l_cursor SYS_REFCURSOR;
BEGIN
  3    4
  5    for l_hdr_row in (select FILENAME, REPORT_DATE, DOMAINCODE, LEGALENTITYCODE from RDM_OUT.JSON_NKEY_REP_HDR where DOMAINCODE = '00001')
  loop

  6    7    8       APEX_JSON.INITIALIZE_CLOB_OUTPUT;
  9       APEX_JSON.OPEN_OBJECT;
 10       APEX_JSON.OPEN_OBJECT(l_hdr_row.FILENAME);
 11
     APEX_JSON.OPEN_OBJECT;
 12   13       APEX_JSON.WRITE('Date',l_hdr_row.REPORT_DATE);
     APEX_JSON.WRITE('DomainCode',l_hdr_row.DOMAINCODE);
 14   15       APEX_JSON.WRITE('LegalEntityCode',l_hdr_row.LEGALENTITYCODE);
     APEX_JSON.OPEN_ARRAY('Keys');
 16   17
     FOR dtl IN (SELECT NATIVEKEY, MASTERKEY, ENDDATE
               FROM RDM_OUT.JSON_NKEY_REP_DTL  DTL
 18   19   20                 WHERE DTL.FILENAME = l_hdr_row.FILENAME and rownum < 5 ) LOOP

               APEX_JSON.OPEN_OBJECT;
               APEX_JSON.WRITE('NativeKey',dtl.NATIVEKEY);
 21   22   23   24                 APEX_JSON.WRITE('MasterKey',dtl.MASTERKEY);
               APEX_JSON.WRITE('EndDate',dtl.ENDDATE);
 25                 APEX_JSON.CLOSE_OBJECT;
     END LOOP;
 26   27   28       APEX_JSON.CLOSE_ARRAY;
     APEX_JSON.CLOSE_OBJECT;
     APEX_JSON.CLOSE_ALL;

 29   30   31   32       DBMS_OUTPUT.PUT_LINE(APEX_JSON.GET_CLOB_OUTPUT);
     APEX_JSON.FREE_OUTPUT;
  end loop;
END;
 33   34   35   36  /
{
"RTTA_00013_20190831_00001_00055_NKEY":{
{
"Date":"2019-08-31"
,"DomainCode":"
00001"
,"LegalEntityCode":"00055"
,"Keys":[
{
"NativeKey":"85430299"
,"MasterKey
":"01483175470"
,"EndDate":"9999-12-31"
}
,{
"NativeKey":"33227843"
,"MasterKey"
:"329401533000001934"
,"EndDate":"9999-12-31"
}
,{
"NativeKey":"42565570"
,"Mast
erKey":"01500329780"
,"EndDate":"9999-12-31"
}
,{
"NativeKey":"98536882"
,"Maste
rKey":"01502416501"
,"EndDate":"9999-12-31"
}
]
}
}
}

我的问题如下：
有没有更好的方法来做我正在做的事？也许我可以用批量收集与限制吗？
如何使用json\u对象和json\u数组获得相同的结果和格式？我试过了，但是我没有得到相同的输出格式。
谢谢大家！

sql oracle oracle-apex

来源：https://stackoverflow.com/questions/62653417/how-to-convert-from-apex-json-to-use-sql-json-functions

1条答案

按热度按时间

bt1cpqcv1#

最后，我想出了我自己的解决办法。我改为批量收集，现在的过程完美地工作。在这里您可以看到代码现在的样子：

DECLARE
        c_limit   CONSTANT PLS_INTEGER DEFAULT 50000; 
        CURSOR nkey_cur (v_filename in varchar2) IS SELECT * FROM RDM_OUT.JSON_NKEY_REP_DTL DTL where DTL.FILENAME = v_filename ;
        TYPE tbl_Nativekey IS TABLE OF RDM_OUT.JSON_NKEY_REP_DTL%ROWTYPE INDEX BY PLS_INTEGER;
        var_Nativekey tbl_Nativekey;
        PROCEDURE print_clob_to_output (p_clob IN CLOB)  
         IS  
           l_offset     INT := 1;  
         BEGIN  
            loop  
                exit when l_offset > dbms_lob.getlength(p_clob);  
                dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) );  
                l_offset := l_offset + 255;  
            end loop;  
         END print_clob_to_output;
    BEGIN
      for l_hdr_row in (select FILENAME, REPORT_DATE, DOMAINCODE, LEGALENTITYCODE from RDM_OUT.JSON_NKEY_REP_HDR where DOMAINCODE = '00001' )
         loop

         APEX_JSON.INITIALIZE_CLOB_OUTPUT;
        -- APEX_JSON.OPEN_OBJECT;
        -- APEX_JSON.OPEN_OBJECT(l_hdr_row.FILENAME);

         APEX_JSON.OPEN_OBJECT;
         APEX_JSON.WRITE('Date',l_hdr_row.REPORT_DATE);
         APEX_JSON.WRITE('DomainCode',l_hdr_row.DOMAINCODE);
         APEX_JSON.WRITE('LegalEntityCode',l_hdr_row.LEGALENTITYCODE);
         APEX_JSON.OPEN_ARRAY('Keys');

         OPEN nkey_cur(l_hdr_row.FILENAME);

       LOOP
          FETCH nkey_cur BULK COLLECT INTO var_Nativekey LIMIT c_limit;
          EXIT WHEN var_Nativekey.COUNT = 0;  
             FOR i IN var_Nativekey.FIRST..var_Nativekey.LAST LOOP
                   APEX_JSON.OPEN_OBJECT;
                   APEX_JSON.WRITE('NativeKey',var_Nativekey(i).NATIVEKEY);
                   APEX_JSON.WRITE('MasterKey',var_Nativekey(i).MASTERKEY);
                   APEX_JSON.WRITE('EndDate',var_Nativekey(i).ENDDATE);
                   APEX_JSON.CLOSE_OBJECT;
             END LOOP;
         END LOOP;

         CLOSE nkey_cur;

         APEX_JSON.CLOSE_ARRAY;
         APEX_JSON.CLOSE_OBJECT;
         APEX_JSON.CLOSE_ALL;

         PRINT_CLOB_TO_OUTPUT(p_clob => APEX_JSON.GET_CLOB_OUTPUT);
         APEX_JSON.FREE_OUTPUT;  
      end loop;
    END;
    /

我无法使用sql/json函数获得相同的格式，所以我决定转换所使用的代码以获得bulk collect的好处。我还修改了在输出中打印clob的方式，以避免缓冲区出现问题：

SQL> host cat JSON_00001_NKEY.sql
spool &1

DECLARE
    c_limit   CONSTANT PLS_INTEGER DEFAULT 10000; -- EXAMPLE of LIMIT
    CURSOR nkey_cur (v_filename in varchar2) IS SELECT * FROM RDM_OUT.JSON_NKEY_REP_DTL DTL where DTL.FILENAME = v_filename ;
    TYPE tbl_Nativekey IS TABLE OF RDM_OUT.JSON_NKEY_REP_DTL%ROWTYPE INDEX BY PLS_INTEGER;
    var_Nativekey tbl_Nativekey;
        PROCEDURE print_clob_to_output (p_clob IN CLOB)
         IS
           l_offset     INT := 1;
         BEGIN
                loop
                        exit when l_offset > dbms_lob.getlength(p_clob);
                        dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) );
                        l_offset := l_offset + 255;
                end loop;
         END print_clob_to_output;
BEGIN
  for l_hdr_row in (select FILENAME, REPORT_DATE, DOMAINCODE, LEGALENTITYCODE from RDM_OUT.JSON_NKEY_REP_HDR where DOMAINCODE = '00001' )
     loop

     APEX_JSON.INITIALIZE_CLOB_OUTPUT;

     APEX_JSON.OPEN_OBJECT;
     APEX_JSON.WRITE('Date',l_hdr_row.REPORT_DATE);
     APEX_JSON.WRITE('DomainCode',l_hdr_row.DOMAINCODE);
     APEX_JSON.WRITE('LegalEntityCode',l_hdr_row.LEGALENTITYCODE);
     APEX_JSON.OPEN_ARRAY('Keys');

     OPEN nkey_cur(l_hdr_row.FILENAME);

   LOOP
      FETCH nkey_cur BULK COLLECT INTO var_Nativekey LIMIT c_limit;
      EXIT WHEN var_Nativekey.COUNT = 0;
         FOR i IN var_Nativekey.FIRST..var_Nativekey.LAST LOOP
               APEX_JSON.OPEN_OBJECT;
               APEX_JSON.WRITE('NativeKey',var_Nativekey(i).NATIVEKEY);
               APEX_JSON.WRITE('MasterKey',var_Nativekey(i).MASTERKEY);
               APEX_JSON.WRITE('EndDate',var_Nativekey(i).ENDDATE);
               APEX_JSON.CLOSE_OBJECT;
         END LOOP;
     END LOOP;

     CLOSE nkey_cur;

     APEX_JSON.CLOSE_ARRAY;
     APEX_JSON.CLOSE_OBJECT;
     APEX_JSON.CLOSE_ALL;

     PRINT_CLOB_TO_OUTPUT(p_clob => APEX_JSON.GET_CLOB_OUTPUT);
     APEX_JSON.FREE_OUTPUT;
  end loop;
END;
/

spool off

exit

SQL> set serveroutput off
SQL> set timing on
SQL> @JSON_00001_NKEY.sql test.json
PL/SQL procedure successfully completed.

Elapsed: 00:02:33.89

我想一定有一个使用sql/json的选项，但我相信apex\ujson提供了一个非常简单的api。要小心内存溢出或性能问题，批量收集可能是一个好方法。

赞(0）回复(0）举报 2021-08-01

我来回答

如何从apex\u json转换为使用sql/json函数

1条答案

相关问题

热门标签

最新问答