RE: can we call unix scripts from oracle database

  • From: <jim.silverman@xxxxxxxxxxx>
  • To: <edwin_kodamala@xxxxxxxxx>, <knecht.stefan@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 30 Mar 2007 10:20:20 -0400

Edwin, apparently our Exchange server, in its infinite wisdom,
considered the ".ksh" file containing the shell script snippet, as
"potentially harmful" and stripped it from the message.  I've now
attached it in a ".txt" file; hopefully it will arrive successfully this
time.

 <<x1.txt>> 
=====================================
Jim Silverman
Senior Systems Database Administrator
Solucient, LLC - A Thomson Company
Telephone:   734-669-7641
FAX:            734-930-7611
E-Mail:         jim.silverman@xxxxxxxxxxx


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Silverman, James (TH
USA)
Sent: Friday, March 30, 2007 9:19 AM
To: edwin_kodamala@xxxxxxxxx; knecht.stefan@xxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
Subject: RE: can we call unix scripts from oracle database

Edwin, attached to this message are two text files.  The first, x1.sql,
contains a snippet of PL/SQL code that will create a job, assign values
to its arguments, and enable (i.e., call) it.  The second, x1.ksh,
contains the start of a shell script that could serve as the executable
invoked by the database-generated job.

This code lives in a 10.1.0.5 database on a Solaris host.

A couple of things I learned along the way:

*       The environment for the process in which the executable file
runs is virtually empty - not even a PATH variable exists - so you'll
have to establish the environment on your own.
*       Depending on the platform on which the database resides, and the
method by which the Oracle software was installed, you may encounter
ORA-27369 errors (job of type EXECUTABLE failed with exit code ...).  If
so, consult Metalink for specific solutions based on your environment.

 <<x1.sql>>  <<x1.ksh>> 
===================================== 
Jim Silverman 
Senior Systems Database Administrator 
Solucient, LLC - A Thomson Company 
Telephone:   734-669-7641 
FAX:            734-930-7611 
E-Mail:         jim.silverman@xxxxxxxxxxx

#!/bin/ksh
################################################################################
#   The script  at "full-pathname-to-executable" referenced  in the call  to the
#   procedure dbms_scheduler.create_job.  Note that  the environment for this OS
#   process is VERY  empty - it doesn't  even contain a PATH, so  you'll need to
#   bootstrap assuming nothing ...
################################################################################

PATH=/usr/bin:/usr/ucb:/usr/sbin: ...
export PATH

var_1=$1
var_2=$2

...

Other related posts: