Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: February 19, 2016 - 4:47 pm UTC

Last updated: February 22, 2016 - 12:29 am UTC

Version: 11.1.0.7.23

Viewed 10K+ times! This question is

You Asked

I am thinking of changing the current way scripting works in our database, rather than executing database scripts from shell scripts, I would execute in reverse. My thinking is it would be easier to manage scripts that call database procedures using dbms_scheduler. I can also manage all database scripts from one place and easily enable or disable them. Also future DBAs would not have to look in multiple locations for scheduled database procedures. It would also be easy to overview all the procedures using the dba_scheduler_jobs. The shell scripts are ftp scripts that either pull in and push out flat files, email files or call some other scripts.
1. Are there disadvantages to doing it this way?
2. How do I call ftp process or shell scripts from the database?
I just discovered how to email from the database from asktom so that part I figured out

and Connor said...

My thoughts on this are that it's a matter of "perspective".

For example, if I have 4 databases running on one server, then you could argue that having all the scripting in 'cron', means its *more* centralised than having scripts in each of the 4 databases. Similarly, if you have any scripts that need to run when the database is down, or *detect* that the database is down or not responding...then having them external the database might be better.

Alternatively, if consolidation is the aim - then perhaps an enterprise-level scheduling tool is a better option. That could be Enterprise Manager or some other 3rd party tool.

Anyway...to run a shell script from the database:

  dbms_scheduler.create_program (
    program_name        => 'my_script',
    program_type        => 'executable',
    program_action      => '/scripts/my_script.sh',
    number_of_arguments => 0,
    enabled             => true,
    comments            => 'shell script');


and then you schedule the *program* in the same way you would schedule anything else.

Hope this helps.

Rating

  (2 ratings)

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

Comments

A reader, February 21, 2016 - 7:06 pm UTC

Yes you are right, this is actually how I have the scripts running at the moment. I have some scripts running from database control and some in cron. My reasoning for wanting to run from within the database is to avoid the schema password in plain text within the scripts (solved that using external identified user), allow the script to run on any available RAC instance and easy to disable. My other reason is cron is not very user friendly, if there are a lot of jobs in cron, it can be hard to glance and find what you are looking to change (Maybe there are GUI tools I am not aware of). Scripts that need to be run on the OS would be called from within the pl/sql scripts. For example one of the jobs I run has the following steps
- use ftp to pull in flat file from filezilla server
- sqlplus script to load the flat file as an external table and import it into an oracle table
- send an email to an external agency with a subset of that data

So my shell script calls an ftp script and then calls the sql script and then calls a pl/sql procedure to email the file. I figure using one pl/sql script for the entire process makes more sense and I dont have to worry about schema passwords and it is easy to see all the jobs for that particular database under dba_scheduler_jobs.
The create program package allows me to create the program but how do I execute it?
Come to think of it if the database has packages that can handle emailing, ftping, write and read files there might be no need for shell scripts at all.
Chris Saxon
February 22, 2016 - 12:26 am UTC

re: "The create program package allows me to create the program but how do I execute it? "

You just schedule it, like anything else in the scheduler using create_job.

re: "password in plain text within the scripts"

Check out wallets - no more plaintext passwords needed.

https://connormcdonald.wordpress.com/2015/09/21/connection-shortcuts-with-a-wallet/


fynix, February 21, 2016 - 7:22 pm UTC

I forgot to mention, database control and its issues in a RAC environment is what got me thinking about this in the first place. If the node running the EM console goes down then the procedures won't run and currently management is cutting budget so getting a server for running OEM is like pulling teeth lol.
Chris Saxon
February 22, 2016 - 12:29 am UTC

:-)
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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library