Oracle AFTER/BEFORE CREATE ON DATABASE trigger: how to access object sources

Question

I am trying to implement pseudo version control and repository for Oracle. The idea is: when a stored function/procedure is altered or a created global DATABASE trigger is fired, which would grab the current sources of a modified object and store them to a table. So I'm using two triggers:

TRIGGER BEFORE_MODIFY before ALTER or CREATE ON DATABASE
TRIGGER AFTER_MODIFY after ALTER or CREATE ON DATABASE

Everything works fine, except the AFTER_MODIFY trigger sees an old version (sources) of the compiled object. I have tried to get sources from SYS.SOURCE$.SOURCE and as a second try from dbms_metadata.get_ddl(OBJ_TYPE, OBJ_NAME, OBJ_OWNER) with same results.

I am looking for advice or a definite answer such as "You can't do it in Oracle 10g+".

Answer 1

Suggest you just use ora_sql_txt to get the new code:

create or replace TRIGGER AFTER_MODIFY after CREATE ON hr.SCHEMA 
declare
  sql_text ora_name_list_t;
  n number;
begin
  n := ora_sql_txt(sql_text);
  FOR i IN 1..n LOOP
   dbms_output.put_line(sql_text(i));
  END LOOP;
end;
/