[SOLVED] How do I pass a return code from an Oracle script back to the WINDOWS Batch script which called it?

How do I pass a return code from an Oracle script back to the WINDOWS Batch script which called it?

I'm running Windows 10-64 bit and Oracle 18.4 I have a Windows batch file (test1.bat) which calls a windows SQL script file.

SQLPLUS -s (username)/(password) as sysdba @test1.sql
Echo %errorlevel%

I would then like to do some error checking on the %errorlevel% variable when I'm returned to Test1.bat from SQLPLUS. I'll create different processes depending on whether the return code is ORA-01940 (Cannot drop a user that is currently connected) or ORA-01918 (user 'MYUSER' does not exist), etc.

When I run test1.bat, I can't reference the return code from Oracle SQLPLUS, even when the user was previously dropped. The batch script generates

>Echo 0
0

How can I pass the error code from Oracle SQLPLUS back to the batch script which called it? Here's the test1.sql

PROMPT Begin Dropping User Schema MyUser
Alter Session Set CONTAINER=(MyContainer);
DROP user MYUSER cascade;
Whenever sqlerror exit sql.sqlcode;
EXIT;

Solution

You need to declare the error condition handling before the error occurs:

Whenever sqlerror exit sql.sqlcode; 
DROP user MYUSER cascade;
EXIT 0;

You can change the handling at different point in the script. For instance you could do do a protective drop before create, by ignoring an error on the drop, but still stopping if the next step fails:

whenever sqlerror continue
drop ...
whenever sqlerror exit failure
create...
alter...
etc

Incidentally, in Unix-land this approach is limited because most (all?) shells have limited return code, and higher numbers wrap around; so ORA-0918 would be reported as 126, making it impossible to interpret. More importantly some values will wrap to zero, making it look like no error occurred. You don't have that issue with %errorlevel% fortunately.