Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

PL/SQL Record Types and the Node.js Driver

DZone's Guide to

PL/SQL Record Types and the Node.js Driver

Just because the current version of the Node.js driver (v1.12) doesn't mean you can't invoke stored procedures and functions that use record types. Here's a workaround!

· Database Zone
Free Resource

Find out how Database DevOps helps your team deliver value quicker while keeping your data safe and your organization compliant. Align DevOps for your applications with DevOps for your SQL Server databases to discover the advantages of true Database DevOps, brought to you in partnership with Redgate

The current version of the Node.js driver (v1.12) doesn't support binding record types directly. Does that mean you can't invoke stored procedures and functions that use record types? Of course not! For now, you just have to decompose the record types for binding and then recompose them inside your PL/SQL block. Let's have a look at an example...

Imagine we have the following PL/SQL package spec and body:

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

We could invoke the stored procedure with the following JavaScript code:

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

That's not so bad, right? But what about an array of record types? You can do that too with a little extra code...

Here's an updated package that adds an array type that the procedure uses:

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 the procedure:

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

Okay, that was a bit trickier! The ability to bind record types and arrays of record types directly would be a very welcome addition to the driver. If you agree, feel free to let us know in this issue. Your feedback helps the driver team prioritize enhancements!

Align DevOps for your applications with DevOps for your SQL Server databases to increase speed of delivery and keep data safe. Discover true Database DevOps, brought to you in partnership with Redgate

Topics:
database ,pl/sql ,tutorial ,node.js ,drivers ,stored procedures

Published at DZone with permission of Dan McGhan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}