DBMS_PIPE Problem
Hi,
I am trying to call "OS Command from Pl/sql" using DBMS_PIPE, for this I have created a Proc*C program and PL/SQL package. But when I call the function in a PL/SQL package then I am getting the error
declare
a number;
Begin
a := pipesys.execute_system('ls');
dbms_output.put_line('a = ' ||a );
End;
/
ORA-20011: Execute_system: Error while receiving. Status = 1
ORA-06512: at "NISMNT.PIPESYS", line 25
ORA-06512: at line 4
My Proc*C program is as such(pipesys.pc)
---------------------------------
#include <sys/types.h>
#include <sys/resource.h>
#include <sys/wait.h>
#include <stdio.h>
#include <string.h>
EXEC SQL include sqlca;
#include <signal.h>
EXEC SQL begin declare section;
char *uid = "nismnt/nismnt";
int status;
varchar command[20];
varchar value[2000];
varchar return_name[30];
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("pipesys error while connecting:\n");
printf("%.*s\n", msg_length, msg_buffer);
printf("pipesys 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("pipesys error while executing:\n");
printf("%.*s\n", msg_length, msg_buffer);
if ((sqlca.sqlcode ==-3114) || (sqlca.sqlcode == -1092))
{
printf("pipesys Exiting ");
exit(1);
}
printf("pipesys continuing.\n");
}
int childpid = 0;
int flag = 0;
int s=0;
void sigalarm();
struct sigaction act;
main()
{
struct sqlca sqlca;
sql_context ctxQuery;
EXEC SQL CONTEXT USE :ctxQuery;
EXEC SQL whenever sqlerror do connect_error();
EXEC SQL connect :uid; printf("pipesys connected.\n");
EXEC SQL whenever sqlerror do sql_error();
printf("pipesys waiting...\n");
writetofile("pipesysn waiting .....");
while (1)
{
flag=0;
EXEC SQL EXECUTE
begin
:status := dbms_pipe.receive_message('pipesys');
if :status = 0 then
dbms_pipe.unpack_message(:command);
end if;
end;
END-EXEC;
writetofile("pipesysn after receiving .....");
if (status == 0)
{
command.arr[command.len] = '\0';
if (!strcmp((char *)command.arr, "STOP"))
{
printf("pipesys exiting.\n");
writetofile("pipesysn exting .....");
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);
writetofile("Will execute system command");
sigemptyset(&act.sa_mask);
act.sa_handler = sigalarm;
act.sa_flags = 0;
sigaction(SIGALRM,&act,NULL);
alarm(20);
childpid = fork();
if (childpid==0)
{
status = system(value.arr);
printf("the status is %d",status);
writetofile("the status is");
if (status == 0)
exit(0);
else
exit(-1);
}
wait (&s);
alarm(0);
if (s==0)
status=0;
else
status=-1;
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("pipesys error while responding to system command.");
writetofile("pipesys 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);
writetofile("Will execute sql command");
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("pipesys error while responding to sql command.");
writetofile("pipesys error while responding to sql command.");
printf(" status: %d\n", status);
}
}
else
{
/* Invalid pipesys command received. */
printf("pipesys error: invalid command '%s' received.\n", command.arr);
writetofile("invalid command");
}
}
else
{
printf("pipesys error while waiting for signal.");
writetofile("pipesys error while waiting for signal.");
printf(" status = %d\n", status);
}
}
EXEC SQL commit work release;
}
void sigalarm()
{
printf(" The OS Command is hanging so terminating the command\n");
writetofile("The OS Command is hanging so terminating the command");
kill ( childpid,SIGKILL);
}
int writetofile(char *loginfo)
{
FILE *destinationFile;
char *buffer;
int n;
destinationFile = fopen("file2.apl", "a+");
if(destinationFile==NULL)
{
printf("Error: can't create file for writing.\n");
return 1;
}
else
{
fprintf(destinationFile, "%s", loginfo);
fclose(destinationFile);
}
}
PL/SLQ Package
-------------
create or replace package pipesys 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 pipesys;
/
create or replace package body pipesys as
function execute_system(command varchar2, timeout number default 500) 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 pipesys, and it is
send along the pipe named 'pipesys'. */
pipe_name := 'vinay';
/* Send the 'SYSTEM' command to the pipesys. */
dbms_pipe.pack_message('SYSTEM');
dbms_pipe.pack_message(pipe_name);
dbms_pipe.pack_message(command);
s := dbms_pipe.send_message('pipesys', timeout);
dbms_output.Put_line ('S = ' || s);
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('pipesys', timeout);
if s <> 0 then
dbms_output.Put_line (SQLCODE || sqlerrm );
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 pipesys, and it is
send along the pipe named 'pipesys'. */
pipe_name := dbms_pipe.unique_session_name;
/* Send the 'SQL' command to the pipesys. */
dbms_pipe.pack_message('SQL');
dbms_pipe.pack_message(pipe_name);
dbms_pipe.pack_message(command);
s := dbms_pipe.send_message('pipesys', 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 pipesys. */
dbms_pipe.pack_message('STOP');
s := dbms_pipe.send_message('pipesys', timeout);
if s <> 0 then
raise_application_error(-20030, 'Stop: Error while sending. Status = ' || s);
end if;
end stop;
end pipesys;
/
|
