Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Use of "DBA_OBJECTS" in "AFTER CREATE" Trigger

dullaertdSep 23 2010 — edited Sep 23 2010
Hi,

We would like to store some extra information about our objects we have in the database. To do so we tought of the idea of creating a 'documenting' table containing an object_id that references to the object_id from the view dba_views.

Now we want to automatically create a new record in our documenting table when a new object is created, in the first stage this should only contain the object_id
To accomplish this we are using an 'AFTER CREATE' trigger, but when this trigger fires and the code searches for the new object_id in dba_objects, it does not return any records and generates an error. Likely because when the trigger is executed, the view is not yet updated?

create or replace
TRIGGER TRG_TEST
AFTER CREATE ON SCOTT.SCHEMA
DECLARE
tmp VARCHAR2(50);
BEGIN
dbms_output.put_line(ora_dict_obj_name);

select object_id
into tmp
from dba_objects
where object_name = ora_dict_obj_name;

dbms_output.put_line(tmp);
END;

Error report:
ORA-04088: Fout bij uitvoering van trigger 'SCOTT.TRG_TEST'.
ORA-01403: Geen gegevens gevonden.
ORA-06512: in regel 6
04088. 00000 - "error during execution of trigger '%s.%s'"
*Cause: A runtime error occurred during execution of a trigger.
*Action: Check the triggers which were involved in the operation.

It's in dutch, so I'll have a go at translating:

Error report:
ORA-04088: Exception while executing trigger 'SCOTT.TRG_TEST'.
ORA-01403: No data found
ORA-06512: in rule 6
04088. 00000 - "error during execution of trigger '%s.%s'"
*Cause: A runtime error occurred during execution of a trigger.
*Action: Check the triggers which were involved in the operation.

Does anyone have an idea of how I can accomplish what I'm trying to do here.. Or maybe something I'm doing wrong?

Thanks in advance!
Davy
This post has been answered by Peter Gjelstrup on Sep 23 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 21 2010
Added on Sep 23 2010
5 comments
2,592 views