Re: Can I do a system call (unix call) from pl/sql
Date: 1996/08/23
Message-ID: <321DF2CE.86E_at_marykay.com>
Lianyi Zhu wrote:
>
> Hi Gurus:
>
> I am new to pl/sql programming. I need to call a C/PERL/SHELL program
> from PL/SQL. Can I do it? How?
>
> Your help is greatly appreciated.
>
> Thank you,
>
> LianyiTry using pipes. Here is some SQL to create some packages that may
be helpful - you may have to play with it a bit to get it to do what
you are looking for...
/*****************************************************************/
/* ???_pipe_server.sql */
/* */
/* Function and Stored Procedures to take advantage of Oracle */
/* database pipes. */
/* */
/* These objects should be created using the "???" id. */
/* */
/* The function (???_unix_trigger): */
/* - packs a returnpipe address on the pipe named */
/* - packs its argument(s) on the pipe named "??? unix req" */
/* - sends the piped request */
/* */
/* An external program executes a loop which: */
/* - calls ???_pending_server (package) to read the pipe */
/* (procedure: get_unix_request()) */
/* - writes data to a file */
/* - updates record status */
/* */
/* To stop the external program from running, issue the following*/
/* command in SQL*Plus: */
/* - execute ???_pending_server.stop('unix'); */
/* */
/* */
/* Modification Log */
/* Date Who Description */
/* 08/22/95 M Bailey Created. */
/* */
/*****************************************************************/
connect ???
CREATE OR REPLACE TRIGGER ???_unix_trigger AFTER INSERT ON ???.pending_unix_requests
FOR EACH ROW
DECLARE
s integer;
BEGIN
dbms_pipe.pack_message(dbms_pipe.unique_session_name);
dbms_pipe.pack_message(:new.REQUEST_SEQUENCE_NUMBER );
dbms_pipe.pack_message(:new.ACTION_INDICATOR );
dbms_pipe.pack_message(:new.STATUS_INDICATOR);
s := dbms_pipe.send_message('??? unix req');
END;
/
/*****************************************************************/
CREATE OR REPLACE PACKAGE ???_pending_server AS
PROCEDURE get_unix_request(wi_REQUEST_SEQ_NUM OUT NUMBER
, wv_ACTION_INDICATOR OUT VARCHAR2
, wv_STATUS_INDICATOR OUT VARCHAR2
) ;
PROCEDURE stop( pipe IN VARCHAR2);
END;
/
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
CREATE OR REPLACE PACKAGE BODY ???_pending_server AS
returnpipe VARCHAR2(30);
PROCEDURE get_unix_request(wi_REQUEST_SEQ_NUM OUT NUMBER
, wv_ACTION_INDICATOR OUT VARCHAR2
, wv_STATUS_INDICATOR OUT VARCHAR2)
IS
s INTEGER ;
BEGIN
s := dbms_pipe.receive_message('??? unix req');
dbms_pipe.unpack_message(returnpipe);
dbms_pipe.unpack_message(wi_REQUEST_SEQ_NUM);
dbms_pipe.unpack_message(wv_ACTION_INDICATOR);
dbms_pipe.unpack_message(wv_STATUS_INDICATOR);
END;
PROCEDURE stop(pipe IN VARCHAR2) IS
s integer;
BEGIN
IF LOWER(pipe) = 'unix' THEN
dbms_pipe.purge('??? unix req');
dbms_pipe.pack_message('?');
dbms_pipe.pack_message( 0 );
dbms_pipe.pack_message('stop');
dbms_pipe.pack_message('?');
s := dbms_pipe.send_message('??? unix req');
END IF;
END;
END;
/
/*****************************************************************/GRANT EXECUTE ON ???_pending_server TO PUBLIC /
-- ################################################################### # baileyma_at_marykay.com My opinions, although correct, are not # # necessarily those of Mary Kay, Inc. # ###################################################################Received on Fri Aug 23 1996 - 00:00:00 CEST