RE: Calling UNIX Shell Scripts from a Stored Procedure

From: Wasmund, Derek <DWasmund_at_EFTIA.com>
Date: Fri, 16 Jun 2000 15:42:06 -0400
Message-Id: <10530.109609@fatcity.com>

There is a few steps involved in setting up external stored procedures.

You must first setup a second listener or add additional parameters to your existing listener, you will also have configure you tnsnames to support the external procedure call.

Second you will have to create a library that points to the script ro command on the operating system. For example ..

CREATE OR REPLACE LIBRARY library_name

    AS 'directory/mycprogram.so
/

Third you would create a package that will be responsible for talking to your C program. External stored procs will only talk to C programs. For example ..

CREATE OR REPLACE
PACKAGE package_name
IS

    FUNCTION function_name

       (   )
     RETURN ;

    PRAGMA RESTRICT_REFERENCES (function_name, WNDS, RNDS, WNPS, RNPS); END package_name;
/

CREATE OR REPLACE
PACKAGE BODY package_name
IS

    FUNCTION function_name
    ( )

     RETURN PLS_INTEGER    IS EXTERNAL
       LIBRARY library_name
       NAME "mycprogram"
       LANGUAGE C
    WITH CONTEXT
       PARAMETERS
          (CONTEXT,
    RETURN     int);

END package_name;
/

Fourth you will create you C program.

Fifth you will test and fix problems.

Sixth you will test and fix problems.

Seventh you will test and fix problems...

...Well I think you get the picture, hope this helps!

Regards!

Derek

Eftia Corporation
Ottawa, On
Canada

-----Original Message-----
From: David Mauzey [mailto:dmj_at_mdcl.com] Sent: Friday, June 16, 2000 12:48 PM
To: Multiple recipients of list ORACLE-L Subject: Calling UNIX Shell Scripts from a Stored Procedure

Can anyone give me any advice on how to execute a simple UNIX shell script from an Oracle stored procedure? The UNIX shell script will simply use SQL*Loader to load a file into the database. I have heard whispers about several methods to accomplish this task; however, I haven't yet seen any examples or documentation on the subject.

Thank you.

David Mauzey
ppoONE.com
a MedicalControl Company
(214) 267-3410
dmj_at_ppoone.com

-- 
Author: David Mauzey
  INET: dmj_at_mdcl.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Fri Jun 16 2000 - 14:42:06 CDT