How set Autocommit gobal in the instance before running a procedure in Oracledb using Node.js

Question

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

Answer 1

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.