從Oracle資料庫中批量抓取Trigger指令碼的方法

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/，如需轉載，請註明出處，否則將追究法律責任。