create or replace package my_package
as

type thing_t is record(
  part_1 varchar2(50),
  part_2 number,
  part_3 date
);

procedure my_proc(
  p_the_thing in thing_t
);

end;
/

create or replace package body my_package
as

procedure my_proc(
  p_the_thing in thing_t
)
is
begin
  null;
end;

end;
/

var oracledb = require('oracledb');
var config = require('./dbconfig');

oracledb.getConnection(config, function(err, connection) {
  var jsThing;
  var plsql;

  if (err) {throw err;}

  jsThing = {
    part1: 'Some kind of thing',
    part2: 100,
    part3: new Date()
  }; 

  plsql = 
   `declare

      l_plsql_thing my_package.thing_t;

    begin

      l_plsql_thing.part_1 := :part_1;
      l_plsql_thing.part_2 := :part_2;
      l_plsql_thing.part_3 := :part_3;

      my_package.my_proc(
        p_the_thing => l_plsql_thing
      );
      
    end;`;

  connection.execute(
    plsql, 
    {
      part_1: jsThing.part1,
      part_2: jsThing.part2,
      part_3: jsThing.part3
    }, 
    function(err, result) {
      if (err) {throw err;}

      console.log('The proc was called successfully!');

      connection.release(function(err) {
        if (err) {throw err;}

        console.log('Connection released.');
      })
  });
});

create or replace package my_package
as

type thing_t is record(
  part_1 varchar2(50),
  part_2 number,
  part_3 date
);

type thing_aat is table of thing_t
  index by pls_integer;

procedure my_proc(
  p_the_things in thing_aat
);

end;
/

create or replace package body my_package
as

procedure my_proc(
  p_the_things in thing_aat
)
is
begin
  null;
end;

end;
/

var oracledb = require('oracledb');
var config = require('./dbconfig');
var jsThings;

function getThings(count) {
  var things = [];

  for (idx = 0; idx < count; idx += 1) {
    things[idx] = {
      part1: 'Some kind of thing #' + idx,
      part2: idx,
      part3: new Date
    };
  }

  return things;
}

// Imagine this was some kind of remote/async call that fetched a collection
// of things.
jsThings = getThings(500);

oracledb.getConnection(config, function(err, connection) {
  var plsql;
  var idx;
  var part1Vals = [];
  var part2Vals = [];
  var part3Vals = [];

  if (err) {throw err;}
  
  // Before we send the "things" to Oracle, we need to decompose them into array
  // types that the driver currently supports: Number and String. Because date
  // isn't yet supported, I'll convert it to an ISO 8601 string here and back 
  // to a date in Oracle.
  for (idx = 0; idx < jsThings.length; idx += 1) {
    part1Vals.push(jsThings[idx].part1);
    part2Vals.push(jsThings[idx].part2);
    // Stringify converts to a string and parse removes the outer double quotes
    part3Vals.push(JSON.parse(JSON.stringify(jsThings[idx].part3)));
  }
  
  plsql = 
   `declare
 
      type varchar2_aat is table of varchar2(50)
        index by pls_integer;
      type number_aat is table of number
        index by pls_integer;
  
      l_part_1_vals  varchar2_aat;
      l_part_2_vals  number_aat;
      l_part_3_vals  varchar2_aat;
  
      l_plsql_thing  my_package.thing_t;
      l_plsql_things my_package.thing_aat;

    begin
  
      l_part_1_vals := :part_1_vals;
      l_part_2_vals := :part_2_vals;
      l_part_3_vals := :part_3_vals;
  
      -- Now that the decomposed array values have made it over to Oracle, we
      -- can put them back together as an array of record types.
      for idx in 1 .. l_part_1_vals.count
      loop
        l_plsql_thing.part_1 := l_part_1_vals(idx);
        l_plsql_thing.part_2 := l_part_2_vals(idx);
        
        -- Here, the date string is first converted to a timestamp with time zone 
        -- data type. That value is implicitly converted to a date. When this happens,
        -- the time zone is normalized to the session time zone.
        l_plsql_thing.part_3 := to_timestamp_tz(l_part_3_vals(idx),'yyyy-mm-dd"T"hh24:mi:ssxfftzr');

        l_plsql_things(idx) := l_plsql_thing;
      end loop;

      my_package.my_proc(
        p_the_things => l_plsql_things
      );
      
    end;`;
  
  connection.execute(
    plsql, 
    {
      part_1_vals: {
        type: oracledb.STRING,
        dir: oracledb.BIND_IN,
        val: part1Vals
      }, 
      part_2_vals: {
        type: oracledb.NUMBER,
        dir: oracledb.BIND_IN,
        val: part2Vals
      }, 
      part_3_vals: {
        type: oracledb.STRING,
        dir: oracledb.BIND_IN,
        val: part3Vals
      }
    }, 
    function(err, result) {
      if (err) {throw err;}

      console.log('The proc was called successfully!');

      connection.release(function(err) {
        if (err) {throw err;}

        console.log('Connection released.');
      });
  });
});