I have a few questions about JSON functions, both in the database (Oracle SQL) and in PL/SQL. Alas, I wasn't able to find the answers (mostly, the confirmation of answers I have found through experimentation) in the documentation; if in fact documentation does exist, links would be greatly appreciated.
My DB version is 12.2.0.1 (no patches), but as far as I can tell there are no differences through version 21.
ASIS option
I have seen the ASIS option in a few places; for example Tim Hall mentions it here: https://oracle-base.com/articles/12c/indexing-json-data-in-oracle-database-12cr1 He shows that the ASIS option is important in the context of function-based indexes using functions like JSON_VALUE.
Well - where is this option documented? The railroad diagrams for JSON_VALUE and JSON_QUERY make no mention of it; and I downloaded the full JSON Developer's Guide (305 pages) for Oracle 21c in PDF and I did a keyword search for it - zero matches.
It also seems that this option is only available in the SQL version of the functions; in PL/SQL it throws an error (if used as PL/SQL functions, and not part of embedded SQL statements).
Documentation for the PL/SQL versions of the functions
This brings me to the more general question - where are the PL/SQL versions of the functions documented? Experimentation has shown that there are significant differences between the SQL and the PL/SQL versions. But I have been unsuccessful trying to find documentation for the PL/SQL functions.
Some of the differences are discussed below - please let me know if I misunderstood something.
RETURNING clause differences
As documented, the RETURNING clause of JSON_[VALUE|QUERY] allows the specification of column length, as in VARCHAR2(80). I found that the PL/SQL versions allow RETURNING VARCHAR2, but they don't allow the specification of length. Is that correct? Is it documented?
PATH must be text literal - in SQL only?
This restriction seems to apply only to the SQL version, not to the PL/SQL version.
Here is a small test example:
create table j_tbl (json_doc clob check (json_doc is json));
insert into j_tbl (json_doc) values ('{"attrib":["green", "blue", "red"]}');
commit;
It seems I can't do this in SQL:
select json_value(json_doc, '$.attrib[' || '1' || ']') from j_tbl;
as it will error out with ORA-00907: missing right parenthesis. (Of course, I wouldn't do that, but I might want to have some expression, like a column name, instead of '1'; I chose the simplest reproducible example for illustration.)
If I try something clever like this:
with
prep (json_doc, pth) as (
select json_doc, '$.attrib[' || '1' || ']' from j_tbl
)
select json_value(json_doc, pth) from p;
then it will error out with ORA-40454: path expression not a literal - which pretty much explains the previous error too. (Oracle expected a closing parenthesis after the literal path - invalid as it is - '$.attrib[' )
PL/SQL doesn't seem to have the same restriction. I can do something like this without problem:
declare
json_str clob;
begin
select json_doc into json_str from j_tbl;
for i in 0 .. 2 loop
dbms_output.put_line( json_value(json_str, '$.attrib[' || to_char(i) || ']') );
end loop;
end;
/
green
blue
red
PL/SQL procedure successfully completed.
Right?
So, where is all of this documented? Are there other undocumented features, like the ASIS option? (Is that really an "undocumented" feature, which then shouldn't be used in serious production environments?) What other differences exist between SQL and PL/SQL JSON functions?