Statement FOR LOOP

[spernegaParticipante]

O anexo não foi, segue o script…

exec sp_verifica_campo(‘CD_CLIE_MATR’,’203601′)

create or replace
Procedure sp_verifica_Campo (P_Nm_Camp In Varchar2, P_vl_Camp in varchar2) Is
—
type tp_campo is REF CURSOR;
c_campo tp_Campo;
vSQL_campo Varchar2(8000);
v_Table_name Varchar2(30);
v_column_name Varchar2(30);
v_conta_tabelas number := 0;
—
type tp_comm is REF CURSOR;
c_comm tp_comm;
vSQL_comm Varchar2(8000);
v_ds_comm Varchar2(2000);
—
type tp_tabela is REF CURSOR;
c_tabela tp_tabela;
vSQL_tabela Varchar2(8000);
v_qt_regi number(6) := 0;
—
v_dt_inic date := sysdate;
v_tempo date;
—
Begin
—
dbms_output.put_line (rpad(‘Tabela’,30,’ ‘)||’ ‘||
rpad(‘Coluna’,30,’ ‘)||’ ‘||
‘ ‘||rpad(‘Registros’,12,’ ‘)||’ ‘||
‘Comentários’);
—
dbms_output.put_line (rpad(‘-‘,30,’-‘)||’ ‘||
rpad(‘-‘,30,’-‘)||’ ‘||
‘ ‘||rpad(‘-‘,12,’-‘)||’ ‘||
‘———–‘);
—
vSQL_campo := ‘Select col.Table_name , col.column_name From User_Tab_Columns col, user_objects obj ‘||
‘ Where obj.object_type = ‘||””||’TABLE’||””||
‘ And col.table_name = obj.object_name ‘||
‘ And col.column_name like ‘||””||’%’||upper(p_nm_camp)||’%’||””;
—
open c_campo for vSQL_campo;
loop
fetch c_campo into v_Table_name, v_column_name;
exit when c_campo%notfound;
—
v_conta_tabelas := nvl(v_conta_tabelas,0) + 1;
v_qt_regi := 0;
vSQL_tabela := ‘Select Count(1) From ‘||v_Table_name||’ Where ‘||v_column_name||’ = ‘||””||p_vl_camp||””;
—
open c_tabela for vSQL_tabela;
fetch c_tabela into v_qt_regi;
—
vSQL_comm := ‘select t.COMMENTS’||’||’||””||’.’||””||’||’||’c.COMMENTS’||
‘ from user_tab_comments t, user_col_comments c’||
‘ where c.column_name = ‘||””||v_column_name||””||
‘ and t.table_name = c.table_name ‘||
‘ and t.table_name = ‘||””||v_Table_name||””;
—
v_ds_comm := null;
open c_comm for vSQL_comm;
fetch c_comm into v_ds_comm;
close c_comm;
—
close c_tabela;
—
if nvl(v_qt_regi,0) > 0 then
dbms_output.put_line (substr(rpad(v_Table_name,30,’ ‘)||’ ‘||
rpad(V_column_name,30,’ ‘)||
‘ = ‘||rpad(nvl(v_Qt_regi,0),12,’ ‘)||’ ‘||
v_ds_comm,1,255));
end if;
—
end loop;
close c_campo;
—
v_tempo := (To_Date(’00:00′,’Hh24:Mi’) + ( sysdate – v_Dt_Inic));
Dbms_Output.Put_Line(‘.’);
Dbms_Output.Put_Line(‘tempo de execução… ‘||to_char((v_tempo),’HH24:MI:SS’));
Dbms_Output.Put_Line(‘Valor pesquisado…. ‘||P_vl_Camp);
Dbms_Output.Put_Line(‘Tabelas pesquisadas. ‘||v_conta_tabelas);
—
Exception
When Others Then
Commit;
Dbms_Output.Put_Line(‘Erro ‘||Sqlerrm(Sqlcode));
Dbms_Output.Put_Line(vSQL_tabela);
End sp_verifica_Campo;
/