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".
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:
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.
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.