How to handle a oracle.sql.ARRAY in node js?

Question

I am trying to call an Oracle stored procedure using the node oracle driver - https://github.com/joeferner/node-oracle

I am able to call the procedure using the following code, but I am having an issue with the second parameter (parameterArray). It requires an array of items to be passed to it, in java we use the oracle.sql.ARRAY but how would I handle this with node js? My current code is as follows...

var oracle = require('oracle');

var connectData = {
    hostname: "example_editted.com",
    port: 1521,
    database: "dev", // System ID (SID)
    user: "user",
    password: "password"
}

oracle.connect(connectData, function(err, connection) {

var starting_time = req.body.startDate + " 00:00:00"
var ending_time = req.body.endDate +" 00:00:00"
var parameterArray = {owner_id: req.body.accountId, time_min: null, time_max: null, duration_min: null, duration_max: null, date_format: "'MM/DD/YYYY HH24:MI:SS'", start_date: starting_time, end_date: ending_time} 
connection.execute("call reporting.execute_report(:1, :2, :3)", ["ProcedureName", parameterArray,new oracle.OutParam()], function(err, results) {

The current error I am getting is

Assertion failed: (handle->InternalFieldCount() > 0), function Unwrap, file /Users/johnson/.node-gyp/0.10.35/src/node_object_wrap.h, line 61.
Abort trap: 6

By what I see my guess (w/o any node.js experience) is that you are trying to pass string-indexed associative array, which the Oracle Call Interface does not allow. Wherever you google for "bind associative array indexed by varchar2", you'll only find mentions of failures to do so. Passing arrays/collections via OCI is allowed only for integer-indexed ones, afaik. — peter.hrasko.sk
– peter.hrasko.sk, Commented Jan 12, 2015 at 7:56

peter.hrasko.sk · Accepted Answer · 2015-01-12 13:30:11Z

Based on a pure guess that the problem may lie in the OCI's inability of binding string-indexed collections, your solution might be to decompose your JS object to a pair of regular arrays before calling the stored procedure and recomposing your associative collection in your PLSQL code before calling the stored procedure, i.e. ...

.
.
.    
//var parameterArray = {owner_id: req.body.accountId, time_min: null, time_max: null, duration_min: null, duration_max: null, date_format: "'MM/DD/YYYY HH24:MI:SS'", start_date: starting_time, end_date: ending_time} 
var parameterArrayIndices = ["owner_id", "time_min", "time_max", "duration_min", "duration_max", "date_format", "start_date", "end_date"];
var parameterArrayValues = [req.body.accountId, null, null, null, null, "'MM/DD/YYYY HH24:MI:SS'", starting_time, ending_time];

connection.execute("
    declare
        i_indices     dbms_sql.varchar2a;
        i_values      dbms_sql.varchar2a;
        l_params      <the_collection_type_of_the_procedure's_second_parameter>;
    begin
        i_indices := :1;
        i_values := :2;

        for i in nvl(i_indices.first,1)..nvl(i_indices.last,0) loop
            l_params(i_indices(i)) := i_values(i);
        end loop;

        reporting.execute_report(:3, l_params, :4);
    end;
", [parameterArrayIndices, parameterArrayValues, "ProcedureName", new oracle.OutParam()], function(err, results) {

Collectives™ on Stack Overflow

How to handle a oracle.sql.ARRAY in node js?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related