Re: Trigger using DBMS_PIPE won't parse

Try granting execute on dbms_pipe to public

Jeff Boes wrote:

> Ho-kay, not even my DBA can figure out what's wrong with this TRIGGER
> definition:
>
> CREATE OR REPLACE TRIGGER
> WHPL.DCS_POD_STORED_TRIG
> AFTER INSERT OR UPDATE ON WHPL.DCS_BOL
> FOR EACH ROW
> DECLARE
> STAT NUMBER;
> BEGIN
> FOR MNFST IN ( -- each associated manifest
> SELECT MNFST_NBR,
> MNFST_INPRCSS_DATE
> FROM WHPL.DCS_MDL_MFST
> WHERE BOL_NBR = :NEW.BOL_NBR)
> LOOP
> DBMS_PIPE.PACK_MESSAGE('DNR.MHSHIPMENT.REQUEST'); -- queue name
> DBMS_PIPE.PACK_MESSAGE('MH-SHIPMENT-REQUEST'); -- message ID
> DBMS_PIPE.PACK_MESSAGE(MNFST.MNFST_NBR); -- correl ID
> DBMS_PIPE.PACK_MESSAGE('0'); -- priority
> DBMS_PIPE.PACK_MESSAGE('DCS_POD_STORED_TRIG'); -- sending appl
> DBMS_PIPE.PACK_MESSAGE(MNFST.MNFST_NBR || -- fields
> TO_CHAR(MNFST.MNFST_INPRCSS_DATE, 'YYYYMMDD'));
> STAT := DBMS_PIPE.SEND_MESSAGE('DNR_ORA_MQ_PIPE'); -- send it
> END LOOP;
> END;
>
> The BEGIN-END block works just fine, when entered by itself, but the trigger
> definition complains about 'SYS.DBMS_PIPE' not being declared.
Received on Tue Oct 28 1997 - 00:00:00 CST