Re: after drop trigger : object neither in dba_objects nor dba_recyclebin

On Wednesday, October 2, 2013 10:47:15 AM UTC-7, Syltrem wrote:
> Hello
>
>
>
> In an AFTER DROP trigger I'm trying to lookup some info about the object
>
> being dropped, but found that the object is not found in any of
>
> DBA_RECYCLEBIN, DBA_OBJECTS or OBJ$
>
>
>
> Before the DROP (and execution of the trigger) the object is listed in
>
> DBA_OBJECTS, and after the trigger (or after the DROP is complete) it is
>
> listed in DBA_RECYCLEBIN, but the trigger itself sees nothing at all.
>
>
>
> Is there a workaround for the trigger to get more info about the object ?
>
> I'd like to get the OBJECT_ID.
>
>
>
> Here is a quick example that shows the object is "non-existant" while the
>
> trigger runs
>
>
>
> SQL> create table popo (aa number);
>
>
>
> Table créée.
>
>
>
> SQL> select object_id from user_objects where object_name='POPO';
>
>
>
> OBJECT_ID
>
> ----------
>
> 1508751
>
>
>
> 1 ligne sélectionnée.
>
>
>
> SQL> CREATE OR REPLACE TRIGGER droptrig
>
> 2 after DROP
>
> 3 ON DATABASE
>
> 4 begin
>
> 5 dbms_output.put_line('Looking for object_id=1508751 in dba_objects');
>
> 6 for x in (select owner, object_type, object_name from dba_objects
>
> 7 where object_id= 1508751)
>
> 8 loop
>
> 9 dbms_output.put_line('OBJ ----- ' || x.object_type|| ' '|| x.owner || '.'
>
> || x.object_name);
>
> 10 end loop;
>
> 11
>
> 12 dbms_output.put_line('Looking for purge_object=1508751 in
>
> dba_recyclebin');
>
> 13 for x in (select owner, type, original_name from dba_recyclebin
>
> 14 where purge_object= 1508751)
>
> 15 loop
>
> 16 dbms_output.put_line('BIN ----- ' || x.type|| ' ' || x.owner || '.' ||
>
> x.original_name);
>
> 17 end loop;
>
> 18 end;
>
> 19 /
>
>
>
> Déclencheur créé.
>
>
>
> SQL> drop table popo;
>
> Looking for object_id=1508751 in dba_objects
>
> Looking for purge_object=1508751 in dba_recyclebin
>
>
>
> Table supprimée.
>
>
>
> SQL>
>
>
>
> The messages in the FOR loops don't get printed as the object ID is in none
>
> of the views.. Would I run the trigger code in an anonymous block *after*
>
> the DROP, it would find it in DBA_RECYCLEBIN.
>
> Certainly Oracle has not lost trace of this object at this point (when the
>
> trigger runs) , it must be somewhere still in tha data dictionary !? Where
>
> can I find it ?
>
>
>
> This is part of a trigger that audits table DROPs by developers.
>
>
>
> Thank you !
>
> Syltrem

Does it work outside of the trigger? I'm wondering if x is implicitly a numeric counter.

I can't remember enough PL to say without seeming silly.

jg

-- 
_at_home.com is bogus.
http://www.pcworld.com/article/2051740/oracle-scoffs-at-charge-that-ellison-is-overpaid.html