Skip to Main Content
  • Questions

  • Run sql script through DBMS_SCHEDULER in 11g R2

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question.

Asked: April 18, 2016 - 12:45 pm UTC

Last updated: April 19, 2016 - 9:32 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

1) Is there a way to run a sql script through DBMS_SCHEDULER in 11g R2 without the usage of sh/bat files to call the script?
2) Is there a way to run dbms_scheduler executable sh/bat file that has relative path in the shell script?

Thanks.

and Chris said...

1. In 11g, no. In 12c yes! There is the new job type SQL_SCRIPT. This enables you to run sql files as SQL*Plus scripts with all the functionality that gives you:

https://oracle-base.com/articles/12c/scheduler-enhancements-12cr1#sql-script

2. If you want to use relative paths in shell scripts, at the start of it cd to the location you want everything to be relative to. e.g.:

cd /home/oracle

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 19, 2016 - 7:26 am UTC

Thanks a lot. I guess the only option is to move to the required directory and then use relative path as you mentioned.

Surprisingly the env variables like ORACLE_HOME, PATH etc are not set when run through scheduler.
I might need to set these variable again in the called shell script or run the sh file that sets it when users logs in through putty.


Connor McDonald
April 19, 2016 - 9:32 am UTC

The shell environment is kept *very* clean as a security measure. We dont want to pick up a "custom" version of (say) the 'rm' command, and be running it as 'oracle'.

Cheers,
Connor
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

DBMS_SCHEDULER

More on PL/SQL routine DBMS_SCHEDULER here