Many tasks have been automated using shell scripting languages. Frequently,
these shell scripts need to be called from an Oracle stored procedure, but no
direct interface to call shell scripts is provided. Oracle does, however,
provide a method for calling C and Java programs via external procedures. Since
many shell programmers have never programmed in C or Java, converting shell
scripts or writing new tasks in either of these languages is difficult and time
consuming.


This solution provides a C routine that will make OS calls, and these calls can
be commands, shell scripts, or other application programs. Using this method,
no C programming experience is required, but does allow existing and new tasks
to be written as shell scripts.


This article does not provide information to setup the database or
environment for external procedures.

Refer to How to Configure a UNIX Oracle Server to Use External Procedures for
information on setting up the Oracle Server machine.

The external procedure is written in C and compiled with the Oracle
makefile, and hence, an Oracle supported compiler for the specific UNIX
platform is required. See Certified
Compilers

From the Oracle server machine:


1. Create a file for the external procedure code:


shell.c
=======

#include <stdio.h>

#include <stdlib.h>

#include <string.h>

void sh(char *);

void sh( char *cmd )

{
int num;
num = system(cmd);
}


2. Compile and link the C code into a shared library:

make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk extproc_nocallback \

SHARED_LIBNAME=shell.so OBJS=shell.o

Log into SQL*Plus to perform the remaining steps.

3. Define the shared library in Oracle:

CREATE LIBRARY shell_lib IS '<full path to shared lib>/shell.so';
/

4. Create the PL/SQL wrapper procedure:

CREATE OR REPLACE PROCEDURE shell (cmd IN CHAR)

AS EXTERNAL

NAME "sh"

LIBRARY shell_lib

LANGUAGE C

PARAMETERS (

cmd STRING);

/

5. Call a shell script:

SQL> exec shell('sh myscript.sh');

PL/SQL procedure successfully completed.

===========================================================

HTHU

Ankur Shah
Oracle DBA
DHR-GA

----- Original Message -----
From: <Dibyendu.[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] Monday, September 30, 2002 3:08 AM
Subject: RE: Cal we call a file/URL from a stored procedure


But i want to call the shell script or the file from a stored procedure.


-----Original Message-----
From: Jayesh Rode-PU
Sent: Monday, September 30, 2002 12:33 PM
To: Dibyendu Kole-VP
Subject: RE: Cal we call a file/URL from a stored procedure


there are cron jobs in unix, just like schedulers e.g. dbms_jobs in oracle.
hope this would solve your problem.


Oracle LazyDBA home page