How to get compilation errors while executing stored procedure in node-oracledb 5.1

Question

I am trying to execute a raw stored procedure, it shows some compilation error in SQL Developer. but it is not returning in the call back function from execute() method.

ex.runQuery  = async (data)=>{
  //{ "STATUS": "SUCCESS", data: { result }, object_key_seq: data[i].object_key_seq, type: data[i].type, i: i }
  //{ "STATUS": "ERROR", "ERROR": utils.parseError(err, query), object_key_seq: data[i].object_key_seq, type: data[i].type, i: i }
  let connection = null;
  let resutl = null;

  try{
    connection = await oraconnect.getPoolConnection()
    if(data.object_type == 'SEQUENCE' || data.object_type == 'TABLE' || data.object_type == 'VIEW' || data.object_type == 'INDEX'){
      data.script = data.script.slice(0, -1);
    }
    if(data.script.slice(-1) == '/'){
      data.script = data.script.slice(0, -1);
    }
    resutl = await oraconnect.query(connection, data.script, [], 100);
  }catch(err){
    console.log(err);
    return { "STATUS": "ERROR", "ERROR": {errorMessage:err.message}, object_key_seq: data.object_key_seq, type: data.type, object_name :data.object_name}

  }finally{
    if (connection) {

      try {
        await   oraconnect.doRelease(connection);
        //await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }

  return { "STATUS": "SUCCESS", data: { resutl }, object_key_seq: data.object_key_seq, type: data.type, object_name: data.object_name}
}

Above is the one I used. I am expecting an error in the first catch block like shown in the below image

Answer 1

You question doesn't show the statement text being executed in node-oracledb, but the SQL Dev screenshot seems to show that you are creating or compiling a stored procedure.

When creating PL/SQL procedures and functions in node-oracledb, the current version 5.1 doesn't support 'success with info' errors that PL/SQL compilation errors return. This is noted in enhancement request https://github.com/oracle/node-oracledb/issues/823.

You can manually check for issues by querying user_errors like:

    await connection.execute(
      `create or replace procedure badproc() as
       begin
           INVALID
       end;`);
    const r = await connection.execute(
      `select line, position, text
       from user_errors
       where name = 'BADPROC' and type = 'PROCEDURE'
       order by name, type, line, position`,
      [], { outFormat: oracledb.OUT_FORMAT_OBJECT }
    );
    if (r.rows.length) {
      console.error(r.rows[0].TEXT);
      console.error('at line ', r.rows[0].LINE, 'position', r.rows[0].POSITION);
    }

Output is like:

PLS-00103: Encountered the symbol ")" when expecting one of the following:

   <an identifier> <a double-quoted delimited-identifier>
   current delete exists prior

at line  1 position 19

Trying to call such an invalid procedure gives the expected error:

    const r2 = await connection.execute(`begin badproc(1); end;`);
    console.log(r2);

gives:

[Error: ORA-06550: line 1, column 7:
PLS-00905: object CJ.BADPROC is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored] {
  errorNum: 6550,
  offset: 6
}