Wednesday, May 31, 2023

Handling of non-ASCII characters in JSON objects in a non-AL32UTF8 database

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
declare
 js   json_object_t;
begin
 js := json_object_t.parse('
{
  "name"       : "whatever",
  "values" : [
              {"name" : "x", "type" : "number", "value" : 2023},
              {"name" : "y", "type" : "string", "value" : "some non-ASCII char àùì"}
             ]
}
');
end;
/

Error report -
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 4
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.

 

Dealing with non-ASCII characters inside JSON objects in a non-AL32UTF8 database requires definitely some extra work, depending also on the final use of the information.

 For instance, if JSON's contents are consumed by a browser, the following escape method works well:

declare
js json_object_t;
begin
js := json_object_t.parse(REGEXP_REPLACE(ASCIISTR('
{
"name" : "whatever",
"values" : [
{"name" : "x", "type" : "number", "value" : 2023},
{"name" : "y", "type" : "string", "value" : "some non-ASCII char àùì"}
]
}
'), '\\([0-9A-F]{4})', '&#x\1;'));
end;
/

Escaping the characters with ASCIISTR only doesn't work because the resulting presence of the "\" characters also breaks the parser as "\" is normally used to escape JSON "reserved" characters, such as \ and ".

If the data need to be converted back from the JSON then the reverse process must be applied:

select
unistr(regexp_replace('some non-ASCII char àùì',
'&#x([0-9A-F]+);',
'\\\1',1,0)) original
from dual;

 

If a lossy character translation is acceptable then CONVERT function could be used.

declare
js json_object_t;
begin
js := json_object_t.parse(CONVERT('
{
"name" : "whatever",
"values" : [
{"name" : "x", "type" : "number", "value" : 2023},
{"name" : "y", "type" : "string", "value" : "some non-ASCII char àùì"}
]
}
','US7ASCII'));
end;
/
-- equivalent to:
declare
 js   json_object_t;
begin
 js := json_object_t.parse('
{
  "name"       : "whatever",
  "values" : [
              {"name" : "x", "type" : "number", "value" : 2023},
              {"name" : "y", "type" : "string", "value" : "some non-ASCII char aui"}
             ]
}
');
end;
/

Please note that in a database with AL32UTF8 character set, the first PL/SQL block would have been processed just fine without the need of escaping anything.

Tuesday, May 30, 2023

Fixing the SRID in a spatial geometry column

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
CREATE INDEX test_geom_srid_spidx ON test_geom_srid (geom)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX
Error report - ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-13249: internal error in Spatial index: [mdidxrbd] ORA-13249: Error in Spatial index: index build failed ORA-13249: Error in spatial index: [mdrcrtxfergm] ORA-13249: Error in spatial index: [mdpridxtxfergm] ORA-13200: internal error [ROWID:AAHlT1AABAAHWddAAA] in spatial indexing. ORA-13206: internal error [Invalid geometry; check ROWID] while creating the spatial index ORA-13365: layer SRID does not match geometry SRID ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10 29855. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine" *Cause: Failed to successfully execute the ODCIIndexCreate routine. *Action: Check to see if the routine has been coded correctly.

 

This problem is caused by an attempt to create a spatial index on a table where a SRID n has been specified in USER_SDO_GEOM_METADATA, but the geometries in the table are not associated to the same SRID, typically they have a null SRID.

If the coordinates of the geometries are correct for the expected SRID, you can fix the value stored inside the geometry objects as follows, provided you have a primary or unique key defined for the table:

update tab a
   set geom = (select sdo_geometry(
b.geom.sdo_gtype,
<new_srid>
,
b.geom.sdo_point,
b.geom.sdo_elem_info,
b.geom.sdo_ordinates) 
from tab b 
where b.id = a.id);

After fixing the geometries the spatial index creation should succeed, after dropping any invalid index that might have been left after the initial attempt.

See message translations for ORA-13365.

Friday, March 10, 2023

Yet another curious syntax with little documentation: SELECT FROM RELATIONAL("schema"."table")

Have you ever seen this syntax?

SELECT * FROM RELATIONAL("SCOTT"."EMP");

For tables containing only primitive data types it returns the same results as it would the normal query:

SELECT * FROM "SCOTT"."EMP"; 

For tables containing object data types, however, it splits object's underlying attributes into separate columns:

CREATE TABLE GEOM_TABLE(
ID NUMBER,
GEOM SDO_GEOMETRY
);

INSERT INTO GEOM_TABLE(ID, GEOM)
VALUES(1, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(1680337.2, 4973436.1, 1680337.3, 4973436.2, 1680337.2, 4973436.9, 1680337.2, 4973436.1)));

 
SELECT * FROM RELATIONAL("GEOM_TABLE"); -- I omitted the schema

ID GEOM SYS_NC00003$ SYS_NC00004$ SYS_NC00005$ SYS_NC00006$ SYS_NC00007$ SYS_NC00008$ SYS_NC00009$
1 02 2003         MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1) MDSYS.SDO_ORDINATE_ARRAY(1680337.2, 4973436.1, 1680337.3, 4973436.2, 1680337.2, 4973436.9, 1680337.2, 4973436.1)

I didn't investigate what that 02 means in the GEOM column.

As far as I could see there is no official document describing this syntax, however it must be there since ages because I found it in queries created by an old java based framework in 2009.

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