Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
|
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 28th, 2004, 05:55 AM
Registered User
 
Join Date: Jun 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to goel_vinay_2000
Default 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;
 /









Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.