oracle-json

nodejs module to call oracle stored procedures using json input and output params. Automatically sends HTTP 503 response for stored procedure errors or when the database connection is lost. Exits when the database connection is lost ( assumes it will be restarted automatically by upstart or some such process) Requires the oracledb driver module.

Install

  npm install oracle-json

Options:

• database - connection parameters for the ''oracle'' node module.
• databasePool - database pool settings which can be:
  • poolTimeout - time (s) that unused connections will be closed. Default 30.
  • queueTimeout - time (ms) to wait for a connection before returning an Error. Default 120000.
  • poolMin - The smallest number of connections the pool will shrink to - Default 0.
  • poolMax - The greatest number of connections the pool will grow to - Default 1.

The following options are used on the call to execute a procedure; query

• procedure the name of the stored procedure to execute. If not specified a `query``must be specified instead
• query the query to execute. The response will be executed as a resultset and returned in res.locals.data. You can reference the 'inputs'or requestvalues by name e.gselect * from table where id = :idwhereinputs` is {'id': 12}
• noRespond do not automatically respond to the client but save results of procedure as res.locals.data
• request use the object named from express as the input to the procedure e.g. request:"query" will use req.query
• inputs specifiy the input parameters explicitly in this object
• debugMaskList array of object properties that should be masked in debug data e.g.    debugMaskList:["card.cardNumber","card.expDate"]
• outputType
  • BLOB: response will by read without any modification.
  • CURSOR: output will be processed as 1 or more (see ) CURSORS and converted to Arrays of JSON (using field names are object property names)
  • CLOB (default): CLOB will be converted to JSON. Output will be set to res.locals.data
• outputCount The number of output Cursors to expect. Default 1.
• numRows number of rows to prefetch when processing queries/cursors.
• databaseConnectionErrors an array of Oracle connection errors codes that will cause the current connection pool to be shutdown, a new one started and the requested query/proc to be reprocessed. e.g     databaseConnectionErrors:["ORA-04068","ORA-06508","ORA-04061","ORA-03135"].

Examples:

The examples below assume the express module is also being used.

Initialization

var oj=require('oracle-json')(options);
app.use(oj.connect());

Called a Stored Procedure which returns JSON

var priceCall={procedure:"pkg_test.getPrice",request:"body",output:true} ;
app.get('/price',oj.execute(priceCall));

Guarantee your connection is good

conntest() runs the simplest query, select 1 from dual and reconnects on error. The overhead for this test seems about 2ms.

var priceCall={procedure:"pkg_test.getPrice",request:"body",output:true} ;
app.get('/price',oj.conntest(),oj.execute(priceCall));
//execsafe includes conntest and execute
app.get('/pricesafe',oj.execsafe(priceCall));

Release History

License

(The MIT License)

Copyright (c) 2016 PC

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the 'Software'), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED 'AS IS', WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.