oracle批量创建trigger,从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