Using Extensible Types or Custom Types as Bind In variables in oracledb

Danilo Henrique Garcia Da Silva

Hi Everyone,

I am wondering whether or not the support for extensible types is in node-oracledb's road-map.

Currently oracledb supports tables of number and varchar types with the following implementation

nodeJs :

 ARR_NUMBER: { type: oracledb.NUMBER, dir: oracledb.BIND_IN, val: args.map(args=> args.number || null) }, ARR_NAME: { type: oracledb.STRING, dir: oracledb.BIND_IN, val: args.map(args=> args.name|| null) },

This works perfectly despite the fact that these bindings are string arrays, and the value is well obtained by any proc you may call....

if you try the following though:

 OPEN :cursor FOR   SELECT t.*   FROM   tb_ids t          INNER JOIN          TABLE( your_collection ) c          ON t.id = c.COLUMN_VALUE;

however this fails to work in collection joins leading you to "ora-22905-cannot-access-rows-from-a-non-nested-table-item",

due to the fact that you have to have the types, defined outside of a proc, as a custom type or extensible pre-built types.

Please note that oracle12g users do not face this issue even with types declared at pl/sql block scope.

These work if outside of a procedure/package, but inside it's scope they do not.

   TYPE test_collection_type is table of number;   test_collection_type SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST();

Can you tell me if there is any way to bind data as custom types? Documentation does not cover such case.

Warm Regards.

Christopher Jones-Oracle

Support for UDTs is being tracked in  https://github.com/oracle/node-oracledb/issues/147