Re: Problems with DBMS_PIPE and Triggers

From: <tkyte_at_us.oracle.com>
Date: 1997/01/21
Message-ID: <853876818.29523@dejanews.com>#1/1

In article <853856335.16719_at_dejanews.com>,   jshiruru_at_att.com wrote:

You cannot create stored objects based on priveleges you gain from a role. You probably have the ability to 'see' the dbms_pipe package via a privelege you have in a role (eg: dba, connect, resource are all roles).

To create the trigger, the user sys should "grant execute on dbms_pipe to YOUR_ACCOUNT". That will allow you to create a stored object (view/procedure/trigger) that can access this object.

To see the errors for a trigger you would:

SQL> show errors trigger TRIG-NAME

Also, the user_errors table may be queried for the same information.

>
> Hi all,
>
> I am creating an oracle trigger which fires after update or insert into
> a table and sends some data through dbms_pipe. The sample code is as
> follows:
>
> CREATE OR REPLACE TRIGGER trig
> AFTER INSERT OR UPDATE OF column1 ON table
> FOR EACH ROW
> WHEN (new.column1 = 'value')
> DECLARE
> send_data VARCHAR(10);
> status integer;
> BEGIN
> send_data := 'sending this data ';
> DBMS_PIPE.PACK_MESSAGE(send_data);
> status := DBMS_SEND_MESSAGE('mesg_pipe');
> END;
> /
> The trigger is created with compilation errors(the errors are not
> printed on the screen) and that is mainly due to
> the use of DBMS_PIPE. I have all the privilegs for dbms_pipe. I ran a
> pl/sql script with PACK and SEND and it works. I can also use PACK, SEND,
> RECEIVE and UNPACK in a Pro*C code, it works. The problem is only when I
> use it in a trigger or a procedure body. Any help is appreciated, and also
> how do you see the compilation errors at the sql prompt ?
>
> Thanks a lot
>
> Jagdeep
> jshiruru_at_att.com
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

Thomas Kyte
Oracle Government

---

opinions are mine and do not necessarily reflect the opinions of Oracle Corporation.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Tue Jan 21 1997 - 00:00:00 CST