issue1069.js

'use strict';

process.env.ORA_SDTZ = 'UTC';

const oracledb = require('oracledb');
let config;
try {
  config = require('./dbconfig.js');
} catch (err) {
  if (err.code === 'MODULE_NOT_FOUND')
    config = { user: 'cj', password: 'cj', connectString: 'localhost/orclpdb1' };
  else
    throw err;
}

async function run() {
  let connection;

  try {
    connection = await oracledb.getConnection(config);

    let stmts = [

      `DROP TABLE mytesttab`,

      `CREATE TABLE mytesttab (code VARCHAR2(20), created TIMESTAMP WITH LOCAL TIME ZONE, bin BLOB, id NUMBER)`,

      `CREATE OR REPLACE PACKAGE test AS
         PROCEDURE createItem (p_code VARCHAR2, p_created TIMESTAMP WITH LOCAL TIME ZONE, p_bin BLOB, p_id NUMBER);
       END test;`,

      `CREATE OR REPLACE PACKAGE BODY test AS
         PROCEDURE createItem (p_code VARCHAR2, p_created TIMESTAMP WITH LOCAL TIME ZONE, p_bin BLOB, p_id NUMBER) IS
         BEGIN
           insert into mytesttab (code, created, bin, id) values (p_code, p_created, p_bin, p_id);
         END;
       END;`

    ];

    for (const s of stmts) {
      try {
        await connection.execute(s);
      } catch(e) {
        if (e.errorNum != 942)
          console.error(e);
      }
    }

    let result = await connection.execute(
      `begin test.createItem(
         P_CODE =>     :code,
         P_CREATED =>  :created,
         P_BIN =>      :bin,
         P_ID =>       :id
       );
      end;`,
      {
        code: { type: 2001, dir: 3002, val: 'code-0' },
        created: { type: 2014, dir: 3001, val: new Date() },
        bin: { type: 2006, dir: 3001, val: Buffer.from('abcdef') },
        id: { type: 2010, dir: 3003 }
      }
    );

    let sql = `SELECT * from mytesttab`;
    let binds = [];
    let options = { outFormat: oracledb.OBJECT, fetchInfo : {BIN: {type: oracledb.BUFFER}} };

    result = await connection.execute(sql, binds, options);
    console.log(result.rows);

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

run();