Node.Js Oracledb optional Array as IN bind parameter to Oracle Procedure

Question

I am trying to pass array in procedure as mentioned in following link but that array should be optional. I am doing bulk insert using forall in the procedure. but some time list can be empty as well and still other stuff in the procedure should be executed succesfully.

https://github.com/oracle/node-oracledb/blob/master/doc/api.md#plsqlindexbybinds

connection.execute(
  "BEGIN mypkg.myinproc(:id, :vals); END;",
  [
    1234,
    { type: oracledb.NUMBER,
       dir: oracledb.BIND_IN,
       val: [1, 2, 23, 4, 10]
    }
  ],

  function (err) { . . . });

works find but if i don't want to pass vals i am getting errors

i tried

val:[]
val: null
val: undefined

and not having val at all

i am getting following error messages

when trying to pass null or undefined or do not pass:

PLS-00306: wrong number or types of arguments in call to 'INSERT_PROCEDURE'

When trying to pass empty array

NJS-039: empty array is not allowed for IN bind

I did a work around by passing array with null

 val:[null]

and in procedure removing nulls from the list before executing FORALL.

this works for temporary but i would really like to solve this problem in proper way.

Christopher Jones · Accepted Answer · 2016-11-19 01:07:28Z

1

I'm going to treat this as a bug (or design flaw) and get someone to look at it. Both PHP OCI8 2.0 and Node node-oracledb 1.11 disallow binding empty arrays, but Python cx_Oracle doesn't.

answered Nov 19, 2016 at 1:07

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Node.Js Oracledb optional Array as IN bind parameter to Oracle Procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related