node.js database connectivity to oracle by noradle
npm install noradle-ndbcUse NDBC API to access ORACLE
-----------------------------
through PL/SQL servlet can print arbitrary content to feed NDBC call,
the most NDBC use is for fetch SQL
result sets,
so the example is for that.
javascript
var DBDriver = require('noradle-nodejs-client')
, NDBC = require('noradle-ndbc')
, dbPool = DBDriver.connect([9009], {cid : 'test', passwd : 'test'})
, dbc = new NDBC.DBCall(dbPool, {__parse : true, 'x$dbu': 'demo'})
;
dbc.call('db_src_b.example', {limit : 3}, function(status, headers, page){
if(status!==200) {
...
return;
}
console.log(status);
console.log(headers);
console.log(page);
});
`
Note:
* NDBC instance have dbPool as oracle access path supporting object as (dbPool, {...
* NDBC have default parameter in second parameter as {__parse : true, 'x$dbu': 'demo'}
* ndbc.call(stored_procedure_name, parameter, cb) will combine default parameter to make a PL/SQL servlet request
* callback(status, headers, page) is just like http response
* headers is name-value(s) pairs
* page is Buffer, String or Object type as headers['content-type'] indicate
* __parse : true will let noradle automatically convert certain response type to parsed javascript objectsfor how raw SQL result is parsed, see [noradle-resultsets][]
[noradle-resultsets]: https://github.com/noradle/noradle-resultsets
$3
`plsql
create or replace package body db_src_b is procedure example is
cur sys_refcursor;
v1 varchar2(50) := 'psp.web';
v2 number := 123456;
v3 date := date '1976-10-26';
begin
h.content_type('text/resultsets');
open cur for
select a.object_name, a.subobject_name, a.object_type, a.created
from user_objects a
where rownum <= r.getn('limit', 8);
rs.print('test', cur);
open cur for
select v1 as name, v2 as val, v3 as ctime from dual;
rs.print('namevals', cur);
end;
end db_src_b;
`
Note:
* r.getn('limit', 8) will get number type parameter limit, as js code, it's 3, and default to 8 if no this input
* h.content_type('text/resultsets'); specify the response content-type is result sets
that can be converted to javascript object or JSON text
* rs.print(name, sys_refcursor) will print SQL result and its meta data to condensed table format
* v1,v2,v3 is varchar2/number/date types, all scalar data can be printed out with from dual SQL$3
`
[objects]
OBJECT_NAME:1,SUBOBJECT_NAME:1,OBJECT_TYPE:1,CREATED:12
TOOL,,TYPE,2015-04-20 16:38:39
TOOL2,,TYPE,2015-04-20 16:38:39
TERM_T,,TABLE,2015-04-20 16:38:45
[namevals]
NAME:1,VAL:2,CTIME:12,P1:1,P2:1,PNULL:1
psp.web,123456,1976-10-26 00:00:00,value1,value2,
`
Note:
* SQL resultsets is printed section by section
* one resultset have one meta line and zero, one or more data lines each line for one record
* columns/lines are separate not only with comma and linefeed, but with a hidden ACSII char together with separator
so column content can safely have comma and linefeed.$3
`
200
{ Date: 'Fri, 24 Jul 2015 00:55:46 GMT',
'Content-Encoding': '?',
'Content-Length': '649',
'Content-Type': 'text/resultsets; charset=UTF-8',
'x-pw-timespan': '40 / 40 ms' }
{ objects:
{ name: 'objects',
attrs:
[ { name: 'object_name', dataType: 1 },
{ name: 'subobject_name', dataType: 1 },
{ name: 'object_type', dataType: 1 },
{ name: 'created', dataType: 12 } ],
rows:
[ { object_name: 'TOOL',
subobject_name: '',
object_type: 'TYPE',
created: '2015-04-20 16:38:39' },
{ object_name: 'TOOL2',
subobject_name: '',
object_type: 'TYPE',
created: '2015-04-20 16:38:39' },
{ object_name: 'TERM_T',
subobject_name: '',
object_type: 'TABLE',
created: '2015-04-20 16:38:45' } ] },
namevals:
{ name: 'namevals',
attrs:
[ { name: 'name', dataType: 1 },
{ name: 'val', dataType: 2 },
{ name: 'ctime', dataType: 12 },
{ name: 'p1', dataType: 1 },
{ name: 'p2', dataType: 1 },
{ name: 'pnull', dataType: 1 } ],
rows:
[ { name: 'psp.web',
val: 123456,
ctime: '1976-10-26 00:00:00',
p1: 'value1',
p2: 'value2',
pnull: '' } ] } }
``