oracle-databasesqlplus

Equivalence to SQLCODE & SQLERRM, but for OS Errors?


I'm trying to catch an OS Error message just like when we catch SQL Error Messages.

An example of this error could be SP2-0310: Unable to open file: "file name". enter image description here

I would normally just use variables SQLCODE & SQLERRM to do so, but is there a way to do it for OS?

I already tried just using SQLERRM, but it throws nothing of relevancy: enter image description here


Edit 2024 Aug 29th 09:19 utc-6

Just to highlight one thing: this SQL script is intended to run SQL queries using SQLcl & report back some runtime data (e.g. how much time it took). But in order to not spool the entire SQL extract in the screen, I need to use SET TERMOUT ON / OFF.

The expectation is to actually see if an SQL query failed because someone typed in the wrong directory + name. But the aim is not to exit the session, as I would need to login multiple times: I don't believe WHENEVER OSERROR will help, because it is either EXIT or CONTINUE (do nothing). For instance, with SQLCODE I can create a BEGIN - PUT_LINE - END with the error.

enter image description here


Solution

  • The SQL*Plus client will pre-process your script and split it into statements and will either:

    ORA- errors are generated by the database and can be handled in PL/SQL by using BEGIN ... EXCEPTION WHEN error_type THEN ... END;

    SP2- errors are generated by the SQL*Plus client before anything is sent to the database. In this case, you have asked the SQL*Plus client to open the file fs\fds.sql and the file does not exist on the client.

    If you want the SQL*Plus client to do something when an OS error occurs you can use the WHENEVER OSERROR command before you try to call the non-existent script and then the client will process the handler you define in that command.

    WHENEVER OSERROR EXIT
    @"&RFLD.&RFLE..&REXT."
    
    WHENEVER OSERROR CONTINUE
    @path\to\other.sql
    

    Note: this is a command for the SQL*Plus client application (and other client applications that support the same syntax) and is not a command that is processed on the database and is meaningless to client applications that do not support the SQL*Plus syntax.