How to use node-oracle with an oracle array?

[willfults]

Hi, I am trying to call an Oracle stored procedure using the node oracle driver. 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

If anyone could provide an example of how to do this it would be appreciated. Thanks!

[SegFault0x0]

It looks like parameterArray is currently simply an object, and you need to wrap the data inside of brackets [ ] to make it an array.

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}];

[willfults]

Thanks, I added the brackets but am still getting the same error...

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

[SegFault0x0]

I see what you're trying to do now... The Readme states that this module is only equipped to pass in the OCCIINT, OCCISTRING, OCCIDOUBLE, OCCIFLOAT, and OCCINUMBER INOUT param types. I was able to get it to work by Stringifying the object, thereby passing it as a OCCISTRING param:

var parameterArray = JSON.stringify({owner_id: 42,
                                     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
                                    });

You could also probably add array capability by adding an option for setDataBufferArray (http://docs.oracle.com/cd/B10501_01/appdev.920/a96583/cci08r20.htm#1084798) to connection::SetValuesOnStatement in the C++ code.

[willfults]

Ah thanks. When you made the call did your code look something like this?

connection.execute("call reporting.execute_report(:1, :2, :3)", ["REPORT_TYPE", new oracle.OutParam(oracle.OCCISTRING, {in: parameterArray}),new oracle.OutParam(oracle.OCCICURSOR)], function(err, results) {

For some reason I get an error only saying "segmentation fault 11". Or if I try without specifying the out type I get this... connection.execute("call reporting.execute_report(:1, :2, :3)", ["TRAFFIC_ANALYSIS_CALL_SUMMARY", parameterArray,new oracle.OutParam(oracle.OCCICURSOR)], function(err, results) {

Error: CopyValuesToBaton: Unhandled value type: undefined at /Users/will/node_api/routes/calls.js:25:17

My guess is since I am passing the parameterArray as a string I would need to modify my stored procedure to accept a string right? I didn't do that yet, thus likely the reason for the error. If I understand your post right the problem may lie in the OCI's inability of binding string-indexed collections, so the workaround is to edit the stored procedure to accept a string. If I am wrong in any regard let me know, else thank you iGeechee for all your help!

[SegFault0x0]

Yeah, you'll have to refactor your stored procedure to tokenize the information out of the string, or you may be able to use JSON.parse (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON/parse). Glad I could help!