oj

oracle-json

nodejs module to call oracle stored procedures using json input and output params

Showing:

Popularity

Downloads/wk

67

GitHub Stars

7

Maintenance

Last Commit

3mos ago

Contributors

5

Package

Dependencies

2

Size (min+gzip)

15.2KB

License

Type Definitions

Tree-Shakeable

No?

Categories

Readme

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

VersionDateDescription
v5.0.02021-06-21Upgrade dependency to oracledb v5. Updated node compatibility to match
v4.0.12020-01-27Remove password from console logging of settings.
v4.0.02019-08-12Upgrade to oracledb v4
v3.1.02019-07-24Upgrade module dependencies
v3.0.02018-02-19Upgrade to version 3.0.x of oracledb and debug modules.
v2.1.62018-02-19Use console.log when exiting process to ensure log messages are written.
v2.1.52018-01-29Fix invalid reference in debug mesage.
v2.1.42017-05-29Fixed retry of Pool Termination where hundreds or thousands of failed requests could be made - there was not waiting before retry.
v2.1.32017-03-06Added support to restart the connection pool on certain connection errors
v2.1.22017-01-24Fixed errors being raised because error handlign code was not returning from function
v2.1.12016-10-19Improved Error reporting
v2.1.02016-10-04Added handling of CURSORS and ResultSets from queries
v2.0.22016-08-04support the autoCommit property
v2.0.12016-03-01uses callback to handle errors
v2.0.02016-03-01Switched from oracle to oracledb module
v1.0.42016-03-01Publish BLOB handling missing from 1.0.3
v1.0.32015-08-14BLOB output type handled and oracle module updated to version 0.3.8
v1.0.22015-07-21Split input parameter
v1.0.12014-09-22Test executions
v1.0.02014-09-15Connection pooling
v0.8.52014-09-10Check for keep open
v0.8.42014-09-10Close when test fails
v0.8.32014-09-10Reconnect when test fails
v0.8.22014-09-09Test a connection with a simple query
v0.8.12014-09-08Close connection
v0.8.02014-09-08Error status
v0.7.102014-09-08Monitor reconnect times
v0.7.92014-09-07Monitor total procedure executions
v0.7.82014-09-01Connection maxRetries
v0.7.72014-09-01Debug logging
v0.7.62014-08-24Raise an error when not responding directly. Otherwise send json error
v0.7.42014-08-22Close on error status
v0.7.32014-08-21Check database status returned
v0.7.22014-08-21Parse JSON result
v0.6.52014-07-03Use any object in the express request as input to the procedure call
v0.5.52014-07-03Reverted to oracle0.3.4
v0.5.12014-07-02Check TNS settings not used
v0.5.02014-07-02Use oracle.connectSync
v0.4.22014-06-18Option to use req.params as input
v0.4.12014-05-16Configure whether to respond to the client
v0.4.02014-05-13Keep the connection open if configured
v0.2.12013-12-18Make one attempt to reconnect to Oracle per request
v0.1.12013-12-05Initial Version

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.

Rate & Review

Great Documentation0
Easy to Use0
Performant0
Highly Customizable0
Bleeding Edge0
Responsive Maintainers0
Poor Documentation0
Hard to Use0
Slow0
Buggy0
Abandoned0
Unwelcoming Community0
100