ORACLE EBS接口开发-json格式数据快捷生成

进行接口开发的时候，如果涉及很多字段或者复杂的嵌套结构，为了避免人工拼凑json数据，下面是常用的几种json数据产生方式。

插件 pljson 方式

避免中文进行Unicode转换

修改：pljson_printer.escapeChar 如下：

function escapeChar(ch char) return varchar2 deterministic is
     result varchar2(20);
  begin
      --backspace b = U+0008
      --formfeed  f = U+000C
      --newline   n = U+000A
      --carret    r = U+000D
      --tabulator t = U+0009
      result := ch;

      case ch
      when chr( 8) then result := '\b';
      when chr( 9) then result := '\t';
      when chr(10) then result := '\n';
      when chr(12) then result := '\f';
      when chr(13) then result := '\r';
      when chr(34) then result := '\"';
      when chr(47) then if (escape_solidus) then result := '\/'; end if;
      when chr(92) then result := '\\';
      /* WARNING: ascii() returns PLS_INTEGER and large unicode code points can be negative */
      else /*if (ascii(ch) >= 0 and ascii(ch) < 32) then
             result :=  '\u' || replace(substr(to_char(ascii(ch), 'XXXX'), 2, 4), ' ', '0');
        elsif (ascii_output) then
             result := replace(asciistr(ch), '\', '\u');
        end if;*/ --yangshixian@2022-5-23 避免将中文转码
        null;
      end case;
      return result;
  end;

直接打印

表字段类型虽然是varchar2类型，但是如果存放的值全是数字的话，会自动被转换成number格式，即没有双引号了，可以通过加单引号，然后把单引号给替换掉的方式来避免这种隐式转换。

declare
   ret     pljson_list;
   dyn_ref sys_refcursor;
