Home » SQL & PL/SQL » SQL & PL/SQL » Unix from Stored Procedure
Unix from Stored Procedure [message #35707] Mon, 08 October 2001 10:55 Go to next message
Vani
Messages: 13
Registered: October 2001
Junior Member
I am trying to call a Unix command or a Unix script from a Oracle stored procedure.
I tried using DBMS_PIPE

create or replace procedure qqq( cmd in varchar2 )
as
status number;
begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end;
It runs fine, but does not do anything when I call the procedure.
Please help ASAP
Vani

----------------------------------------------------------------------
Re: Unix from Stored Procedure [message #35711 is a reply to message #35707] Tue, 09 October 2001 03:12 Go to previous messageGo to next message
bharat
Messages: 11
Registered: February 2000
Junior Member
u are doing half the job only. ur procedure will only put the command on the database pipe name HOST_PIPE. now u need to write a pro*C program which will receive the message from the database pipe like this and execute the command. so u can make the pro*C program a daemon program(put an infinite while loop) which will keep on looking the database pipe for command whenever it gets the command it executes it. run this daemon then execute ur procedure:
EXEC SQL EXECUTE
BEGIN
:status = dbms_pipe.receive_message('HOST_PIPE');
dbms_pipe.unpack_message(cmd);
end;
end-exec;
system(cmd);
this will work

bharat

----------------------------------------------------------------------
Re: Unix from Stored Procedure [message #35713 is a reply to message #35711] Tue, 09 October 2001 06:49 Go to previous message
Vani
Messages: 13
Registered: October 2001
Junior Member
Thanks for the reply.
Could you be more specific. Should I put the
code in a seperate program. How should I compile it? Then how should I pass a Unix command to it?
I have Oracle 7.3.

Thanks in advance
Vani

----------------------------------------------------------------------
Previous Topic: update long column
Next Topic: Table lock
Goto Forum:
  


Current Time: Sat May 25 10:14:31 CDT 2024