PL/JSON creates strings as numbers

[Dave]

I am using sql_to_json to create some JSON's from my Oracle database.  These are sent via web service to a mobile device.  All is working well except…

When on of my VARCHAR2 columns contains only digits (e.g. the postal_code), PL/JSON creates the node as a number - not surrounded by quotes.  This causes problems on the target app because it want to treat it as a number.

How can I force sql_to_json to respect the data type of the base table OR what commands can I run on the resulting JSON to manually correct the data type before calling your to_clob procedure?

[James Sumners]

I believe you can thank Oracle for that favor. The sql_to_json method returns a json_list which is merely a json_value_array (i.e. a "table of json_value"). When you print out your JSON object, the procedure simply loops through all values in the table and calls json_value.get_type to determine the type. This function simply asks Oracle what the type is and then adds that type to the clob as would be appropriate for the JSON representation. Thus, Oracle thinks your varchar2 is really a number since the value is numeric, not alphanumeric.

[Jonas Krogsboell]

Switch to the json_dyn package, or correct the underlying xslt in sql_to_json (may be very difficult or impossible). Then this should be possible:

'select to_char(1) as "test" from dual' -> [{"test": "1"}]
'select to_number('1') as "test" from dual' -> [{"test": 1}]