Skip to Main Content

Programming Languages & Frameworks

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Danilo Henrique Garcia Da SilvaNov 23 2017 — edited Dec 7 2017

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.

Comments

Post Details

Added on Nov 23 2017
1 comment
1,270 views