NJS-072 at PL/SQL Boolean

[arcezd]

I am having troubles handling PL/SQL Boolean in DBObjects.

1. With the async/await programming style, make sure you are using 'await' in the right places.
   Y

2. Is it an error or a hang or a crash?
   Error.

3. What error(s) you are seeing?

Error: NJS-072: cannot convert from attribute "DATATYPEBOOLEAN" of type "B2000.RESULTRECORD" to JavaScript value
    at DbObject._toPojo (/Users/diego.arce/git/test/node-oracledb/lib/dbObject.js:52:23)
    at DbObject.toJSON (/Users/diego.arce/git/test/node-oracledb/lib/dbObject.js:88:17)
    at JSON.stringify (<anonymous>)
    at run (/Users/diego.arce/git/test/test.js:50:22)

4. Include a runnable Node.js script that shows the problem.
   Include all SQL needed to create the database schema. Use Markdown syntax, see https://help.github.com/github/writing-on-github/basic-writing-and-formatting-syntax

PL/SQL

    CREATE OR REPLACE PACKAGE NODE_ORACLEDB_ISSUE IS
       TYPE resultRecord IS RECORD(
          dataTypeBoolean BOOLEAN);
       PROCEDURE ReturnRecordWithBOOLEAN
       (
          data              OUT resultRecord,
          pErrorCode        OUT VARCHAR2,
          pUserMessage      OUT VARCHAR2,
          pTechnicalMessage OUT VARCHAR2
       );
    END NODE_ORACLEDB_ISSUE;
    CREATE OR REPLACE PACKAGE BODY NODE_ORACLEDB_ISSUE IS
       PROCEDURE ReturnRecordWithBOOLEAN
       (
          data              OUT resultRecord,
          pErrorCode        OUT VARCHAR2,
          pUserMessage      OUT VARCHAR2,
          pTechnicalMessage OUT VARCHAR2
       ) IS
       BEGIN
          data.dataTypeBoolean := TRUE;
          pErrorCode           := '000000';
       END;
    END NODE_ORACLEDB_ISSUE;

test.js

const oracledb = require('oracledb');

const mypw = "password" // set mypw to the hr schema password

async function run() {

  let connection;

  try {
    connection = await oracledb.getConnection({
      user: "user",
      password: mypw,
      connectString: "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = ip) (PORT = 1522))(CONNECT_DATA = (SERVER = DEDICATED)  (SERVICE_NAME = DB )))"
    });

    const sql = "BEGIN NODE_ORACLEDB_ISSUE.RETURNRECORDWITHBOOLEAN(:DATA,:PERRORCODE,:PUSERMESSAGE,:PTECHNICALMESSAGE); END;"

    const dataDbObject = await connection.getDbObjectClass("B2000.NODE_ORACLEDB_ISSUE.RESULTRECORD")

    const binds = {
      DATA: {
        dir: oracledb.BIND_OUT,
        type: dataDbObject
      },
      PERRORCODE: {
        dir: oracledb.BIND_OUT,
        type: oracledb.STRING
      },
      PUSERMESSAGE: {
        dir: oracledb.BIND_OUT,
        type: oracledb.STRING
      },
      PTECHNICALMESSAGE: {
        dir: oracledb.BIND_OUT,
        type: oracledb.STRING
      }
    }

    const options = {
      outFormat: oracledb.OUT_FORMAT_OBJECT,
      resultSet: true
    };

    const result = await connection.execute(
      sql,
      binds,
      options
    );

    console.log(JSON.stringify(result));

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }
}

run();

5. Run node and show the output of:

process.platform -> darwin
process.platform -> v10.16.2
process.platform -> x64
require('oracledb').versionString -> 4.1.0
require('oracledb').oracleClientVersionString -> 19.3.0.0.0

6. What is your Oracle Database version?

[arcezd]

I already have a workaround for this: #1190 .

I am also having troubles handling PL/SQL Boolean as IN or OUT parameter of store procedures. I am working in the solution.
I want to know if i need to fill one issue for every scenario? Or just one like "Problem handling PL/SQL boolean in store procedures"?

[cjbj]

One issue is fine. I appreciate you looking at this area.

Before you submit any PRs you must have an OCA accepted, otherwise it can cause problems.

[sla100]

Maybe the conversion through the package DIUTIL will be satisfactory? Something like:

declare 
    v_bool boolean := sys.diutil.int_to_bool(:bind_input_integer);
begin
    :bind_output_integer := sys.diutil.bool_to_int( v_bool );
end;
/

[cjbj]

@arcezd's PR has landed in node-oracledb 4.2 ! Thank you.

See https://blogs.oracle.com/opal/node-oracledb-42-with-improved-data-type-binding and https://oracle.github.io/node-oracledb/doc/api.html#executebindparamtype