Hai All,
I am trying to Create a setup in which to send system commands.So i created packages for Daemon and the package is created successfully.while compiling this daemon.pc C program i am facing errors like DBMS_PIPE.PACK_MESSAGE must be declared etc...which i am not able to debug it.I here with sending the code of it. I am compiling this daemon.pc as proc sqlcheck=semantics daemon.pc.



/************************************************************************
File: daemon.sql

This is the source code for the daemon package. It sends messages to the
daemon listener via dbms_pipe. The package has two functions and one
procedure:

execute_sql: passes the sql command given by the first argument to the daemon
listener for execution. The sql command must not be a query.
Returns the sqlcode after execution of the command.
execute_system: passes the system command given by the first argument to the
daemon listener for execution in the operating system. Returns
the result of the system command.
stop: causes the daemon to exit. After this command, calls to execute_sql
and execute_system will fail until the daemon is restarted.

This package sends the first message to the daemon over the pipe named
'daemon'. As part of this message, the return pipe name is passed. The name
of the return pipe is the value of dbms_pipe.unique_session_name. This way,
each session will be listening on its own pipe, and so one session won't
receive the messsages meant for another.

************************************************************************/

create or replace package daemon as
/* Executes a non-query sql statement or plsql block. Arguments:
command: the sql statement to execute
timeout: (optional) number of seconds to wait to send or receive a
message
Returns the sqlcode after execution of the statement. */
function execute_sql(command varchar2, timeout number default 10)
return number;

/* Executes a system (host) command. Arguments:
command: the command to execute
timeout: (optional) number of seconds to wait to send or receive a
message
Returns the value passed to the operating system by the command. */
function execute_system(command varchar2, timeout number default 10)
return number;

/* Tells the daemon listener to exit. Arguments:
timeout: (optional) number of seconds to wait to send the message. */
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

/* Use uniqe_session_name to generate a unique name for the return pipe.
We include this as part of the inital message to the daemon, and it is
send along the pipe named 'daemon'. */
pipe_name := dbms_pipe.unique_session_name;

/* Send the 'SYSTEM' command to the daemon. */
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;

/* Check for the handshake message. Note that we are now listening on
the pipe which is unique to this session. */
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;

/* Get the operating system result code, and display it using
dbms_output.put_line(). */
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


/* Use uniqe_session_name to generate a unique name for the return pipe.
We include this as part of the inital message to the daemon, and it is
send along the pipe named 'daemon'. */
pipe_name := dbms_pipe.unique_session_name;

/* Send the 'SQL' command to the daemon. */
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;

/* Check for the handshake message. Note that we are now listening on
the pipe which is unique to this session. */
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;

/* Get the result code from the SQL statement, and display it using
dbms_output.put_line(). */
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

/* Send the 'STOP' command to the daemon. */
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;





/************************************************************************
File: daemon.pc

This is the source code for the daemon listener to implement dynamic sql
and system commands from plsql. The program accepts three
'daemon commands':

STOP: causes the daemon to disconnect from Oracle and exit.
SYSTEM: causes the daemon to execute the next item on the pipe as a
operating system command.
SQL: causes the daemon to execute the next item on the pipe as an
sql statement. Also returns the sqlcode resulting from the
statement.

The daemon commands are received over the pipe named 'daemon'. As part of the
first message sent along this pipe, the name of the pipe to use for the return
session is passed.

************************************************************************/
#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;

/* This is the error handler for connecting to Oracle. If we failed on the
connection attempt, we need to exit the program. */
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);
}


/* This is the general error handler. Note that we don't exit the program in
this case. We just print the error and continue. This is because any
errors probably will not affect future operations, and we should keep the
daemon running. This of course depends on the error, and you may want to
change this behavior. */
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) {
/* Wait for a message to be received, using pipe daemon. */
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) {
/* At this point, we have successfully received a message. Now we
need to determine which daemon command to execute. */
command.arr[command.len] = '\0';
if (!strcmp((char *)command.arr, "STOP")) {
/* STOP command received. Simply exit the program. */
printf("Daemon exiting.\n");
break;
}

else if (!strcmp((char *)command.arr, "SYSTEM")) {
/* SYSTEM command received. Unpack the next 2 values. These will be
the name of the return pipe, and the command to pass to the
operating 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);

/* Execute the command. */
status = system(value.arr);

/* Send a message back to indicate that the command has been
executed. Also send the result of the system command.
Use the pipe passed in from the first message for this. */

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")) {
/* SQL command received. Unpack the next 2 values. These will be
the name of the return pipe, and the SQL command to execute. */
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);

/* Execute the command. Note that we don't want to go to the error
handler if there is a problem - we just pass the code back. */
EXEC SQL whenever sqlerror continue;
EXEC SQL EXECUTE IMMEDIATE :value;
status = sqlca.sqlcode;

/* Reset the error handler, and send a message back to indicate
that the command has been executed. Also send the sqlcode.
Use the pipe passed in from the first message for this. */

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 {
/* Invalid daemon command received. */
printf("Daemon error: invalid command '%s' received.\n", command.arr);
}
}
else {
/* We get here if an error was received while the daemon was waiting.
If the status = 1, this is a timeout and is probably not a problem.
However, the default timeout for the receive_message function is
1000 days, so unless the daemon is kept running for over 3 years
without receiving a signal, you won't time out. */
printf("Daemon error while waiting for signal.");
printf(" status = %d\n", status);
}
}

EXEC SQL commit work release;
}