CODEWITHSUNDEEP
node.jsoracle-database
HelloWorld
HelloWorld

Reputation: 4349

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

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

Upvotes: 1

Views: 1798

Answers (1)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

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) {

Upvotes: 1

Related Questions