Oracle AFTER/BEFORE CREATE ON DATABASE 触发器：如何访问对象源

发布于 2024-11-28 15:32:34 字数 441 浏览 0 评论 0原文

我正在尝试为 Oracle 实现伪版本控制和存储库。这个想法是：当存储的函数/过程被更改或创建的全局数据库时触发触发器，这将获取已修改对象的当前源并存储它们到一张桌子。所以我使用两个触发器：

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

一切正常，除了 AFTER_MODIFY 触发器看到编译对象的旧版本（源）。我尝试从 SYS.SOURCE$.SOURCE 获取源，并再次尝试从 dbms_metadata.get_ddl(OBJ_TYPE, OBJ_NAME, OBJ_OWNER) 获取相同的结果。

我正在寻找建议或明确的答案，例如“你不能在 Oracle 10g+ 中做到这一点”。

原文

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+".

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

傲世九天 2024-12-05 15:32:34

建议您只使用 ora_sql_txt 来获取新代码：

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;
/

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;
/

回复收藏 0 原文

~没有更多了~