begin
   open dyn_ref for
      select h.BATCH_NO,
             h.INVOICE_TYPE,
             h.SEQ,
             h.DOC_SEQUENCE_NUM,
             h.DOC_NUM,
             h.DOC_TYPE,
             h.APPROVE_EMPLOYEE_NO,
             h.MAKER_EMPLOYEE_NO,
             h.REQUESTOR_NO,
             h.CURRENCY_CODE,
             h.CURRENCY_CODE_BEG,
             h.EXCHANGE_RATE,
             h.VENDOR_NUM,
             h.PAY_FLAG,
             to_char(h.GL_DATE,'YYYY-MM-DD') GL_DATE,
              to_char(h.BUSINESS_DATE,'YYYY-MM-DD') BUSINESS_DATE,
             h.PREPAYMENT_FLAG,
             h.INVOICE_AMOUNT,
             h.INVOICE_AMOUNT_BEG,
             h.SIG_FLAG,
             TO_CHAR(h.FINISHED_DATE,'YYYY-MM-DD') FINISHED_DATE,
             h.PAYMENT_CURRENCY_CODE,
             /*h.ORGANIZATION_NAME,*/
             ''''||hou.short_code || '''' short_code,
             h.SOURCE_LINE_ID,
             h.CREATION_DATE,
             h.ATTRIBUTE1,
             h.ATTRIBUTE2,
             h.ATTRIBUTE3,
             h.ATTRIBUTE4,
             h.ATTRIBUTE5,
             h.ATTRIBUTE6,
             cursor (select NVL(l.LINE_NUM,0) LINE_NUM,
                            l.IFACE_TYPE,
                            l.DR_CR_DIRECTOR,
                            l.AMOUNT,
                            l.AMOUNT_BEG,
                            l.INVOICE_COMMENTS,
                            '''' || l.COA_COMPANY || '''' COA_COMPANY,
                            '''' || l.COA_DEPT || '''' COA_DEPT,
                            '''' || l.COA_ACCOUNt || '''' COA_ACCOUNT,
                            l.COA_SUB_ACCOUNT,
                            '''' ||l.COA_PRODUCT || '''' COA_PRODUCT,
                            '''' ||l.COA_PROJECT || '''' COA_PROJECT,
                            '''' ||l.COA_INTERCOMPANY || '''' COA_INTERCOMPANY,
                            to_char(l.PAYMENT_DATE,'YYYY-MM-DD') PAYMENT_DATE,
                            l.PAYMENT_AMOUNT,
                            l.PAYMENT_BANK_NUM,
                            l.VERIFICATION_AMOUNT,
                            l.VERIFICATION_AMOUNT_BEG,
                            l.VERIFICATION_DOC_NUM,
                            l.SOURCE_LINE_ID,
                            l.ATTRIBUTE1,
                            l.ATTRIBUTE2,
                            l.ATTRIBUTE3,
                            l.ATTRIBUTE4,
                            l.ATTRIBUTE5,
                            l.ATTRIBUTE6
                       from cux_ap_amt_to_ebs_iface_l l
                      where h.header_id = l.header_id
                      order by l.line_id 
                      ) item_lines
        from cux_ap_amt_to_ebs_iface_h h,hr_operating_units hou 
       where h.process_status = 'S'
       and h.organization_name = hou.name
             and h.header_id = 10149;
   ret := pljson_util_pkg.ref_cursor_to_json(dyn_ref);
   /*ret.print;*/
   dbms_output.put_line(replace(ret.to_char(true),'''',null)); 
end;

转为clob 大容量

上面那个直接打印的可能超出打印缓冲设置,下面这个可以转为clob方式

create or replace function ufx_plson_get_json_from_sql (i_sql in varchar2)
return clob
as
  -- Local variables here
  tstjson_list pljson_list;
  l_Result_json_clob clob;
begin
  -- Test statements here
  dbms_lob.createtemporary(l_Result_json_clob, true);

  tstjson_list := pljson_util_pkg.sql_to_json(i_sql);
  tstjson_list.to_clob(l_Result_json_clob);

  return l_Result_json_clob;
exception
  when others then raise;
end;
/
/* example */
select ufx_plson_get_json_from_sql (i_sql =>
          q'[ select sysdate, systimestamp, user from dual]') sql_result_as_json
  from dual;

oracle apex 方式

cursor 方式

有点：可以使实现嵌套打印

缺陷：如果字段是null值，则字段不显示，无法控制是否打印空置。

DECLARE
  c sys_refcursor;
BEGIN
  open c for select deptno,
                    dname,
                    cursor(select empno,
                                  ename
                             from emp e
                            where e.deptno=d.deptno) emps
               from dept d;
  apex_json.open_object;
  apex_json. write('departments', c);
  apex_json.close_object;
END;
 
{ "departments":[
      {"DEPTNO":10,
       "DNAME":"ACCOUNTING",
       "EMPS":[{"EMPNO":7839,"ENAME":"KING"}]},
      ...
     ,{"DEPTNO":40,"DNAME":"OPERATIONS","EMPS":null}] }
     

WRITE_CONTEXT Procedure

优点:通过write_context参数p_write_null 可以控制是否打印空值
缺点：If the query contains object type, collection or cursor columns, an error is raised. If the column is VARCHAR2 and the uppercase value is ‘TRUE’ or ‘FALSE’, boolean values are generated.

DECLARE
    l_context apex_exec.t_context;
begin
    l_context := apex_exec.open_query_context( 
                     p_location   => apex_exec.c_location_local_db,
                     p_sql_query  => q'#select * from dept#' );

   apex_json.open_object;
   apex_json.write_context( p_name => 'departments', p_context => l_context);
   apex_json.close_object;
 end;

{ "departments":[
     { "DEPTNO":10 ,"DNAME":"ACCOUNTING" ,"LOC":"NEW YORK" }
    ,{ "DEPTNO":20 ,"DNAME":"RESEARCH" ,"LOC":"DALLAS" } 
    ,{ "DEPTNO":30 ,"DNAME":"SALES" ,"LOC":"CHICAGO" } 
    ,{ "DEPTNO":40 ,"DNAME":"OPERATIONS" ,"LOC":"BOSTON" } ] }