Forum Stats

  • 3,874,556 Users
  • 2,266,758 Discussions
  • 7,911,898 Comments

Discussions

Use of "DBA_OBJECTS" in "AFTER CREATE" Trigger

dullaertd
dullaertd Member Posts: 7
edited Sep 23, 2010 7:36AM in SQL & PL/SQL
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
Tagged:

Best Answer

Answers

  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
    Answer ✓
    Hi,

    Instead of doing the select in your trigger, you could submit a job, that does it for you. This job would not run until your table is created.

    Regards
    Peter
  • bluefrog
    bluefrog Member Posts: 1,512
    What is "ora_dict_obj_name" defined as?


    Another option might be to setup a DBMS_SCHEDULER job to run the following insert

    firstly though, create the following table ;
    create document_table as (select * from dba_objects where rownum < 1)
    and then setup a DBMS_SCHEDULER job to run the following:
    begin
    
      insert into document_table
      (select dbo.*
       from   dba_objects    dbo
             ,document_Table dtb
       where  dbo.Object_ID = dtb.Object_ID(+)
       and    dtb.Object_ID is null);
    
       commit;
    
       exception
        when others then
          rollback;
          -- log a message somewhere with the error, i.e. SQLERRM
    end;
    /
    Note, I specified all columns, but you'll probably specify explicitly which column you require, in which case the CREATE table would change as well.
    Then you won't have a need for any trigger, and can better manage when you want your documentation to be updated.


    Another option might be to use COMMENT, which can be used on tables, views, materialized views, but won't cover all objects
    to the extent you might want.
    SQL> desc emp2
     Name                                                                                                   
     -------------------------------------------------------------------------------------------------
     EMPNO                                                                                                  
     JOB                                                                                                    
     START_DATE                                                                                             
     SAL                                                                                                    
     DEPT                                                                                                   
     END_DATE                                                                                               
    
    SQL> comment on column emp2.sal is 'Existing Salary of employee as paid at most recent month end';
    bluefrog
  • Herald ten Dam
    Herald ten Dam Member Posts: 1,254
    Hi,

    I tried just your example in a 10.2 database and it is working for a table. I get the id from the select. So what is exactly the create you are using? I tried simple:
     
    create table xyz as select 1 coll from dual;
    But If I try a view it gives your error. So I think there is a delay for the view to get in the DBA_OBJECTS. Otherwise go for the solution posted by others by defining a job.

    Herald ten Dam
    http://htendam.wordpress.com
    Herald ten Dam
  • dullaertd
    dullaertd Member Posts: 7
    *@Peter Gjelstrup*
    Thanks for your advice! I did indeed solve the probleem by creating a new job. (see below)

    *@bluefrog*
    "ora_dict_obj_name" is variable filled in by the database itself within the trigger and states the name of the object you have just created.

    I used dbms_job to schedule a procedure which will fill my doc table with the variable that i fill in at the trigger level. Because not every object of the database needs to be documented just the functions and procedures..

    The query you have written will none the less be very helpfull to check wether all functions and procedures have a record in the documenting table! So many thanks for that.

    *@Herald ten Dam*
    As you said, when creating a table the function does indeed work! Did not notice that.. But while we only want to have the function and procedures, my trigger was useless.. Also to you thanks for the remark!


    So the job I submitted is as follows:
    dbms_job.submit(
            job => ln_job, 
            what => 'p_add_doc(''' || ora_dict_obj_name || ''', ''' || lc_osuser || ''');', 
            next_date => trunc(sysdate+1/86400), 
            interval => null);
    And the procedure that it calls, looks like this:
    create or replace procedure p_add_doc(an_obj_name VARCHAR2, an_osuser VARCHAR2) as
    ln_obj_id dba_objects.object_id%TYPE;
    begin
      select object_id
      into ln_obj_id
      from dba_objects
      where object_name = an_obj_name;
      
      insert into doc_table (OBJ_ID, OBJ_CREATOR)
      values (ln_obj_id, an_osuser);
      
    end;
    /
  • bluefrog
    bluefrog Member Posts: 1,512
    You could modify the INSERT statement in the job as;
    insert into document_table
    (select dbo.*
     from   dba_objects    dbo
           ,document_Table dtb
     where  dbo.Object_ID = dtb.Object_ID(+)
     and    dbo.Object_Type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER', 'TYPE', 'TYPE BODY')
     and    dtb.Object_ID is null);
    which would cover all your programming objects and therefore you need not use the trigger (-:
This discussion has been closed.