Maybe the follwing explains it better
Dieter B�cherl, April 10, 2019 - 12:23 pm UTC
Connected to Oracle Database 12c Standard Edition Release 12.2.0.1.0
DECLARE
l_cursor BINARY_INTEGER;
l_exec BINARY_INTEGER;
l_ref_cursor SYS_REFCURSOR;
l_sql_statement VARCHAR2(2000) := 'select cursor(select ''- 087'' "TEL" from dual) from dual';
l_json_clob CLOB := '[{"CURSOR_x005F_x0028_SELECT_x005F_x0027_-87_x005F_x0027_TELFROMDUAL_x005F_x0029_":[{"TEL":-87}]}]';
BEGIN
dbms_output.put_line(l_json_clob);
IF l_json_clob IS NOT json THEN
dbms_output.put_line('invalid JSON');
ELSE
dbms_output.put_line('valid JSON');
END IF;
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor,
l_sql_statement,
dbms_sql.native);
l_exec := dbms_sql.execute(l_cursor);
l_ref_cursor := dbms_sql.to_refcursor(l_cursor);
apex_json.initialize_clob_output;
apex_json.open_object;
apex_json.write(p_cursor => l_ref_cursor);
-- here I don't know where the first character is generated (causing unbalanced brackets), so I simply cut it off
l_json_clob := dbms_lob.substr(lob_loc => apex_json.get_clob_output,
offset => 2);
apex_json.close_object;
dbms_output.put_line(l_json_clob);
IF l_json_clob IS NOT json THEN
dbms_output.put_line('invalid JSON');
ELSE
dbms_output.put_line('valid JSON');
END IF;
l_json_clob := NULL;
ROLLBACK;
END;
a probably better example
Dieter B�cherl, April 10, 2019 - 12:58 pm UTC
DECLARE
l_cursor BINARY_INTEGER;
l_ref_cursor SYS_REFCURSOR;
l_cursor1 BINARY_INTEGER;
l_ref_cursor1 SYS_REFCURSOR;
l_exec BINARY_INTEGER;
l_sql_statement VARCHAR2(2000) := 'select cursor(select ''-1'' "TEL" from dual) from dual';
l_json_clob CLOB;
BEGIN
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor,
l_sql_statement,
dbms_sql.native);
l_exec := dbms_sql.execute(l_cursor);
l_ref_cursor := dbms_sql.to_refcursor(l_cursor);
apex_json.initialize_clob_output;
apex_json.open_object;
apex_json.write(p_cursor => l_ref_cursor);
-- here I don't know where the first character is generated (causing unbalanced brackets), so I simply cut it off
l_json_clob := dbms_lob.substr(lob_loc => apex_json.get_clob_output,
offset => 2);
apex_json.close_object;
dbms_output.put_line(l_sql_statement);
dbms_output.put_line(l_json_clob);
IF l_json_clob IS NOT json THEN
dbms_output.put_line('invalid JSON');
ELSE
dbms_output.put_line('valid JSON');
END IF;
l_json_clob := NULL;
dbms_output.put_line('-- here I insert a space between the minus and the number');
l_sql_statement := 'select cursor(select ''- 1'' "TEL" from dual) from dual';
l_cursor1 := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor1,
l_sql_statement,
dbms_sql.native);
l_exec := dbms_sql.execute(l_cursor1);
l_ref_cursor1 := dbms_sql.to_refcursor(l_cursor1);
apex_json.initialize_clob_output;
apex_json.open_object;
apex_json.write(p_cursor => l_ref_cursor1);
-- here I don't know where the first character is generated (causing unbalanced brackets), so I simply cut it off
l_json_clob := dbms_lob.substr(lob_loc => apex_json.get_clob_output,
offset => 2);
apex_json.close_object;
dbms_output.put_line(l_sql_statement);
dbms_output.put_line(l_json_clob);
IF l_json_clob IS NOT json THEN
dbms_output.put_line('invalid JSON');
ELSE
dbms_output.put_line('valid JSON');
END IF;
l_json_clob := NULL;
ROLLBACK;
END;
April 10, 2019 - 5:29 pm UTC
I still don't understand what you're trying to do...
Both snippets produce valid JSON.
my output of previous snippet
Dieter B�cherl, April 11, 2019 - 6:16 am UTC
Oracle Database 12c Standard Edition Release 12.2.0.1.0
select cursor(select '-1' "TEL" from dual) from dual
[{"CURSOR_x005F_x0028_SELECT_x005F_x0027_-1_x005F_x0027__x005F_x0022_TEL_x005F_x0022_FROMDUAL_x005F_x0029_":[{"TEL":-1}]}]
valid JSON
-- here I insert a space between the minus and the number
select cursor(select '- 1' "TEL" from dual) from dual
[{"CURSOR_x005F_x0028_SELECT_x005F_x0027_-1_x005F_x0027__x005F_x0022_TEL_x005F_x0022_FROMDUAL_x005F_x0029_":[{"TEL":- 1}]}]
invalid JSON
Background: We're creating master-details reports with JASPER, with the SELECT for details in a CURSOR, e.g.
SELECT m.*, CURSOR(SELECT d.* from address d where d.m_id = m.id) from customer m where m.id = :mid
With a telefon extension containing a blank between the hyphen and the first digit, it produces invalid JSON --> see APEX_JSON.Write(Cursor): It uses XPath.Isnumber which allows the hyphen in a numeric string but JSON.Parse does not (at least in our case???)
Thanks again for trying to fowllow my apparently convoluted examples!
April 11, 2019 - 10:43 am UTC
OK, I understand now.
The problem is
{"TEL":- 1}
is INVALID JSON!
Numbers in JSON
can't contain spaces. To avoid this you need to strip whitespace out of numeric values before the conversion to JSON. Similarly you have to remove leading zeros (other than one immediately before the decimal point).
Correction of explanation
Dieter B�cherl, April 11, 2019 - 6:57 am UTC
... which allows a blank in a numeric string
Stripping blank from numeric string
Dieter from Germany, April 11, 2019 - 2:34 pm UTC
Thanks, Chris!
I'm glad that I finally managed to describe the problem, so you can understand it (it's not always easy for a non-native speaker).
Still shouldn't the validation rules for Xpath and JSON match, so both come to the same result:
Currently:
XPath.IsNumber('- 1') = TRUE; =>
APEX_JSON.Write generates "- 1"; -- number
JSON.Parse says "INVALID JSON";
Better:
1)
XPath.IsNumber('- 1') = FALSE; -- XPath.IsNumber does not allow blank between sign and first digit or decimal point
APEX_JSON.Write generates "'- 1'"; -- string
JSON.Parse says "VALID JSON";
or:
2)
XPath.IsNumber('- 1') = TRUE;
APEX_JSON.Write generates "-1"; -- number without blank
JSON.Parse says "VALID JSON";
or:
3)
XPath.IsNumber('- 1') = TRUE; =>
APEX_JSON.Write generates "- 1"; -- number with blank
JSON.Parse says "VALID JSON"; --parse allows blank(s) between sign and first digit or decimal point
April 15, 2019 - 11:13 am UTC
Hang on - where are you getting XPath.isNumber?
Because as far I as can tell, "- 087" is not an XML number:
with x as (
select xmltype (
'<adr>
<str>bachstr.</str><tel>- 087</tel><tel1>-87</tel1><tel2>-087</tel2>
</adr>'
) t
from dual
)
select t.*
from x, xmltable (
'/adr' passing x.t
columns
str path 'str',
tel number path 'tel'
) t;
ORA-01722: invalid number
with x as (
select xmltype (
'<adr>
<str>bachstr.</str><tel>- 087</tel><tel1>-87</tel1><tel2>-087</tel2>
</adr>'
) t
from dual
)
select t.*
from x, xmltable (
'/adr' passing x.t
columns
str path 'str',
tel varchar2(10) path 'tel'
) t;
STR TEL
bachstr. - 087
Example further extended
Dieter, April 12, 2019 - 8:04 am UTC
Oracle Database 12c Standard Edition Release 12.2.0.1.0
select p.PARAMETER, p.VALUE from V$NLS_PARAMETERS p;
PARAMETER VALUENLS_LANGUAGE GERMAN
NLS_TERRITORY GERMANY
NLS_CURRENCY €
NLS_ISO_CURRENCY GERMANY
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD.MM.RR
NLS_DATE_LANGUAGE GERMAN
NLS_CHARACTERSET WE8MSWIN1252
NLS_SORT GERMAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY €
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
DECLARE
l_cursor BINARY_INTEGER;
l_ref_cursor SYS_REFCURSOR;
l_cursor1 BINARY_INTEGER;
l_ref_cursor1 SYS_REFCURSOR;
l_exec BINARY_INTEGER;
l_sql_statement VARCHAR2(2000) := '
SELECT TO_CHAR(DATE''2019-04-11'', ''YYYY-MM-DD'') DT,
DATE''2019-04-11'' DT2,
''-1'' "TEL",
''09144'' "ZIP",
''+49'' "COUNTYCODE",
TO_CHAR(100000.92) NUM,
CURSOR(SELECT TO_CHAR(DATE''2019-04-11'', ''YYYY-MM-DD'') DT,
DATE''2019-04-11'' DT2,
''-1'' "TEL",
''09144'' "ZIP",
''+49'' "COUNTYCODE",
TO_CHAR(100000.92) NUM FROM DUAL)
ADR FROM DUAL';
l_json_clob CLOB;
"NUM" number;
"DUMP" varchar2(100);
BEGIN
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor, l_sql_statement, dbms_sql.native);
l_exec := dbms_sql.execute(l_cursor);
l_ref_cursor := dbms_sql.to_refcursor(l_cursor);
apex_json.initialize_clob_output;
apex_json.open_object;
apex_json.write(p_cursor => l_ref_cursor);
-- here I don't know where the first character is generated (causing unbalanced brackets), so I simply cut it off
l_json_clob := dbms_lob.substr(lob_loc => apex_json.get_clob_output,
offset => 2);
apex_json.close_object;
dbms_output.put_line(l_sql_statement);
dbms_output.put_line(l_json_clob);
IF l_json_clob IS NOT json THEN
dbms_output.put_line('invalid JSON');
ELSE
dbms_output.put_line('valid JSON');
END IF;
select dump(json_value(l_json_clob, '$.ADR.ZIP'), 1016)
into "DUMP"
from dual;
dbms_output.put_line('DUMP ZIP: ' || "DUMP");
"NUM" := json_value(l_json_clob, '$.NUM');
dbms_output.put_line('PRINT NUM: ' || "NUM");
"NUM" := json_value(l_json_clob, '$.ADR.NUM');
dbms_output.put_line('PRINT ADR.NUM: ' || "NUM");
exception
when others then
dbms_output.put_line(SQLERRM || ': ' ||
json_value(l_json_clob, '$.ADR.NUM'));
END;
Notice the formatting discrepancies of DT2 and ADR.DT2 !!
Notice the formatting discrepancies of NUM and ADR.NUM !!
Apparently APEX_JSON.Write(Cursor) cannot handle NLS_SETTINGS, so I consider this as bug ...
April 16, 2019 - 10:04 am UTC
Running that gives me:
[{"DT":"2019-04-11","DT2":"11-APR-2019 00:00:00","TEL":-1,"ZIP":"09144","COUNTYCODE":"+49","NUM":"100000,92","ADR":[{"DT":"2019-04-11","DT2":"2019-04-11T00:00:00Z","TEL":-1,"ZIP":"09144","COUNTYCODE":"+49","NUM":"100000,92"}]}]
valid JSON
DUMP ZIP: Typ=1 Len=5 CharacterSet=WE8DEC: 30,39,31,34,34
PRINT NUM: 100000,92
PRINT ADR.NUM: 100000,92
I'm not sure what behaviour you're expecting here. You've already to_chared DT and ADR.NUM in your query. Using the NLS settings.
So at this point they're no longer dates or numbers. They're strings. Which don't conform to the JSON standard for these data types. So there's no way they can become anything other than a string in your JSON.
Description in APEX_JSON Package Specification
Dieter B�cherl, April 15, 2019 - 12:44 pm UTC
Hi Chris,
thank you for looking into this!
I came to that conclusion based on the descriptions of WWV_FLOW_JSON.WRITE(sys.xml_type) and WWV_FLOW_JSON.WRITE(sys_refcursor).
Am I wrong?
Kind regards, Dieter
--==============================================================================
-- Write array attribute.
--
-- The procedure uses a XSL transformation to generate JSON. To determine the
-- JSON type of values, it uses the following rules:
-- * If the value is empty, it generates a null value
-- * If upper(value) is TRUE, it generates a boolean true value
-- * If upper(value) is FALSE, it generates a boolean false value
-- * If the XPath number function returns true, it emits the value as is
-- * Otherwise, it enquotes the value (i.e. treats it as a JSON string)
--
-- PARAMETERS
-- * p_value The value to be written. The XML is converted to JSON.
--==============================================================================
procedure write (
p_value in sys.xmltype );
--==============================================================================
-- Write an array with all rows that the cursor returns. each row is a separate
-- object.
--
-- If the query contains object type, collection or cursor columns, the
-- procedure uses write(<xmltype>) to generate JSON. See write(<xmltype>) for
-- further details. Otherwise, it uses DBMS_SQL to fetch rows and the write()
-- procedures for the appropriate column data types for output. If the column
-- type is varchar2 and the uppercase value is 'TRUE' or 'FALSE', it generates
-- boolean values.
--
-- PARAMETERS
-- * p_cursor The cursor
--
-- EXAMPLE
-- Write an array containing JSON objects for departments 10 and 20.
--
-- declare
-- c sys_refcursor;
-- begin
-- open c for select deptno, dname, loc from dept where deptno in (10, 20);
-- apex_json.write(c);
-- end;
--
-- [ { "DEPTNO":10 ,"DNAME":"ACCOUNTING" ,"LOC":"NEW YORK" }
-- , { "DEPTNO":20 ,"DNAME":"RESEARCH" ,"LOC":"DALLAS" } ]
--==============================================================================
procedure write (
p_cursor in out nocopy sys_refcursor );
April 16, 2019 - 10:06 am UTC
So how has that led you to your conclusions above?
String of logic
Dieter B�cherl, April 16, 2019 - 11:36 am UTC
Well, Connor
-- If the query contains object type, collection or cursor columns, the
-- procedure uses write(<xmltype>) to generate JSON.
this leads me to:
-- The procedure uses a XSL transformation to generate JSON. To determine the
-- JSON type of values, it uses the following rules:
...
-- * If the XPath number function returns true, it emits the value as is
-- * Otherwise, it enquotes the value (i.e. treats it as a JSON string)
How would you interpret these statements?
Regards, Dieter
April 16, 2019 - 3:09 pm UTC
And how have you verified that the XPath number function returns true for those values?
Verification?
Dieter B�cherl, April 17, 2019 - 7:38 am UTC
Hi Connor,
I am a trusting person :-)
When a developer says in the documentation he doing it a certain way, then I believe him, even more when the output seems to support that claim:
My input: A statement with a cursor variable containing a string value of "- 1"
The output: JSON that doesn't enclose that value in double apostrophes, treating it a number ....
Who am I to doubt the words of the person supplying the functionality (or do you even suggest that I unwrap the package, sniffing around in someone else's intellectual proper)?
One more question: What is your opinion on APEX_JSON write ignoring the NLS_SETTINGS?
In my post from April 12, 2019 - 8:04 am UTC, I entered a script that shows formatting discrepancies between the data in the outer statement and the inner (CURSOR) statement:
The output was:
[{"DT":"2019-04-11","DT2":"11.04.19","TEL":-1,"ZIP":"09144","COUNTYCODE":"+49","NUM":"100000,92","ADR":[{"DT":"2019-04-11","DT2":"2019-04-11T00:00:00Z","TEL":-1,"ZIP":"09144","COUNTYCODE":"+49","NUM":100000.92}]}]
NUM vs. ADR.NUM: The first being interpreted as string (with a decimal comma) even when my NLS_SETTINGS are German, the second is treated as a number (with decimal point) throwing later an exception ...
Regards, Dieter
April 17, 2019 - 1:05 pm UTC
Trust but verify.
Looks to me like XPath expressions consider the value "- 1" (minus space one) to be "Not a Number":
with x as (
select xmltype ( '<a>- 1</a>' ) doc from dual
union all
select xmltype ( '<a>-1</a>' ) doc from dual
)
select xmlquery ( '/a' passing doc returning content ) val,
xmlquery ( 'number(a)' passing doc returning content ) is_number
from x;
VAL IS_NUMBER
<a>- 1</a> NaN
<a>-1</a> -1.0E+000
The NLS issue looks similar to Bug 25551233 which is fixed in 5.1.1.00.03.
.. and the argonauts
Racer I., April 17, 2019 - 11:32 am UTC
Hi,
To muddy the waters further :
https://stackoverflow.com/questions/3854345/xpath-test-if-node-value-is-number - XPath seems not to have an isNumber function
- The xpath.number-function returns a number or NaN
- using the latter as boolean would yield number(0) = false.
which throws some doubt on
> If the XPath number function returns true...
About APEX_JSON ignoring NLS-settings maybe ask in an APEX forum? What about regular JSON_ARRAY/OBJECT (12.2)?
regards,
JSON generators *should* ignore NLS settings
Stew Ashton, April 18, 2019 - 8:12 am UTC
JSON is defined in two standards documents:
https://tools.ietf.org/html/rfc8259 http://www.ecma-international.org/publications/files/ECMA-ST/ECMA-404.pdf In both documents, the decimal indicator is the decimal
point:
"decimal-point = %x2E ; ."
NLS settings convert non-string data types to strings for display to
end users. JSON was conceived for data interchange among
programming languages, so NLS settings are and should be completely ignored.
Extensible Markup Language (XML) is different, since it is used both as a markup language for documents and as a data exchange mechanism. Oracle respects NLS settings when generating XML - but not when generating JSON.
Look at the examples below (in 18c). The XML has a decimal comma, the JSON has a decimal point.
SQL> alter session set nls_numeric_characters=',.';
Session altered.
SQL> select xmlelement(N, 1.1) from dual;
XMLELEMENT(N,1.1)
-----------------
<N>1,1</N>
SQL> select json_object('N' value 1.1) from dual;
JSON_OBJECT('N'VALUE1.1)
------------------------
{"N":1.1}
Best regards,
Stew
April 18, 2019 - 9:39 am UTC
Good point Stew - but the issue here is that APEX_JSON does the conversion via XML. So if you make the decimal separator comma, the XML converts 1.1 to 1,1. Which of course, is no longer a JSON number...
Dieter B�cherl, April 23, 2019 - 2:49 pm UTC
Thanks, Connor!
That's exactly the point!
Since JSON is a NLS-independent data excchange format, APEX_JSON.write(CURSOR) shouldn't try any NLS conversion (or shouldn't use any implicit NLS conversion).
If a column is a char column, it's value should be enclosed in apostrophes, if the column is a number column it shouldn't. Basically each and every column in the resultset should be treated the way it was queried.
Perhaps DBMS_SQL.describe2 would be a better way to discover the columns' data type than trying to shortcut that by using XPath ...
Regards, Dieter
April 24, 2019 - 1:43 pm UTC
Dieter B�cherl, April 23, 2019 - 2:54 pm UTC
Sorry,
I meant:
Thank you Chris!
April 24, 2019 - 1:40 pm UTC
:)
How we solved the problem ...
Dieter B�cherl, April 26, 2019 - 3:57 pm UTC
Hi Chris,
just to keep you informed:
The problem with the telephone column data containing e.g. '- 1' was resolved by:
1) I asked my colleague to create an APEX input field that optionally replaces a minus = CHR(45) in the input field with a hyphen = CHR(150).
They both look the same but the XPath number function realizes that this cannot be a number.
2) We updated the data in the field and until we can roll out the new input field, a trigger takes care of the replacement.
The inconsistent behaviour of APEX_JSON.write(CURSOR) was resolved by:
1) generate the data as XML using DBMS_XGEN.get_xml(SYS_REFCURSOR) with NLS_TERRITORY 'Germany'
2) ALTER SESSION SET NLS_TERRITORY = 'America'
3) using APEX_JSON.write(XML) then preserves the formatting in the XML since all data formatted German will be treated as strings
4) reset the NLS settings to previous state
Thank you for your help and have a nice weekend!
Dieter
April 29, 2019 - 12:39 pm UTC
Glad you got it resolved.
BTW: one minor glitch
Dieter Bücherl, April 28, 2019 - 7:47 am UTC
Hi Chris,
We have been discussing language settings problem throughout this thread, but it just came to my mind, that the ASKTOM application itself doesn't handle them correctly:
My last name is Bücherl, but it is displayed as B?cherl :-)
Dieter
April 29, 2019 - 12:42 pm UTC
Yet it appears correctly in this review :)
Did you use the same process for typing umlaut-u in this review?
Umlaut
Dieter B�cherl, April 29, 2019 - 12:59 pm UTC
Hi Chris,
One comment was created using Safari (MacBook), the other Mozilla (Windows 7), otherwise no differences (just hitting the ü-key)
Dieter
April 30, 2019 - 2:34 am UTC
And this one messed up.... we'll investigate
Umlaut 2
A reader, April 29, 2019 - 1:01 pm UTC
This one Safari, previous Windows
Umlaut 3
Dieter Bücherl, April 29, 2019 - 1:02 pm UTC
Safari
April 30, 2019 - 2:34 am UTC
Interesting - this is comes in fine.