Hi askTome team.
I have two database one name is prod and another is test.
Their version are 12.2.0.1
I create a scheduler job on db:prod like this
begin
dbms_scheduler.create_job(
job_name=>'J_TEST_02',
job_type=>'SQL_SCRIPT',
job_action=><v_script>,
credential_name=>'C_OS_ORA12C2_01',
auto_drop=>false,
enabled=>false
);
end;
v_scripts is my procedure.
I test this job used a invalid job_action which like this
"select 1+2 from dual;".
begin
dbms_scheduler.run_job(
job_name=>'J_TEST_02',
use_current_session=>false
);
end;
BUT when I check the user_scheduler_job_run_details, I found the record about run this job was SUCCEEDED.
AND the col OUTPUT is
SQL*Plus: Release 12.2.0.1.0 Production on Mon Apr 24 11:10:34 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> SP2-0640: Not connected
SQL>
WHY scheduler job not raise a error and How to raise a error when use scheduler job which job_type was "SQL_SCRIPT" or "BACKUP_SCRIPT".
Thank you very much.
The SQL_SCRIPT job type is in effect the *content* you would supply to a SQLPlus session that was started as:
sqlplus /nolog
So 'v_script' would need something like:
connect scott/tiger
spool output.lst
select 1+2 from dual;
spool off
In your case, you never connected but we *did* indeed manage to run sqlplus and we didn't get any database errors.
Obviously a 'connect' statement in a script is generally not a good thing for security reasons, so the scheduler support credentials as well, ie, you can create a credential, and then submit a SQL_SCRIPT job passing the prebuilt credential.