Skip to Main Content
  • Questions
  • Run sql script through DBMS_SCHEDULER in 11g R2


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?


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:

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


  (1 rating)

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


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'.


More to Explore


More on PL/SQL routine DBMS_SCHEDULER here