Sent: 30 September 2002 13:46
To: LazyDBA.com Discussion

This header confirms that this email message has been swept for the presence of computer viruses. 

Corporate IT
THE WOOLWICH
----------------------------------------------------------------------

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 ----- 
To: "LazyDBA.com Discussion" <oracledba@lazydba.com>
Sent: Monday, September 30, 2002 3:08 AM


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


-----Original Message-----
Sent: Monday, September 30, 2002 12:33 PM
To: Dibyendu Kole-VP


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



--------
Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-unsubscribe@LAZYDBA.com
To subscribe:   send a blank email to oracledba-subscribe@LAZYDBA.com
Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html