Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Kat.

Asked: April 24, 2017 - 3:26 am UTC

Last updated: April 25, 2017 - 9:19 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

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.

and Connor said...

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.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.