How to bind a TYPE as BIND_IN from node.js to Oracle via oracleDB?

[MPBatista]

We have a stored procedure that we need to pass a oracle TYPE:

create or replace PACKAGE
  PKG_TEST
IS
  TYPE t_rec_type IS RECORD (
              trx_header_id                   NUMBER,
              trx_number                      VARCHAR2(20)    DEFAULT NULL,
              trx_date                        DATE            DEFAULT NULL
           );
  TYPE t_type    IS TABLE OF t_rec_type;
  PROCEDURE testproc2(  p_rec_type            IN          t_rec_type);
END;

And the code node.js:

  var bindvars = {};
  
  bindvars.p_trx_header_tbl = [{ 
		  		'trx_header_id':41, 
				'trx_number': 'GA10110720160120T1', 
				'trx_date': '2016-12-20 00:00:00.000'
  }];
  
  connection.execute( 
	"DECLARE \
		 BEGIN \
		   PKG_TEST.testproc2( \
		     :p_rec_type); \
    	 END;", 
    	bindvars,
    function (err, result) {
      if (err) { 
    	  console.error( "Error: " + err.message); return;
      }
      console.log(result.outBinds);
    });

But we have the following message:

Error: ORA-01036: illegal variable name/number

[dmcghan]

@MateusPereiraBatista Currently, you have to decompose the values into separate, scalar datatypes. Then you can recompose them into a record type in the PL/SQL block. I'll put together an example for you...

[dmcghan]

@MateusPereiraBatista Okay, let's say we have the following package:

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;
/

I can invoke the procedure with the following:

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_the_thing my_package.thing_t;

    begin

      l_the_thing.part_1 := :part_1;
      l_the_thing.part_2 := :part_2;
      l_the_thing.part_3 := :part_3;

      my_package.my_proc(
        p_the_thing => l_the_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. Done.');
      })
  });
});

I hope that helps!

[cjbj]

Thanks @dmcghan

I'll close this issue as a dup of #147, which is being used to track it.

[MPBatista]

Hi Dan, thanks!

I think my sample is very simple, in fact I need to pass three as TYPE parameters, 2 of them are a list of TYPE objects , does is it possible something like that with oracleDB?

[dmcghan]

@MateusPereiraBatista It requires a little more code, especially since arrays of dates are not yet supported, but it's possible.

Here's a package with an associative array type and a proc that uses it:

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;
/

And here's the JavaScript code that can call it:

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.');
      });
  });
});

Is that sufficient? :)