oracle-databaseerror-handlingexceptionsqlcl

How to get Oracle exception in SQLcl script when using util.execute?


I try to write a batch file using Oracles SQLcl. In this file, i want to insert a new table row with util.execute. This just returns true / false, which is a boolean return of success/failure.
My question is, how i get the error message of the exception which is thrown, so that i can find out, what the problem is with my insert-statement.

What i do:
First of all, i connect to my database server and start my script:

me@pc:/myproject$ /sqlcl/bin/sql schemaname/pw@server.com:1521/sid


SQLcl: Release 17.3.0 Production [...]

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit [...]

SQL> 
SQL> @mybatchscript.js path/image.jpg

My mybatchscript.js looks like this:

script
var tabName = "MY_TABLE_NAME";
var HashMap = Java.type("java.util.HashMap");
var bindmap = new HashMap();
var filePath="&1";
print("\nreading file: "+ filePath);
var blob=conn.createBlob();
var bstream=blob.setBinaryStream(1);

java.nio.file.Files.copy(java.nio.file.FileSystems.getDefault().getPath(filePath),bstream);
bstream.flush();
bindmap.put("content",blob); // has content
bindmap.put("size",blob.length()); // is 341989
// the follow command fails
var doInsert = util.execute("insert into " 
  + tabName 
  + " (id, main_id, file_name, file_type,"
  + " file_size, file_content, table_name)"
  + " values("
  + " SEQ_MY_TABLE_NAME.nextval, 1,"
  + " 'testname', 'image/jpeg', :size, :content,"
  + " 'my_table_name')"
  ,bindmap);


sqlcl.setStmt(
    "show errors \n"
);
sqlcl.run();


if(!doInsert) {
  print("insert failed");
  print(doInsert);
  exit;
}
/

The console output is like:

reading file: path/image.jpg
insert failed
false

The script is working until the util.execute insert-statement. It returns false, so the insert-statement failed. But it doesn't tell me, why. I have no idea, how i get access to the error message or the exception which is thrown inside the util.execute?

I also tried to turn on SERVEROUTPUT or ERRORLOGGING, but it has the same output as above and the error log table is empty:

SQL> set errorlogging on
SQL> show errorlogging
errorlogging is ON TABLE SPERRORLOG
SQL> set serveroutput on
SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

My knowledge source were these slides where my script is also based on, i didn't find information about the error / exception handling for the util functions in general?


Solution

  • There's basically 2 ways.

    1- When using util.execute ( or any util.XYZ functions ) the last error message is retrieved with the following. I also just updated the scripting README with this : https://github.com/oracle/oracle-db-tools/blob/master/sqlcl/README.md

    var msg = util.getLastException()
    

    2- When using sqlcl.run()

    There's an example I wrote here: https://github.com/oracle/oracle-db-tools/blob/master/sqlcl/examples/audio.js

    The example is a tad silly in that it makes noises on success/failure but you'll see the code that gets the error. Check the ctx.getProperty("sqldev.last.err.message" That will get the last sqlerr message.

     if ( ctx.getProperty("sqldev.last.err.message") ) {          
        //  
        //  FAILED !
        //             
          play("chew_roar.wav");
        } else {        
        //  
        //  Success !!
        // 
          play("R2.wav");
        }