Problem with DBMS_PIPES

From: sgundu <sgundu_at_tdcs.tel.gte.com>
Date: 24 Aug 1999 21:38:43 GMT
Message-ID: <7pv3d3$aag$1_at_news.gte.com>

[Quoted] Hello Everyone,

               I had a problem with DBMS_PIPEs : Here is the Problem:

     I am trying to email the values inserted into a database table. These values are inserted from a Stored Procedure,

executed via Oracle Webserver. When i created a test table in database and Pro*C program on system, i can able to e-mail the

inserted values, I packed the values in trigger. But when I am trying to use Multiple DBMS_PIPE.Pack_Message's in trigger and

Multiple DBMS_PIPE.Receive_Message's in Pro*C, I am not getting anything into the Pro*C program. And if i combine all the

variables into one variable in the trigger and packing that single variable and if i send that variable, i can be able to

receive and unpack it in Pro*C. I don't know what is the reason for it is not working, when i am using multiple packs and

unpacks. Looks like It is receving some thing...bcoz the status variable is zero. But i couldn't see any of the values

inserted in the table from Pro*C program.

Does anybody can tell me why this is not working for multiple pack's and unpack's?

Is there any other easy way to send an email from Oracle WebServer?

Here is my trigger and Pro*C program:

I appreciate Your help on this.

CREATE OR REPLACE TRIGGER email_trouble_ticket

          AFTER INSERT OR UPDATE ON trouble
	  FOR EACH ROW
DECLARE
         result NUMBER(4);
         email_ticket varchar2(3500); 
	BEGIN
              IF ( Lower(SUBSTR(:new.open_by,2)) = LOWER(SUBSTR(:new.assigned_to,(instr(:new.assigned_to,'.') + 1) ) ) ) THEN
              NULL;
              ELSE
             -- DBMS_PIPE.PACK_MESSAGE('I');          
              DBMS_PIPE.PACK_MESSAGE(:new.system);
	      DBMS_PIPE.PACK_MESSAGE(:new.ticket_no);
              DBMS_PIPE.PACK_MESSAGE(:new.open_by);
              DBMS_PIPE.PACK_MESSAGE(:new.reported_by);
              DBMS_PIPE.PACK_MESSAGE(:new.assigned_to);
              DBMS_PIPE.PACK_MESSAGE(:new.problem);
              DBMS_PIPE.PACK_MESSAGE(:new.description);
              DBMS_PIPE.PACK_MESSAGE(:new.reported_date);
              DBMS_PIPE.PACK_MESSAGE(:new.reported_time);
              DBMS_PIPE.PACK_MESSAGE(:new.date_closed);
              DBMS_PIPE.PACK_MESSAGE(:new.time_closed); 
    
                -- DBMS_PIPE.PACK_MESSAGE(email_ticket);
    
	     result := DBMS_PIPE.SEND_MESSAGE('emailoutputpipe');
             END IF;
		IF result <> 0 THEN
                  RAISE_APPLICATION_ERROR(-2000,'Mailer Error');
                END IF;

END;   Pro*C program:

#include <stdio.h>
/* #include "/u01/app/oracle/product/8.0.5/precomp/lib/pdc.o"*/ EXEC SQL BEGIN DECLARE SECTION;

        int     status;
        varchar retval[2000];
        varchar    v_code[5];
        varchar    v_system[8];
        int     v_ticket_no;
        varchar    v_open_by[15];
        varchar    v_reported_by[20];
        varchar    v_assigned_to[20];
        varchar    v_problem[25];
        varchar    v_description[2000];
        varchar    v_reported_date[20];
        varchar    v_reported_time[10];
        varchar    v_date_closed[20];
        varchar    v_time_closed[10];
        char *uid = "scott/tiger_at_ims";

EXEC SQL END DECLARE SECTION;   EXEC SQL INCLUDE SQLCA;
main()
{
char email[200];

     /* Connect to the same schema which created the pipe in 
                the database trigger */
FILE *outfile;
     EXEC SQL CONNECT :uid;
     for (;;)
     {
         EXEC SQL EXECUTE
             DECLARE
                typ INTEGER;
                sta INTEGER;
                chr VARCHAR2(2000);
             BEGIN
                 chr := '';
                 /* Receive the message */      
                 sta := dbms_pipe.receive_message('emailoutputpipe');
                 IF sta = 0 THEN
                        /* Unpack the message */
                     /* dbms_pipe.unpack_message(chr); */
                    
                     dbms_pipe.unpack_message(:v_code);
                     dbms_pipe.unpack_message(:v_system);
                     dbms_pipe.unpack_message(:v_ticket_no);
                     dbms_pipe.unpack_message(:v_open_by);
                     dbms_pipe.unpack_message(:v_reported_by);
                     dbms_pipe.unpack_message(:v_assigned_to);
                     dbms_pipe.unpack_message(:v_problem);
                     dbms_pipe.unpack_message(:v_description);
                     dbms_pipe.unpack_message(:v_reported_date);
                     dbms_pipe.unpack_message(:v_reported_time);
                     dbms_pipe.unpack_message(:v_date_closed);
                     dbms_pipe.unpack_message(:v_time_closed); 
                   
                 END IF;
                 :status := sta;
                -- :retval := chr;
             END;
         END-EXEC;
         if (status == 0)  {
              outfile = fopen("email.log","w");
               /* fprintf(outfile," Mail String:   %s\n",retval); */
               
                fprintf(outfile," V_CODE:   %s\n",v_code);
                fprintf(outfile,"Trouble Ticket From %s\n",v_open_by);
                fprintf(outfile,"Ticket Number: %s\n",v_ticket_no);
                fprintf(outfile,"System: %s\n",v_system);
                fprintf(outfile,"Open By: %s\n",v_open_by);
                fprintf(outfile,"Reported By: %s\n",v_reported_by);
                fprintf(outfile,"Assigned To: %s\n",v_assigned_to);
                fprintf(outfile,"Problem: %s\n",v_problem);
                fprintf(outfile,"Description: %s\n",v_description);
                fprintf(outfile,"Reported Date: %s\n",v_reported_date);
                fprintf(outfile,"Reported Time: %s\n",v_reported_time);
                fprintf(outfile,"Date Closed: %s\n",v_date_closed);
                fprintf(outfile,"Time closed: %s\n",v_time_closed); 
                 
                fclose(outfile);
 sprintf(email,"mail -s \"Trouble Ticket from \" sreeni.gundu_at_telops.gte.com < ;
                system(email); 
                }
                /* The system() call will execute the
                "mail" string which is sent over the pipe */
     }

} Received on Tue Aug 24 1999 - 23:38:43 CEST