The result of the following examples are identical in Oracle Database 23ai Free Version 23.5.0.24.07 and Oracle Database 23ai Enterprise Edition Version 23.6.0.24.10.
This works in SQL
select json [
select json {
'name' : t.name,
'value' : t.value
}
from (values
('a', 42),
('b', 43),
('c', 44)
) as t (name, value)
] as result;
RESULT
-------------------------------------------------------------------------
[{"name":"a","value":42},{"name":"b","value":43},{"name":"c","value":44}]
But in PL/SQL an error is produced. This is a bug IMO.
set serveroutput on size unlimited
declare
l_result json;
begin
select json [
select json {
'name' : t.name,
'value' : t.value
}
from (values
('a', 42),
('b', 43),
('c', 44)
) as t (name, value)
]
into l_result;
dbms_output.put_line(json_serialize(l_result));
end;
/
Error report -
ORA-01008: value for bind variable placeholder was not provided
01008. 00000 - "not all variables bound"
*Cause:
*Action:
It looks like the JSON_OBJECT function is not fully implemented in PL/SQL since the following variant works:
set serveroutput on size unlimited
declare
l_result json;
begin
select json [
select json {
'name' value t.name,
'value' value t.value
}
from (values
('a', 42),
('b', 43),
('c', 44)
) as t (name, value)
]
into l_result;
dbms_output.put_line(json_serialize(l_result));
end;
/
[{"name":"a","value":42},{"name":"b","value":43},{"name":"c","value":44}]
PL/SQL procedure successfully completed.
The relevant railroad diagram can be found here: https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/JSON_OBJECT.html#GUID-1EF347AE-7FDA-4B41-AFE0-DD5A49E8B370__GUID-E53C0458-5A56-4A52-9CB8-89DB5695AB20
