NJS-044: named JSON object is not expected in this context in MLE/JavaScript

This is a quick post demonstrating how to debugg the following error in Multilingual Engine (MLE) as shipping with Oracle Database 23ai on Linux/x86-64 and aarch64:

NJS-044: named JSON object is not expected in this context
    

Th error is caused in situations when you provide a JavaScript object as a positional bind to MLE’s JavaScript SQL Driver. The SQL Driver analyses the JavaScript object and expects to find one of the following attributes: dir, type, or val.

It’s very common to run into the problem trying to insert a JSON into a table.

Triggering the error

Let’s have a look at what might be the most common cause for the error: inserting a JSON into a table. The following table definition is sufficient to reproduce the NJS-044 error.

create table t (
        id number generated by default on null as identity,
        j json not null,
        constraint pk_t primary key (id)
    );
    

Next, a little bit of JavaScript code is needed. Imagine a procedure accepting JSON as input in order to store it in the previously created table.

create or replace procedure named_json_not_expected("data" json) as mle language javascript
    {{
        const result = session.execute(
            'insert into t (j) values (:data)',
            [data]
        );
    
        console.log(result.rowsAffected);
    }};
    /
    

The procedure compiles successfully, however there will be a runtime error. A unit test catches this before you deploy to production by the way – it is always a good idea to add unit tests to new feature. Note the use of the positional bind in session.execute(). Triggering the error is now as simple as:

SQL> begin
      2      named_json_not_expected(json('{"a": 1}'));
      3  end;
      4* /
    begin
    *
    ERROR at line 1:
    ORA-04161: Error: NJS-044: named JSON object is not expected in this context
    ORA-04171: at :=> (<inline-src-js>:3:20)
    ORA-06512: at "EMILY.NAMED_JSON_NOT_EXPECTED", line 1
    ORA-06512: at line 2
    

You get the same error in node-oracledb by the way; MLE and node-oracledb have been, and will remain, close friends.

Solution

Rather than passing JSON as a positional bind, provide it as a named bind, as in this example. Named binds are often preferable to positional binds because they are less prone to error out when you change the order of bind variables in the SQL statement:

create or replace procedure named_json_not_expected_named_bind("data" json) as mle language javascript
    {{
    
        const result = session.execute(
            'insert into t (j) values (:data)',
            {
                data: {
                    type: oracledb.DB_TYPE_JSON,
                    dir: oracledb.BIND_IN,
                    val: data
                }
            }
        );
    
        console.log(result.rowsAffected);
    }};
    /
    

If you absolutely want to keep the positional bind, you can do that, too:

create or replace procedure named_json_not_expected_positional_bind("data" json)
    as mle language javascript
    {{
    
        const result = session.execute(
            'insert into t (j) values (:data)',
            [
                {
                    val: data,
                    type: oracledb.DB_TYPE_JSON
                }
            ]
        );
    
        console.log(result.rowsAffected);
    }};
    /
    

With that slight change completed, runtime errors are averted:

SQL> begin
      2      named_json_not_expected_named_bind(json('{"a": 1}'));
      3  end;
      4* /
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from t;
    
       ID J          
    _____ __________ 
        1 {"a":1}
    

Summary

If your application encounters NJS-044: named JSON object is not expected in this context make sure to switch from positional to named binds or wrap your JSON into another object, containing the required val and type attributes.