PL/SQL record types and the Node.js driver

By January 23, 2017 Uncategorized

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:

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

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 and array type that the procedure uses:

And here’s the JavaScript code that can call the procedure:

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 use know in this issue. Your feedback helps the driver team prioritize enhancements!

4 Comments

  • eed says:

    thank you. It is a great luck for me to find this blog.

  • Andrew says:

    Curious how you got this line of code to work..

    l_part_1_vals := :part_1_vals;
    l_part_2_vals := :part_2_vals;
    l_part_3_vals := :part_3_vals;

    The l_part variables are tables of varchar2 and number. In pl/sql you have to specify the index. So normally you’d have to do something like this.

    l_part_1_vals(1) := ‘Val1’;
    l_part_2_vals(1) := ‘Val1’;
    l_part_3_vals(1) := ‘Val1’;

    That’s the issue I’m seeing when trying to replicate this solution. Let me know if you have any suggestions.

    Thanks

Leave a Reply to eed Cancel Reply