You Asked
Hello again
I´m the office now, and review my question. There´s about Triggers !
Here is a extract of my code to write the trigger in a text file.
The final part of loop(r_posicao) is because the slash (/) put in the end of file stay with a left space, and because of that when I run the "my_trigger.trg" they don´t start. If you remove this part and can help me, I appreciate.
If I execute this script to write all triggers of a table, then have many code inside (like the generated by Table API of Oracle Designer), the code is incomplete, like I said yesterday.
set serverout on
set long 32000000
declare
cursor trig is select distinct table_name
from user_triggers
order by table_name;
cursor trig_code(tabela in user_triggers.table_name%type) is
select table_name, trigger_name, trigger_type, triggering_event, referencing_names
from user_triggers
where table_name = tabela
order by trigger_name;
l_cursor integer;
l_n number;
l_long_val varchar2(32767);
l_long_trab varchar2(32767);
l_long_len number;
l_curpos number;
l_query varchar2(2000);
r_nome_file varchar2(100);
r_nome_executar varchar2(100);
r_path varchar2(100) := 'c:\temp';
r_file utl_file.file_type;
r_sql utl_file.file_type;
r_wrap utl_file.file_type;
r_separador varchar2(1);
r_posicao number;
r_parte1 varchar2(50);
r_parte2 varchar2(50);
r_trigger_name user_triggers.trigger_name%type;
r_table_name user_triggers.table_name%type;
begin
r_sql := utl_file.fopen(r_path, 'runall.sql', 'w');
for cc1 in trig loop
r_table_name := cc1.table_name;
-- Compoe o Nome do File a criar
r_nome_file := cc1.table_name||'.TRG';
-- Cria o File para a Source do Objecto em tratamento
r_file := utl_file.fopen(r_path, r_nome_file, 'w');
if utl_file.is_open(r_file) then
-- Adiciona a View à Lista a executar
utl_file.put_line(r_sql, 'PROMPT Criar Triggers da Tabela '||cc1.table_name);
utl_file.put_line(r_sql, 'start '||r_path||'\'||r_nome_file);
utl_file.put_line(r_sql, '/');
-- Grava Triggers da Tabela
for cc2 in trig_code(cc1.table_name) loop
r_trigger_name := cc2.trigger_name;
r_posicao := instr(cc2.trigger_type, ' ');
r_parte1 := substr(cc2.trigger_type, 1, r_posicao);
r_parte2 := substr(cc2.trigger_type, r_posicao + 1);
if r_parte2 = 'STATEMENT' then -- Triggers de STATEMENT
r_parte2 := null;
elsif r_parte2 = 'EACH ROW' then -- Triggers de ROW
r_parte2 := 'FOR EACH ROW';
elsif r_parte2 = 'OF' then -- INSTEAD OF
r_parte1 := r_parte1||' OF ';
r_parte2 := null;
end if;
utl_file.new_line(r_file);
utl_file.put_line(r_file, 'create or replace trigger '||cc2.trigger_name);
utl_file.put_line(r_file, r_parte1||cc2.triggering_event||' ON '||cc2.table_name);
utl_file.put_line(r_file, cc2.referencing_names);
if r_parte2 is not null then
utl_file.put_line(r_file, r_parte2);
end if;
l_query := 'select trigger_body from user_triggers where trigger_name = '||''''||r_trigger_name||'''';
l_curpos := 0;
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor, l_query, dbms_sql.native);
dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);
if dbms_sql.fetch_rows(l_cursor) > 0 then
loop
dbms_sql.column_value_long(c => l_cursor,
position => 1,
length => 32767,
offset => l_curpos,
value => l_long_val,
value_length => l_long_len);
if l_long_len > 0 then
r_posicao := instr(l_long_val, ';', -1, 1);
if r_posicao > 0 then
l_curpos := l_curpos + r_posicao;
utl_file.put(r_file, substr(l_long_val, 1, r_posicao));
else
if rtrim(l_long_val) != '' then
utl_file.put(r_file, l_long_val);
else
utl_file.new_line(r_file);
end if;
l_curpos := l_curpos + l_long_len;
end if;
end if;
exit when l_long_len = 0;
end loop;
end if;
utl_file.put_line(r_file, '/');
dbms_sql.close_cursor(l_cursor);
end loop;
else
dbms_output.put_line('Erro ao criar file '||r_nome_file||' no disco');
end if;
-- Fecha o ficheiro da Source
utl_file.fclose(r_file);
end loop;
utl_file.fclose(r_sql);
end;
/
Thanks
Rui Brito
and Tom said...
Well, personally I find the concept of a trigger with 32k of code horrifying. I cannot IMAGINE having that much code in a single routine. I have a rule I follow -- the code in a routine must be visible from top to bottom on a single screen. If it is bigger then that, it is neither understandable nor maintainable. It needs to be broken up into tiny chunks in a package somewhere. So, with that in mind I hope you don't mind -- I made a couple of bug fixes and optimizations in the code (and made it so the code in the begin/end fits on a screen).
This'll work for you. It appears to be an issue with the linesize and such. In any case, this routine does it (and it uses BIND VARIABLES which are sooooo important, soooo important. never concatenate in the trigger name in a query like that, don't parse in a loop like that -- do it once and reuse it over and over and over)
declare
l_cursor integer := dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(32767);
l_long_len number;
l_curpos number;
r_path varchar2(100) := '/tmp/test/';
r_file utl_file.file_type;
r_sql utl_file.file_type;
l_last_table varchar2(30) := '*not a table*';
l_the_rest varchar2(2000);
begin
r_sql := utl_file.fopen(r_path, 'runall.sql', 'w');
dbms_sql.parse(l_cursor, 'select trigger_body
from user_triggers
where trigger_name = :x', dbms_sql.native);
dbms_sql.define_column_long(l_cursor, 1);
for cc1 in (select table_name, trigger_name, triggering_event,
referencing_names,
decode( substr( trigger_type, 1, 1 ),
'A', 'AFTER ', 'B', 'BEFORE ', 'I', 'INSTEAD OF ' )
real_trigger_type,
decode( instr( trigger_type, 'EACH ROW' ),
0, null, ' FOR EACH ROW' ) each_row
from user_triggers
order by table_name, trigger_name )
loop
if ( l_last_table <> cc1.table_name )
then
if ( utl_file.is_open(r_file) )
then
utl_file.fclose(r_file);
end if;
-- Cria o File para a Source do Objecto em tratamento
r_file := utl_file.fopen(r_path, cc1.table_name||'.TRG', 'w', 32765);
utl_file.put_line(r_sql, 'PROMPT Criar Triggers da Tabela '||
cc1.table_name);
utl_file.put_line(r_sql, 'start '||r_path||cc1.table_name||'.TRG');
l_last_table := cc1.table_name;
end if;
utl_file.new_line(r_file);
utl_file.put_line(r_file, 'create or replace trigger '||
cc1.trigger_name);
utl_file.put_line(r_file, cc1.real_trigger_type ||cc1.triggering_event||
' ON '||cc1.table_name);
utl_file.put_line(r_file, cc1.referencing_names || cc1.each_row);
dbms_sql.bind_variable( l_cursor, ':x', cc1.trigger_name );
l_curpos := 0;
l_n := dbms_sql.execute(l_cursor);
if dbms_sql.fetch_rows(l_cursor) > 0
then
loop
dbms_sql.column_value_long(c => l_cursor,
position => 1,
length => 30000,
offset => l_curpos,
value => l_long_val,
value_length => l_long_len);
l_long_val := l_the_rest || l_long_val;
loop
l_n := instr( l_long_val, chr(10) );
exit when nvl(l_n,0) = 0;
utl_file.put_line(r_file, substr(l_long_val,1,l_n-1) );
l_long_val := substr( l_long_val, l_n+1 );
end loop;
l_the_rest := l_long_val;
l_curpos := l_curpos + l_long_len;
exit when l_long_len = 0;
end loop;
end if;
utl_file.put_line(r_file, '/');
end loop;
if ( utl_file.is_open(r_file) )
then
utl_file.fclose(r_file);
end if;
utl_file.fclose(r_sql);
dbms_sql.close_cursor(l_cursor);
end;
/
Rating
(4 ratings)
Is this answer out of date? If it is, please let us know via a Comment