CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex
Roberto Hernandez
Roberto Hernandez

Reputation: 8528

APEX_JSON print output using clob

Let me try to explain what is happening. I created a simple sql script to run an apex_json program and creates a json file. Until here, everything fine. However, when I look inside the content of the JSON file created, the lines are broken and the file contains some carriage returns where there should not be. I tried different combinations but I hit a wall.

This is my code:

Here the content of my json_file.sql ( script which contains the 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;
    v_clob := clob;
    procedure print_clob_to_output (p_clob in clob)
    is
      l_offset     int := 1;
    begin
        --dbms_output.put_line('Print CLOB');
    loop
        exit when l_offset > dbms_lob.getlength(p_clob);
        dbms_output.put_line( dbms_lob.substr( p_clob, 32767, l_offset ) );
        l_offset := l_offset + 32767;
    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;
     v_clob :=  APEX_JSON.GET_CLOB_OUTPUT ;
     APEX_JSON.FREE_OUTPUT;  
     PRINT_CLOB_TO_OUTPUT(p_clob => v_clob);
  end loop;
END;
/

spool off 

exit

I run this program using a shell script that basically invokes the sqlplus this way

SQL> whenever sqlerror exit failure;
whenever oserror exit failure;
set serveroutput on size unlimited pages 0 lines 180 long 99999999 head off verify off feed off
@json_query.sql file.json

The json is created and here is when I find the broken lines ( see below the values en the key EndDate ):

$ cat -vte file.json
{$
"Date":"2020-03-31"$
,"DomainCode":"00001"$
,"LegalEntityCode":"00055"$
,"Keys":[$
{$
"NativeKey":"129582692"$
,"MasterKey":"329323111430011996"$
,"EndDate":"9999-12-31"$
}$
,{$
"NativeKey$
":"14735034"$
,"MasterKey":"329390935000331576"$
,"EndDate":"9999-12-31"$
}$
,{                                                                                                         $
$
"NativeKey":"98102571"$
,"MasterKey":"329361261430632555"$
,"EndDate":"9999-12-31"$
}$
,{$
"NativeKey":"175080315"$
,"MasterKey":"329361251430284082"$
,"EndDate":"9999-12-31"$
}$
,{$
"Nativ$
eKey":"34262142"$
,"MasterKey":"329323245070279903"$
,"EndDate":"9999-12-31"$
                                                                                                         $
}$
,{$
"NativeKey":"84094570"$
,"MasterKey":"329395105070385872"$
,"EndDate":"9999-12-31"$
}$
,{$
"NativeKey":"43337038"$
,"MasterKey":"329323085070265746"$
,"EndDate":"9999-12-31"$
}$
,{$
"Na$
tiveKey":"86265510"$
,"MasterKey":"329300305070004470"$
,"EndDate":"9999-12-3                                                                                                         $
1"$
}$
,{$
"NativeKey":"96926906"$
,"MasterKey":"329323031430673339"$
,"EndDate":"9999-12-31"$
}$
,{$
"NativeKey":"96760800"$
,"MasterKey":"329323031430666865"$
,"EndDate":"9999-12-31"$
}$
,{$
$
"NativeKey":"94063822"$
,"MasterKey":"329323031430571553"$
,"EndDate":"9999-1                                                                                                         $
2-31"$
}$
,{$
"NativeKey":"14529231"$
,"MasterKey":"329390935000077722"$
,"EndDate":"9999-12-31"$
}$
,{$
"NativeKey":"173183320"$
,"MasterKey":"329344461430004834"$
,"EndDate":"9999-12-31"$
}$
$
,{$
"NativeKey":"34044987"$
,"MasterKey":"329323245070056084"$
,"EndDate":"99                                                                                                         $
99-12-31"$
}$
]$
}$

Someone knows what is happening here ? I even tried to store the clob in a table and then print it from there with a function, but it was already broken when the data was inserted. I read somewhere that the APEX_JSON uses the HTP buffer to write the data, but I don't understand what I am doing wrong or why the json is corrupted.

Any help is more than appreciated Thank you

Upvotes: 0

Views: 2291

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

The problem isn't with APEX_JSON, You're getting an extra line break every 180 characters - which is your line length - with the first one appearing in the second NativeKey key name.

The issue is that the line breaks within your CLOB are tripping over the SQL*Plus line length handling. Even if you construct a CLOB with the clean JSON contents manually, and do dbms_output.put_line(v_clob, 200, 1);, it will still add a line break in the same place. There doesn't seem to be anything you can do about that as far as I can tell; the closest you can get is to set linesize very high, but large CLOBs will break it eventually. Switching to SQLcl seems to avoid the problem, incidentally.

You can fix it in your print_clob_to_output procedure by splitting the CLOB into individual lines and printing them one by one, instead of splitting it based just on number of characters as you are now. (Incidentally, your procedure was slightly broken anyway; you'd have got an extra line break every 32767 characters, on top of this issue, because put_line adds one. You'd need to look at that if you did decide to switch to SQLcl.)

declare
    v_clob clob;
    procedure print_clob_to_output (p_clob in clob)
    is
      l_offset     pls_integer := 1;
      l_chars      pls_integer;
    begin
        --dbms_output.put_line('Print CLOB');
        loop
            exit when l_offset > dbms_lob.getlength(p_clob);
            l_chars := dbms_lob.instr(p_clob, chr(10), l_offset, 1);
            if l_chars is null or l_chars = 0 then
                l_chars := dbms_lob.getlength(p_clob) + 1;
            end if;
            dbms_output.put_line('> ' || dbms_lob.substr(p_clob, l_chars - l_offset, l_offset));
            l_offset := l_chars + 1;
        end loop;
    end print_clob_to_output;
begin
...

This version looks for the next newline character - chr(10) - in the CLOB, from the current offset, and the substring is then taken from the offset to the character before that. The substring, which represents a complete line without that newline character, and when that line is output with put_line it adds its own. That chr(10) is discarded by the l_offset := l_chars + 1 line. And there's a bit of handling in case there are no line breaks, or the last line doesn't end with a line break.

The buffer handling - I think - means SQL*Plus then isn't confused and doesn't wrap the output onto extra lines.

Upvotes: 1

Related Questions