How to use Oracle SQL/JSON in PL/SQL cursor?

Question

I want to form a JSON from a cursor but when I use the below select query in the cursor, I'm getting "bad bind variable json_object", how to solve this problem? My database is Oracle 19C

select 
  JSON_OBJECT( 'Header':
    JSON_OBJECT(
        'SenderID': 'INS015',
        'PayerID': 'INS015'))
from dual;

Answer 1

The expr: expr syntax you are using was introduced in Oracle 19 so it should work, however, you can also use the original KEY expr VALUE expr syntax:

select JSON_OBJECT(
         KEY 'Header' VALUE JSON_OBJECT(
                              KEY 'SenderID' VALUE 'INS015',
                              KEY 'PayerID' VALUE 'INS015'
                            )
       )
from   dual;

As pointed out by @astentx, there can be issues with client drivers that try to automatically identify bind variables and are generating false positive matches with the new syntax.

The JSON Developer Guide states:

Note: Some client drivers might try to scan query text and identify bind variables before sending the query to the database. In some such cases a colon as name–value separator in json_object might be misinterpreted as introducing a bind variable. You can use keyword VALUE as the separator to avoid this problem ('Name' VALUE Diderot), or you can simply enclose the value part of the pair in parentheses: 'Name':(Diderot).

So you could try using additional parentheses:

SELECT JSON_OBJECT(
         'Header': (
                     JSON_OBJECT(
                       'SenderID': ('INS015'),
                       'PayerID': ('INS015')
                     )
                   )
       )
FROM   dual;

To prevent the client driver from generating false-positive matches for bind variables.

---

Separately, there appears to be a bug when the expr: expr syntax is used in PL/SQL as:

DECLARE
  v_cur SYS_REFCURSOR;
BEGIN
  OPEN v_cur FOR
    select JSON_OBJECT(
             'Header': JSON_OBJECT(
                         'SenderID': 'INS015',
                         'PayerID': 'INS015'
                       )
           ) AS json
    from   dual;
END;
/

In Oracle 21, gives the exception:

ORA-06550: line 7, column 36:
PL/SQL: ORA-00907: missing right parenthesis
ORA-06550: line 5, column 5:
PL/SQL: SQL Statement ignored

(Even though the same SQL statement works in the SQL scope.)

But, by switching to the original VALUE separator:

DECLARE
  v_cur SYS_REFCURSOR;
BEGIN
  OPEN v_cur FOR
    select JSON_OBJECT(
             KEY 'Header' VALUE JSON_OBJECT(
                                  KEY 'SenderID' VALUE 'INS015',
                                  KEY 'PayerID' VALUE 'INS015'
                                )
           ) AS json
    from   dual;
END;
/

Then the statement works.

fiddle