Prepared Statements for SELECT statements fail in second (and plus) execution "[Error: ORA-32109: invalid column or parameter position ]"

[felipeaf]

The problems happens for me with statements like "SELECT * FROM TABLE xxx WHERE :1, :2, :3". It also happens with the statement "SELECT SYSDATE FROM DUAL", so we can use it for test. The first time return the results ok, the second time return the error.

[Error: ORA-32109: invalid column or parameter position]

So we can't use prepared statements for selects =( A little code to reproduce it:

"use strict";
var oracle = require('oracle');

var SQL = "SELECT sysdate from dual"     //It happens with other SELECT stmts too
var args = [] //It happens with more args too.

var conn_json = {
    "hostname": "xxx",
    "port": "xxx",
    "database": "xxx",
    "user": "xxx",
    "password": "xxx"
}

var conn = oracle.connectSync(conn_json);
console.log(conn.isConnected());
var stmt = conn.prepare(SQL);
stmt.execute(args, function(err, results) {
     //FIRST TIME WORKS!
    console.log(err, results);

    stmt.execute(args, function(err, results) {console.log(err, results);}); // now it will print error!
});

Tested with instant client 12.1 in linux.

[SegFault0x0]

Creating a second statement worked for me:

var stmt = conn.prepare(SQL);
var stmt2 = conn.prepare(SQL);
stmt.execute(args, function(err, results) {
     //FIRST TIME WORKS!
    console.log(err, results);

    stmt2.execute(args, function(err, results) {console.log(err, results);}); // now it will work!
});

[felipeaf]

About the IGeechee's comments, create a new statement works, but statements are suposed to be reused.

[SegFault0x0]

I tried it again, the way you had it, and it only gives me that error when I leave args blank, args = []. If I put [1], [1,2], ['hello', 'goodbye'], or [JSON.Stringify({hello: 'goodbye'})] in args, it works. What type of arguments are you giving it?

[jainmnsh]

hello, I am getting error while executing the procedure when OutParam = new oracle.OutParam(oracle.OCCICURSOR) in Linux but it worked fine on Mac. I am thinking its some sort of configuration issue on linux but not sure, can you please help?

sql = 'CALL procName ( :1, :2, :3, :4, :5, :6)' sqlP = [value,value,value,value,value,[object OutParam]]

connection.execute(sql, sqlP, function (err, results

Error: terminate called after throwing an instance of 'oracle::occi::SQLException' what(): ORA-32129: cannot get information about this column