Criar Instrução SQL para gerar SPOOL

[Leonardo LitzParticipante]

Marlontk

Tenta assim:

declare

v_query varchar2(400);
v_coluna varchar2(4000);
v_tabela varchar2(30) := ‘ITENS_NOTA’;
v_user varchar2(30) := user;
v_linha dbms_sql.Varchar2_Table;
c number;

begin

c := 0;

for dd in (select a.column_name
from all_tab_cols a
where a.table_name = v_tabela
and a.owner = v_user) loop

c := c +1;
v_linha(c) := ‘,’||dd.column_name;

end loop;

if(v_linha.count > 0)then
v_linha(1) := substr(v_linha(1),2);

dbms_output.put_line('select '); 
for dd in 1..v_linha.count loop
dbms_output.put_line(v_linha(dd));
end loop;
dbms_output.put_line('from '||v_tabela); 

end if;

end;

sobre o disseram do tamanho do buffer o dbms_output, vc aumenta ele antes.

Vlw Leonardo Litz