Stored proc with In-Out "id" parameter bound to "undefined" randomly fails

[mxriverlynn]

I have an issue where the oracle library randomly fails when using an stored proc with an "id" parameter set to undefined.

The stored procedure itself will check for null in the id and do an insert into a table, or an update based on the id, if one is supplied.

The id parameter is an in/out param.

Randomly, the call to this stored procedure will fail with this error:

Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1

    at Error (native)

The SQL package with sproc and table here, will reproduce the problem: https://gist.github.com/derickbailey/63f5ee10b75d783215c265fb641f2e48

The execution of this code will succeed sometimes and fail other times.

For example, in my test of the package at that gist, it ran fine 3 times and then failed the 4th time.

~/dev/test/in_out_burger/ 
$ node index.js 
create pool
get connection
execute sproc
close connection

~/dev/test/in_out_burger/ 
$ node index.js 
create pool
get connection
execute sproc
close connection

~/dev/test/in_out_burger/ 
$ node index.js 
create pool
get connection
execute sproc
close connection

~/dev/test/in_out_burger/ 
$ node index.js 
create pool
get connection
execute sproc
/Users/derickbailey/dev/test/in_out_burger/index.js:29
      if (err) { throw err; }
                 ^

Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1

    at Error (native)

I have not been able to consistently reproduce the error on command. It happens randomly, as far as I can tell. Sometimes it will fail twice in a row, other times it will fail once and then work 3 or 4 times.

Any help on resolving this is greatly appreciated.

[mxriverlynn]

p.s. if I change the data type of p_id and the id column to varchar2 everything works fine. it only fails randomly when the data type is integer

[mxriverlynn]

if i pass in an actual integer value for the p_id param, everything works fine. it's only when the p_id param is set to undefined or null that it randomly fails

[cjbj]

Yay! A reproducible testcase. Will need to debug.

(I wonder if this is related to #322?)

[mxriverlynn]

FWIW, as a workaround we changed our logic to check if the id param is either null or 0, to do an insert. i'm passing 0 instead of undefined, now, and it's working.

this is an acceptable workaround for us while the real issue is uncovered

[cjbj]

@derickbailey sorry the fix didn't make 1.10.

[cjbj]

@derickbailey This is fixed in 1.10.1. Thanks for reporting it.

[mxriverlynn]

great, thanks @cjbj! i'll get my app upgraded and test the fix as soon as i can (probably next week). if you don't hear back, assume it worked and i forgot to reply to say it was good to go :)