Re: Excuting shell commands from trigger
Date: 1998/02/11
Message-ID: <34E134C3.525AECFC_at_infocomm.com.tw>
Peter Chan wrote:
> I was wondering if it is possible to execute UNIX shell commands from
> a trigger?
>
> And if it is possible, how is this done.
>
> Thanks!
> --
>
I got some solution as follow :
System Commands Using DBMS_PIPE
Two features which would be very nice in PL/SQL are the ability to
execute a
system command (like HOST in SQL*Forms), and the capability to execute
dynamic
SQL
daemon.pc :
daemon.pc is the source code for a C program, called a 'daemon'. This
is
because it needs to be running before it can receive messages. However,
it is
'sleeping' most of the time. In this state, it is looping while waiting
for a
message to be received over the pipe. When a message is received, it
will
'wake up' and process the message. The term 'daemon' is common in
discussions
about the UNIX operating system, and there are many UNIX daemons. The
'sleeping' and 'waking up' are implemented by calls to
dbms_pipe.receive_message(), which will sleep until a message is
received.
#include <stdio.h>;
#include <string.h>;
EXEC SQL include sqlca;
EXEC SQL begin declare section;
char *uid = "scott/tiger"; /* User/password to connect to Oracle */
int status; /* Return value for dbms_pipe.send_message
and dbms_pipe.receive_message */
varchar command[20]; /* Daemon command to execute */
varchar value[2000]; /* Value (SQL statement or system command)
associated with previous daemon command
*/
varchar return_name[30]; /* Name of the pipe on which to send the
results */
EXEC SQL end declare section;
void connect_error() {
char msg_buffer[512];
int msg_length;
int buffer_size = 512;
EXEC SQL whenever sqlerror continue;
sqlglm(msg_buffer, &buffer_size, &msg_length);
printf("Daemon error while connecting:\n");
printf("%.*s\n", msg_length, msg_buffer);
printf("Daemon quitting.\n");
exit(1);
}
void sql_error() {
char msg_buffer[512];
int msg_length;
int buffer_size = 512;
EXEC SQL whenever sqlerror continue;
sqlglm(msg_buffer, &buffer_size, &msg_length);
printf("Daemon error while executing:\n");
printf("%.*s\n", msg_length, msg_buffer);
printf("Daemon continuing.\n");
}
main() {
EXEC SQL whenever sqlerror do connect_error();
EXEC SQL connect :uid;
printf("Daemon connected.\n");
EXEC SQL whenever sqlerror do sql_error();
printf("Daemon waiting...\n");
while (1) {
EXEC SQL EXECUTE
begin
:status := dbms_pipe.receive_message('daemon');
if :status = 0 then
dbms_pipe.unpack_message(:command);
end if;
end;
END-EXEC; if (status == 0) {
command.arr[command.len] = '\0';
if (!strcmp((char *)command.arr, "STOP")) {
printf("Daemon exiting.\n");
break;
}
else if (!strcmp((char *)command.arr, "SYSTEM")) {
EXEC SQL EXECUTE
begin
dbms_pipe.unpack_message(:return_name);
dbms_pipe.unpack_message(:value);
end;
END-EXEC;
value.arr[value.len] = '\0';
printf("Will execute system command '%s'\n", value.arr);
status = system(value.arr);
EXEC SQL EXECUTE
begin
dbms_pipe.pack_message('done');
dbms_pipe.pack_message(:status);
:status := dbms_pipe.send_message(:return_name);
end;
END-EXEC;
if (status) {
printf("Daemon error while responding to system command.");
printf(" status: %d\n", status);
}
}
else if (!strcmp((char *)command.arr, "SQL")) {
EXEC SQL EXECUTE
begin
dbms_pipe.unpack_message(:return_name);
dbms_pipe.unpack_message(:value);
end;
END-EXEC;
value.arr[value.len] = '\0';
printf("Will execute sql command '%s'\n", value.arr);
EXEC SQL whenever sqlerror continue;
EXEC SQL EXECUTE IMMEDIATE :value;
status = sqlca.sqlcode;
EXEC SQL whenever sqlerror do sql_error();
EXEC SQL EXECUTE
begin
dbms_pipe.pack_message('done');
dbms_pipe.pack_message(:status);
:status := dbms_pipe.send_message(:return_name);
end;
END-EXEC;
if (status) {
printf("Daemon error while responding to sql command.");
printf(" status: %d\n", status);
}
}
else {
printf("Daemon error: invalid command '%s' received.\n",
command.arr);
}
}
else {
printf("Daemon error while waiting for signal.");
printf(" status = %d\n", status);
}
}
EXEC SQL commit work release;
}
daemon.sql
daemon.sql is the source code for a PL/SQL package. This package has
procedures which use dbms_pipe to send and receive message to and from
the
daemon. One thing to note is that full handshaking is used. This means
that
the daemon will always send a message back to the package (except in the
case
of the 'STOP' command). This is valuable, since it allows us to make
sure
that the daemon is running.
create or replace package daemon as
function execute_sql(command varchar2, timeout number default 10) return number;
function execute_system(command varchar2, timeout number default 10)
return number;
procedure stop(timeout number default 10);
end daemon;
/
create or replace package body daemon as
function execute_system(command varchar2, timeout number default 10) return number is
s number;
result varchar2(20);
command_code number;
pipe_name varchar2(30);
begin
pipe_name := dbms_pipe.unique_session_name;
dbms_pipe.pack_message('SYSTEM');
dbms_pipe.pack_message(pipe_name);
dbms_pipe.pack_message(command);
s := dbms_pipe.send_message('daemon', timeout); if s <> 0 then
raise_application_error(-20010,
'Execute_system: Error while sending. Status = ' || s);
end if;
s := dbms_pipe.receive_message(pipe_name, timeout); if s <> 0 then
raise_application_error(-20011,
'Execute_system: Error while receiving. Status = ' || s);
end if;
dbms_pipe.unpack_message(result);
if result <> 'done' then
raise_application_error(-20012,
'Execute_system: Done not received.');
end if;
dbms_pipe.unpack_message(command_code); dbms_output.put_line('System command executed. result = ' ||
command_code);
return command_code;
end execute_system;
function execute_sql(command varchar2, timeout number default 10) return number is
s number;
result varchar2(20);
command_code number;
pipe_name varchar2(30);
begin
pipe_name := dbms_pipe.unique_session_name;
dbms_pipe.pack_message('SQL');
dbms_pipe.pack_message(pipe_name);
dbms_pipe.pack_message(command);
s := dbms_pipe.send_message('daemon', timeout); if s <> 0 then
raise_application_error(-20020,
'Execute_sql: Error while sending. Status = ' || s);
end if;
s := dbms_pipe.receive_message(pipe_name, timeout); if s <> 0 then
raise_application_error(-20021,
'Execute_sql: Error while receiving. Status = ' || s);
end if;
dbms_pipe.unpack_message(result);
if result <> 'done' then
raise_application_error(-20022,
'Execute_sql: Done not received.');
end if;
dbms_pipe.unpack_message(command_code); dbms_output.put_line('SQL command executed. sqlcode = ' || command_code);
return command_code;
end execute_sql;
procedure stop(timeout number default 10) is
s number;
begin
dbms_pipe.pack_message('STOP');
s := dbms_pipe.send_message('daemon', timeout);
if s <> 0 then
raise_application_error(-20030,
'Stop: Error while sending. Status = ' || s);
end if;
end stop;
end daemon;
/
Michael Hsiao Received on Wed Feb 11 1998 - 00:00:00 CET