Tuesday, July 30, 2019

JSON, the Euro symbol and a WE8MSWIN1252 character set database

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Trying to parse a JSON_OBJECT_T string containing the € (euro) character on a WE8MSWIN1252
12.2.0.1.0 database returns the following error:
 
declare
 j json_object_t;
begin
 j := json_object_t.parse('{"currency":"€"}');
end;
/
 
ORA-40441: JSON syntax error
ORA-06512: at "SYS.JDOM_T", line 4
ORA-06512: at "SYS.JSON_OBJECT_T", line 86
ORA-06512: at line 10
40441. 00000 -  "JSON syntax error"
*Cause:    The provided JavaScript Object Notation (JSON) data had invalid
           syntax and could not be parsed.
*Action:   Provide JSON data with the correct syntax.

The only workaround I found consists in converting € into € before parsing and back after retrieving the JSON value.
The same problem occurs with the Yen symbol ¥ and the Sterling Pound symbol £.

I also made a test on an AL32UTF8 database where everything works smoothly without having to handle these symbols in a special way.


The pleasures of working with an 8-bit database.

Wednesday, July 03, 2019

Houston, we've got a problem with JSON PUT method

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

In brief, in Oracle 12.2.0.1 there is some problem with PUT method of JSON_OBJECT_T, for some reason numbers are not recognized as such, but they are stored as scalar.
The workaround is to use JSON_OBJECT_T.PARSE in combination with JSON_OBJECT SQL function for entering numbers (see the script below).
Unfortunately, in turn, JSON_OBJECT_T.PARSE won't recognize dates and timestamps as such, but this behavior is explained in the documentation so it doesn't come as a surprise at least.
See also a previous entry regarding the curious handling of dates and timestamps.


Hopefully Oracle will fix this in a future release.

set SERVEROUTPUT ON
declare
  je     json_element_t;
  jo     json_object_t;
  keys   json_key_list;
begin
  jo := new json_object_t;

  dbms_output.put_line('PUT method doesn''t store "value" as number, but as scalar');
  jo.put('a_string','year');
  jo.put('a_number',2019);   -- this is not stored as number but as scalar, casting as number doesn't fix it.
  jo.put('a_boolean', true);
  jo.put('a_date', sysdate);
  jo.put('a_timestamp', cast(systimestamp as timestamp));  -- curiously you need to cast this value to get it as timestamp

  keys := jo.get_keys;
  for j in 1..keys.count
  loop
  
    je := jo.get(keys(j));
    if je.is_string then
      dbms_output.put_line(keys(j) || ' is string');
    elsif je.is_number then
      dbms_output.put_line(keys(j) || ' is number');
    elsif je.is_date then
      dbms_output.put_line(keys(j) || ' is date');
    elsif je.is_timestamp then
      dbms_output.put_line(keys(j) || ' is timestamp');
    elsif je.is_boolean then
      dbms_output.put_line(keys(j) || ' is boolean');
    elsif je.is_scalar then
      dbms_output.put_line(keys(j) || ' is scalar');
    elsif je.is_object then
      dbms_output.put_line(keys(j) || ' is object');
    elsif je.is_array then
      dbms_output.put_line(keys(j) || ' is array');
    elsif je.is_null then
      dbms_output.put_line(keys(j) || ' is null');
    end if;
    dbms_output.put_line(jo.get(keys(j)).to_string);
  end loop;
  
  dbms_output.put_line('');
  dbms_output.put_line('workaround using PARSE method, correctly stores value as number, it doesn''t recognize a_date as date or a_timestamp as timestamp but as string (this is expected and documented however)');
  jo := json_object_t.parse(json_object('a_string' value 'year', 'a_number' value 2019, 'a_boolean' value false, 'a_date' value sysdate, 'a_timestamp' value systimestamp));

  keys := jo.get_keys;
  for j in 1..keys.count
  loop
  
    je := jo.get(keys(j));
    if je.is_string then
      dbms_output.put_line(keys(j) || ' is string');
    elsif je.is_number then
      dbms_output.put_line(keys(j) || ' is number');
    elsif je.is_date then
      dbms_output.put_line(keys(j) || ' is date');
    elsif je.is_timestamp then
      dbms_output.put_line(keys(j) || ' is timestamp');
    elsif je.is_boolean then
      dbms_output.put_line(keys(j) || ' is boolean');
    elsif je.is_scalar then
      dbms_output.put_line(keys(j) || ' is scalar');
    elsif je.is_object then
      dbms_output.put_line(keys(j) || ' is object');
    elsif je.is_array then
      dbms_output.put_line(keys(j) || ' is array');
    elsif je.is_null then
      dbms_output.put_line(keys(j) || ' is null');
    end if;
    dbms_output.put_line(jo.get(keys(j)).to_string);
  end loop;

end;
/

Tuesday, June 11, 2019

Nice bug in Oracle 12.2.0.1 affecting function NUMTODSINTERVAL

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

select numtodsinterval(11505,'SECONDS') from dual;

This simple query killed my sqldeveloper session.
I mistakenly wrote SECONDS instead of SECOND and boom!

I ignore if the same happens with the plurals of the other units.

I wonder what kind of parameter checking is done in this function...

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles