Parser Problem with £ sign

[Matt Nolan]

Hi Jonas

I have a problem with the parser with the following test case producing:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Back Trace: ORA-06512: at "FOEX_010000.JSON_PARSER", line 428

Here's a test case

DECLARE
  l_json JSON;
BEGIN
  l_json := json('{"summaryRenderer": /**/Ext.util.Format.numberRenderer("£000,000,000,000,000.00")/**/}');
END;
/

and the problem is with this code notably the pound sign, if you change it to $ it works Ok.

I can work around the problem using:

  l_json.put('summaryRenderer',JSON_VALUE('Ext.util.Format.numberRenderer("£000,000,000,000,000.00")',FALSE));

I just thought I'd bring it to your attention.

Cheers
Matt

[Matt Nolan]

Actually the workaround causes a problem now in the printer when issuing l_json.htp

ORA-06512: at "SYS.DBMS_LOB", line 1064 ORA-06512: at "JSON_PRINTER", line 606 ORA-06512: at "JSON", line 285 ORA-06512

[Matt Nolan]

Ok I resolved the printer problem by increasing the buffer

e.g.

  procedure htp_output_clob(my_clob clob, jsonp varchar2 default null) as
    /*amount number := 4096;
    pos number := 1;
    len number;
    */
    l_amt    number default 30;
    l_off   number default 1;
    l_str   varchar2(32767);

I suspect it's because I'm using a multi byte charcter set.

NLS_CHARACTERSET
AL32UTF8

[Ja Steam]

Hello Matt & Jonas,

On the same token I'm using the same character set as Matt. I hasd to this because of BASE64 encoding problems when dealing with PDF inside the oracle database.

DECLARE 
ret json_list; 
jsonObj json; 
BEGIN 
jsonObj := json(); 
ret := json_dyn.executeList('SELECT * FROM ori_customers WHERE cust_id = '|| wwv_flow.g_x01); 
jsonObj.put('results',ret); 
owa_util.mime_header('text/javascript', FALSE );
htp.p('Cache-Control: no-cache');
htp.p('Pragma: no-cache');
owa_util.http_header_close;
jsonObj.htp();
END;

Output is:

{"results":[{"CUST_ID":58,"CUST_NAME":"DE Kuchen backen Co.","ADDRESS":"Hauptstra\\u00DFe 43, Wolburgsweg 36c, Webergasse 7-9","CITY":"Berlin","STATE":"","ZIP":"121990","CODE":"DEKBC","ABBREVIATION":"DEKBC","LANG_CODE":"DE","PRIMARY_CONTACT":"Jonas M\\u00FCller","CONTRACT_NO":"6621","EMPLOYEE_ID":211,"COUNTRY_ID":302,"EMPLOYEE_ID_2":44,"EMAIL":"support@dekbc.de","EM_GRID":"","START_TIME":"1:45 AM","END_TIME":"7:00 PM","CONTRACT_START_DATE":"2011-08-20 11:00:00","CONTRACT_END_DATE":"2017-08-20 15:00:00","STATUS_ID":1}]}

Problem with this is that The Address and Contact come out as strings.

However when I do:

DECLARE
vJSON_Str varchar2(32767) :='';
CURSOR c1 is SELECT * FROM ori_customers WHERE cust_id = wwv_flow.g_x01;
BEGIN
  -- enforce security
  -- if not is_valid_session then return; end if;
  -- set mime header
  owa_util.mime_header('text/javascript', FALSE );
  htp.p('Cache-Control: no-cache');
  htp.p('Pragma: no-cache');
  owa_util.http_header_close;
  -- passback data
  FOR cs IN c1 LOOP
  vJSON_Str := '{"CUST_ID":'|| cs.CUST_ID ||',
  "STATUS_ID":"'||cs.STATUS_ID||'",
  "CUST_NAME":"'||cs.CUST_NAME||'",
  "ADDRESS":"'||cs.ADDRESS||'",
  "CITY":"'||cs.CITY||'",
  "STATE":"'||cs.STATE||'",
  "COUNTRY_ID":"'||cs.COUNTRY_ID||'",
  "ZIP":"'||cs.ZIP||'",
  "CODE":"'||cs.CODE||'",
  "ABBREVIATION":"'||cs.ABBREVIATION||'",
  "LANG_CODE":"'||cs.LANG_CODE||'",
  "EMAIL":"'||cs.EMAIL||'",
  "PRIMARY_CONTACT":"'||cs.PRIMARY_CONTACT||'",
  "CONTRACT_NO":"'||cs.CONTRACT_NO||'",
  "EMPLOYEE_ID":"'|| cs.EMPLOYEE_ID||'",
  "EMPLOYEE_ID_2":"'||cs.EMPLOYEE_ID_2||'",
  "EM_GRID":"'||cs.EM_GRID||'"},';
  END LOOP;
  
  vJSON_Str := TRIM(trailing ',' FROM vJSON_Str);
  HTP.P('({"results":['||vJSON_Str||']})');
END;

The outpu is:

({"results":[{"CUST_ID":58, 
  "STATUS_ID":1, 
  "CUST_NAME":"DE Kuchen backen Co.", 
  "ADDRESS":"Hauptstra\u00DFe 43, Wolburgsweg 36c, Webergasse 7-9", 
  "CITY":"Berlin", 
  "STATE":"", 
  "COUNTRY_ID":"302", 
  "ZIP":"121990", 
  "CODE":"DEKBC", 
  "ABBREVIATION":"DEKBC", 
  "LANG_CODE":"DE", 
  "EMAIL":"support@dekbc.de", 
  "PRIMARY_CONTACT":"Jonas M\u00FCller", 
  "CONTRACT_NO":"6621", 
  "EMPLOYEE_ID":"211", 
  "EMPLOYEE_ID_2":"44", 
  "EM_GRID":""}]})

The difference is the double backslash.

Could any of you suggest a fix.