Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

Parser Problem with £ sign

Matt Nolan
2012-08-30
2013-05-28
  • Matt Nolan
    Matt Nolan
    2012-08-30

    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
    Matt Nolan
    2012-08-30

    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
    Matt Nolan
    2012-08-30

    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
    Ja Steam
    2013-01-07

    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.