I am running into very strange behavior of scheduled code:
- my code runs as expected by manual call within the cmd (it echos result = 1)
- it still runs fine when I schedule it over the day
- it does not run as expected at the night (it echos result = 0)
I set a variable in sqlplus and pass it into the Batch script errorlevel. then I echo the error level. I can only imagine that I overwrite my errorlevel at some point but I do not see why this should behave differently over the day.
batch script
@setlocal enableextensions Enabledelayedexpansion
@echo off
set logfile=test_log.log
set dbuser=some_guy@some_db
echo 1_start test... >> !logfile!
sqlplus !dbuser! test.sql >> !logfile!
echo CMD Return Value is: !errorlevel! >> !logfile!
echo 2_start test... >> !logfile!
sqlplus !dbuser! test.sql
echo CMD Return Value is: !errorlevel! >> !logfile!
endlocal
SQL File
variable return_value number
set serveroutput on
SET LINESIZE 10000
declare
null;
begin
:return_value := 1;
dbms_output.enable;
dbms_lock.sleep(1);
dbms_output.put_line('SQL return_value: ' || :return_value );
exception
when others then
:return_value := 5;
end;
/
set serveroutput off
exit :return_value
task-scheduler
run test.cmd at 3 AM and 11 AM
As mentioned above. I simply wanna see "CMD Return Value is: 1" somewhere in the log file. this does work fine, but not at night schedule. I did test some settings in the scheduler (e.g. configure for win-server 2012 or win 2007) - it did not change the result: At day time my return value is 1; at night it is 0.
I am using Windows Server 2012 and Oracle 19
Thank you for your help!