Home » SQL & PL/SQL » SQL & PL/SQL » calling UNIX SHELL SCRIPT from oracle stored procedure PL/SQL
calling UNIX SHELL SCRIPT from oracle stored procedure PL/SQL [message #227783] Thu, 29 March 2007 07:16 Go to next message
pradeep.ramana
Messages: 12
Registered: March 2007
Junior Member
Hi All,

Can anyone tell me how to call UNIX SHELL SCRIPT from oracle stored procedure PL/SQL

Thanks in Advance

Regards,
Re: calling UNIX SHELL SCRIPT from oracle stored procedure PL/SQL [message #227798 is a reply to message #227783] Thu, 29 March 2007 07:35 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The only way to do this is to use a Java procedure. If you search this site or the AskTom site, you should be able to find it fairly easily.
Re: calling UNIX SHELL SCRIPT from oracle stored procedure PL/SQL [message #229478 is a reply to message #227783] Sun, 08 April 2007 18:49 Go to previous messageGo to next message
timarcher52
Messages: 5
Registered: April 2007
Junior Member
You can do it using an oracle external procedure. I did a small writeup on how to set this all up to run host commands/shell scripts on the database server from PL/SQL. I think this will help Go to the following URL to read it:
http://timarcher.com/?q=node/9

I hope it helps you!
Re: calling UNIX SHELL SCRIPT from oracle stored procedure PL/SQL [message #229744 is a reply to message #229478] Mon, 09 April 2007 22:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Also possible in 10g using DBMS_SCHEDULER
Re: calling UNIX SHELL SCRIPT from oracle stored procedure PL/SQL [message #229745 is a reply to message #229744] Mon, 09 April 2007 22:24 Go to previous messageGo to next message
timarcher52
Messages: 5
Registered: April 2007
Junior Member
I agree that you can execute shell scripts through DBMS_SCHEDULER, but I dont believe you can use DBMS_SCHEDULER inline with a PL/SQL procedure. From what I understand, DBMS_SCHEDULER schedules a job to run (can be scheduled immediately), and then the Oracle job scheduler runs it when it is free.

However, what I needed is for a PL/SQL script to get executed (lets say a document rendering program), run some SQL to collect all the variables to be placed on the letter, call a shell script to actually run commands to render a PDF, and then return a success or failure to the PL/SQL program so it can then update some tables tracking the location of the file, status of the render, etc. Essentially, the PL/SQL program needs to wait until the shell command is done running, then get the unix return code of the shell command that was run, and then can continue running pl/sql.

I dont think I can get the specific unix process return code from DBMS_SCHEDULER, nor can I wait within my PL/SQL procedure until DBMS_SCHEDULER runs the job before moving on. Is my understanding of this correct?
Re: calling UNIX SHELL SCRIPT from oracle stored procedure PL/SQL [message #229749 is a reply to message #229745] Mon, 09 April 2007 22:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You are right about getting good feedback (beyond success or failure) from DBMS_SCHEDULER, but it is possible to run it synchronously. You schedule it for a single run at some arbitrarily large future date, and then in the next command call DBMS_SCHEDULER.RUN_JOB(my_job, TRUE) to run it in the current session.

To get good feedback, you would have to log messages to a file, which you could later process with UTL_FILE.

All-in-all, external procs are a neater solution, but DBMS_SCHEDULER makes it possible without external components that can sometimes be a config/promotion hinderance.

Ross Leishman
Re: calling UNIX SHELL SCRIPT from oracle stored procedure PL/SQL [message #229753 is a reply to message #229749] Mon, 09 April 2007 22:50 Go to previous message
timarcher52
Messages: 5
Registered: April 2007
Junior Member
I'll have to give that a shot when I have time. Thanks for the input Ross, I never thought to try to use DBMS_SCHEDULER for synchronous code execution! I learn something new every day.
Previous Topic: First Day of the month
Next Topic: Privileges for using execute immediate or dbms_sql for DDL in Pl/SQL block
Goto Forum:
  


Current Time: Thu Dec 08 12:50:54 CST 2016

Total time taken to generate the page: 0.09619 seconds