ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 从Oracle数据库中批量抓取Trigger脚本的方法

从Oracle数据库中批量抓取Trigger脚本的方法

原创 Linux操作系统 作者:ljm0211 时间:2012-06-13 23:46:39 0 删除 编辑

create directory tmp_dir as '/tmp';
grant read,write on directory tmp_dir to dbmgr;

declare
trg_sql clob;
sql_str varchar2(400);
trg_owner varchar2(30);
trg_name varchar2(50);
file_handle utl_file.file_type;
cursor cur_sql is
select distinct c.trigger_owner,c.trigger_name from dba_trigger_cols c,dba_triggers d where c.column_name in ('FCD','FCU')
and c.table_owner=d.table_owner and c.table_name=d.table_name and c.trigger_owner=d.owner and c.trigger_name=d.trigger_name
and d.triggering_event like '%INSERT%'
and c.table_owner in ('GBSMAN','ACTMAN','VOUDATA')
and c.table_owner||'.'||c.table_name in (
'GBSMAN.CERT_BANK_COL_HIS',
'GBSMAN.BUSINESS_ACTIVITY_INVOICE',
'GBSMAN.EMPLOYEE_WELFARE_POLICY_LIST',
…………

…………

…………
'GBSMAN.CLAIM_SMS'
);
begin
file_handle := utl_file.fopen('TMP_DIR','triggers.sql','a');
open cur_sql;

loop
fetch cur_sql into trg_owner,trg_name;
exit  when cur_sql%notfound;
select dbms_metadata.get_ddl('TRIGGER',trg_name,trg_owner) into trg_sql from dual;
--sql_str:='select dbms_metadata.get_ddl(''TRIGGER'','''||trg_name||''','''||trg_owner||''') into trg_sql from dual';
--dbms_output.put_line(dbms_lob.substr(tri_sql,4000));
--dbms_output.put_line(trg_sql);
IF utl_file.is_open(file_handle) THEN
  utl_file.put_line(file_handle,trg_sql);
end IF;
end loop;
utl_file.fclose(file_handle);

EXCEPTION
            WHEN OTHERS THEN
       begin
            IF utl_file.is_open(file_handle) THEN
               utl_file.fclose(file_handle);
            end IF;
            EXCEPTION
               WHEN OTHERS THEN
               NULL;
      end;
end;

类别:Oracle 查看评论

Link URL: http://hi.baidu.com/ljm0211/blog/item/538e342a7ca9e5185243c191.html

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11411056/viewspace-732748/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-14

  • 博文量
    272
  • 访问量
    436817