Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Rui.

Asked: April 09, 2002 - 6:01 pm UTC

Last updated: June 04, 2004 - 8:20 am UTC

Version: 8.1.6

Viewed 1000+ times

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

Comments

UTL_FILE don't write all text

abeginner, April 10, 2002 - 1:56 pm UTC


Problem in compare the bind variable

Rui Brito, May 09, 2002 - 11:34 am UTC

Thanks Tom
Only today I have time to test your sugestion and I get error
"ORA-06502 numeric or value error: character to number conversion error"
I change your line to dbms_sql.bind_variable( l_cursor, ':x', ''''||cc1.trigger_name||'''') and works, only don´t give me the trigger body. How can I compare a string, because the trigger_name is a string inside a statement using bind variable ?
I need to define the bind variable ?

Best
Rui Brito

Tom Kyte
May 09, 2002 - 7:31 pm UTC

show me -- use MY code as is. show me the line that "blows up". run the block in sqlplus with SET ECHO ON-- show the exact error



You are the best !!!

Rui Brito, May 09, 2002 - 2:24 pm UTC

Sorry Tom
Your code is correct and work´s wonderful!
I make a mistake copying your code and ...

Sorry again
Rui Brito

OK

Ram, June 04, 2004 - 5:22 am UTC

Dear Tom,
If I have an OS file with contents as

"How now brown cow
Barbers who shave themselves
The Cat sat over the Mat
Hello World"
I would like to read the file backwards.I mean "starting from
the last line and proceeding upwards".How to do that?Could you
please help?

Please do reply.
Bye!



Tom Kyte
June 04, 2004 - 8:20 am UTC

you'd find that very hard in any programming language.

you would basically have to do it yourself, reading a character at a time using fseek (available in 9i) and looking for newlines.


I would be tempted to either

o read it from top to bottom, putting lines into a plsql table, then process the table from the bottom up

o if too big for ram -- reading top to bottom and putting into a global temporary table with a "sequence". then selecting it back out order by seq desc.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library