Re: Problem with DBMS_PIPES

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 25 Aug 1999 14:26:58 +0800
Message-ID: <37C38CB2.59F_at_yahoo.com>


sgundu wrote:
>
> 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 */
> }
> }

If you're on Oracle8, you could use external procedures to achieve a similar result without using pipes...

HTH

-- 
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"
connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue."
Received on Wed Aug 25 1999 - 08:26:58 CEST

Original text of this message