I was searching and i didn't seem to find an answer to this that would work
i need to run a procedure that is already stored after finishing a insert function
normally running with SQLDeveloper or Dbeaver you would just do
set autocommit on
execute procedure
set autocommit off
using node i can call the procedure with a CALL PROCEDURE()
but when i try to pass the sql statement set autocommit on
i get a ORA-00922 - missing or invalid option
error
i can't seem to find some way to do this, and i need the autocommit on otherwise the log file will crash the instance with that procedure
here are the examples of the functions i am using
oracledb.getConnection(oracleConn, function (err, conn) {
if (err) {
console.error(err.message);
} else {
setAutoCommit(conn, "on").then(function (result) {
if (result == "Success") {
callProcedure(conn).then(function (result) {
if (result == "Success") {
setAutoCommit(conn, "off").then(function (result) {
if (result === "Success") {
setTimeout(function () {
conn.close();
}, 60000);
}
});
}
});
}
});
}
});
function setAutoCommit(conn, status) {
return new Promise(function (fulfill, reject) {
var sql
if (status === "on") {
sql = 'set autocommit on';
} else if (status === "off") {
sql = "set autocommit off";
}
try {
conn.execute(sql, function (err, result) {
if (err) {
console.log(err, sql)
} else {
console.log(result);
return fulfill("Success");
}
})
} catch (e) {
console.error(e);
reject(e)
}
})
}
function callProcedure(conn) {
return new Promise(function (fulfill, reject) {
var sql = "call PROCEDURE()";
try {
conn.execute(sql, function (err, result) {
if (err) {
console.log(err, sql)
reject(err)
} else {
console.log(result);
return fulfill("Success");
}
})
} catch (e) {
console.log(e);
reject(e);
}
})
}
if anyone know how to do this, i couldnt find anything clear enough in the oracledb API documents
Pass autoCommit
as an option to execute()
(or executeMany()
). See this example.
You can also set the option globally, if that suits your business requirements: oracledb.autoCommit = true
.
Both will do what your SQL*Plus example does.
See the documentation:
https://oracle.github.io/node-oracledb/doc/api.html#propexecautocommit
and
https://oracle.github.io/node-oracledb/doc/api.html#propdbisautocommit
Finally, I'd recommend using the async/await style of programming, which is usable with Node.js 7.6 onwards.