support default values and allow named params to not be specified?

[mxriverlynn]

I have a proc that uses a default value for a parameter, when the param is not specified. i would like to be able to execute the proc without providing a binding for that parameter, but right now I am getting an error.

Error: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'LIST_PT_TYPE_IP_OP'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

in this case, LIST_PT_TYPE_IP_OP is a proc to get a list of things, and it has a parameter declared as p_id in integer Default Null

I need to be able to call the LIST_PT_TYPE_IP_OP stored proc without providing a p_id in my parameter bindings.

is this supported, right now? If so, how do I make it work? If not, can support be added so I don't have to specify a p_id in my binding?

[dmcghan]

@derickbailey Could you please share the code that is not working for you?

[mxriverlynn]

i'm using the bind params, as specified in the docs for stored procedures https://github.com/oracle/node-oracledb/blob/master/doc/api.md#-101-plsql-stored-procedures

i have a few layers of indirection in my code, but the end result is basically the same as the example stored proc call from the docs. teh only major difference is i'm using a cursor to get the result set out of the proc.

adjusting the example code, though, i can show what I want:

CREATE OR REPLACE PROCEDURE myproc (
  id IN NUMBER default 1, 
  name OUT STRING
) AS
BEGIN
  SELECT last_name INTO name FROM employees WHERE employee_id = id;
END;

with this proc, i should be able to omit the id parameter when executing it, and it works fine when using SQLDeveloper or other tools to just run raw queries

but when i call this proc with the node-oracledb library, i get the error stated above, if i do not provide a bind param for id

var bindVars =   {
  // id:   159, <-- don't provide this one, because it has a default value
  name: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 40 },
};

connection.execute(
  "BEGIN myproc(:name); END;", 
  bindVars,
  function (err, result)
  {
    if (err) { console.error(err.message); return; }
    console.log(result.outBinds);
  });

since I have commented out the id parameter in the bindVars variable, the PLS-00306: wrong number or types of arguments error will be thrown

[dmcghan]

@derickbailey There are 2 ways to pass parameters in Oracle Database: Positional and Named notation

You're currently using positional notation, where the values of the actual parameters are associated with the formal parameters positionally (first actual goes with first formal, second actual with second formal, etc.).

With this notation, it's only possible to leverage default values if they are listed after all non-default values. In other words, if you changed your functions to this:

CREATE OR REPLACE PROCEDURE myproc (
  name OUT STRING,
  id IN NUMBER default 1
) AS
BEGIN
  SELECT last_name INTO name FROM employees WHERE employee_id = id;
END;

Then you could do this:

var bindVars =   {
  name: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 40 },
};

connection.execute(
  "BEGIN myproc(:name); END;", 
  bindVars,
  function (err, result)
  {
    if (err) { console.error(err.message); return; }
    console.log(result.outBinds);
  });

However, named notation is better because it's both more flexible AND serves to self document your code. If you leave the procedure as you currently have it defined, you can invoke it as follows:

var bindVars =   {
  name: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 40 },
};

connection.execute(
  "BEGIN myproc(name => :name); END;", 
  bindVars,
  function (err, result)
  {
    if (err) { console.error(err.message); return; }
    console.log(result.outBinds);
  });

Because id was not used, it's default value will be used. Also, now others can read the code a little better.

You can also mix positional notation with named notation, provided the positional portion is first.

I hope that helps!

[mxriverlynn]

@dmcghan i thought I was doing named params, already.

the only mention of named vs positional params i see is here https://github.com/oracle/node-oracledb/blob/master/doc/api.md#-122-out-and-in-out-bind-parameters

and according to that doc, i'm doing it correctly because my bindVars has specified the name: { ... } param syntax

am i supposed to be using BEGIN myproc(name => :name); END; syntax with the name => :name arrow, to get named parameters working? I don't see that anywhere in the documentation (and I'm not an oracle developer so I don't know these syntax options. i'm just a node dev, writing javascript, following the documentation)

[dmcghan]

I see why you're a little confused now - these are two separate things: binding values via the driver into an anonymous block of PL/SQL (which is what the doc you pointed to covers) and invoking a stored procedure. You can read about the latter here: http://docs.oracle.com/database/121/LNPLS/subprograms.htm#LNPLS00825

If I break up your PL/SQL block a little, the difference may become more obvious:

var bindVars =   {
  name: { dir: oracledb.BIND_OUT, type: oracledb.STRING, maxSize: 40 },
};

connection.execute(
  'DECLARE ' +
  '  l_name varchar2(50) := :name; ' +
  'BEGIN ' + 
  '  myproc(name => l_name); ' + 
  'END;', 
  bindVars,
  function (err, result)
  {
    if (err) { console.error(err.message); return; }
    console.log(result.outBinds);
  });

When the driver goes to execute this, the first thing that will happen is the code will be parsed. Next it uses either positional or named notation to "bind" in the values of your bind variables.

Then the code is executed and eventually the call to myproc is made. That's where the positional vs. named notation related to invoking stored procedures comes into play.

Generally speaking, I would recommend named notation for both! :)

[mxriverlynn]

@dmcghan thanks for that extra info!

I also talked to one of the oracle devs here at my client, and asked him about all this. he re-iterated what you just said.

i've adjusted the SQL string I'm using and it appears to be working!

[mxriverlynn]

oh, one last thing... i would suggest updating the docs to have a more explicit illustration of this. the docs should show the correct name => :name syntax in a section about named parameters. i realize this may be common knowledge for those that are used to Oracle... making it explicit will help, though

[cjbj]

@derickbailey I'll make a note about improving the doc and add it to the task list.