Menu de navegação

[Rodrigo IhitzParticipante]

Olá Especialistas!

Trabalho com uma ferramenta de planejamento estratégico (SSM) que utiliza um banco Oracle e possui aproximadamente 830 tabelas. No SSM adicionei um atributo customizado e não consigo localizar em qual tabela essa informação foi inserida, então, estou tentando criar um script para localizar esse atributo no banco de dados, porém, como meu conhecimento é minimo gostaria de saber se vocês poderiam me ajudar.

A minha ideia seria criar um LOOP que executariam varios selects buscando em todas as tabelas e campos do banco de dados a informação que eu preciso. No caso, o script que estou fazendo está da seguinte maneira:

select ‘SELECT * from ‘ || table_name || ‘ where ‘ || column_name || ‘=”ATRIBUTO CUSTOMIZADO” ‘ from all_tab_cols
where owner = ‘XXXYYY’
and data_type like ‘%CHAR%’;

O que falta nesse script seria o FOR LOOP, que com meu pouco conhecimento no assunto, não estou conseguindo desenvolver.

Alguém poderia me ajudar?

Obrigado,

rihitz

[spernegaParticipante]

Boa tarde Rodrigo,

Eu tenho uma PROCEDURE que faz o que você está precisando, em anexo.

Veja se ajuda

[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;
/

[José Laurindo ChiappaModerador]

Bom, ao que entendi uma vez descoberto em qual tabela a tal coluna foi inserida vc já será Plenamente Capaz de alterar os seus SQLs para incluir essa nova coluna, SEM necessidade de construir um complexo SQL dinãmico com isso, né ?
Muito bem, se for isso mesmo e vc só precisa saber em qual tabela foi criada a nova coluna cujo nome vc já sabe, primeiro ** OBSERVO ** que TODA ferramenta que faz SQLs/alterações no banco à revelia se for minimamente decente tem algum tipo de LOG, de Registro, de Auditoria, enfim, algum modo de INDICAR o que ela fez, veja lá se é esse o caso….
Se não quiser/puder/conseguir obter a informação pela ferramenta, pelo lado do RDBMS Oracle o que ocorre é : sempre que alguma tabela é alterada de qualquer forma na sua estrutura, isso fica registrado no dicionário de dados, não só na DBA/ALL/USER_TAB_COLUMNS, mas alguma coisa (como a DATA DA ÚLTIMA ALTERAÇÂO) também na DBA/ALL/USER_OBJECTS, tipo :

==> alguém fez DDL alterando a estrutura da tabela :

SQL> alter table DEPT add COL_TESTE char(1);

Tabela alterada.

Assim, se teu objetivo é apenas saber o nome da tabela que foi alterada tendo uma nova coluna com um nome tal e datatype CHAR, NENHUMA necessidade de SQL dinâmico de NENHUMA forma aqui, é simplesmente fazer um :

SELECT OWNER, TABLE_NAME, COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE COLUMN_NAME=’NOMEDACOLUNAEMMAIUSCULAS’ AND DATA_TYPE=’CHAR’;

e pequenas alterações podem ser feitas, por exemplo se vc não sabe o nome exato ou o datatype da coluna talvez possa usar LIKE ou IN numa lista dos possíveis…. Por exemplo, digamos que sei que a coluna é qquer coisa com TEST no meio E que (obviamente) não foi criada no usuário interno do RDBMS, faria :

SQL> select owner, table_name from dba_tab_columns where column_name like ‘%TEST%’ and owner’SYS’;

SQL> select owner, table_name, column_name from dba_tab_columns where column_name like ‘%TEST%’ and owner’SYS’;

OWNER TABLE_NAME COLUMN_NAME

---

APEX_040000 WWV_FLOW_FEATURES TESTING_IMPACT
APEX_040000 WWV_FLOW_FEATURES TESTING_ASSIGNEE
APEX_040000 WWV_FLOW_FEATURES TESTING_STATUS
APEX_040000 WWV_FLOW_TASK_DEFAULTS ENABLE_FEATURE_TEST_YN
APEX_040000 APEX_TEAM_FEATURES TESTING_ASSIGNEE
SCOTT DEPT COL_TESTE
APEX_040000 APEX_TEAM_FEATURES TESTING_STATUS
APEX_040000 APEX_TEAM_FEATURES TESTING_IMPACT

8 linhas selecionadas.

SQL>

Já se vc ABSOLUTAMENTE NÃO SABE o nome da coluna física na tabela (Não É incomum que ferramentas que deixam vc alterar o modelo on-the-fly e se interpõem entre vc e o banco – parece ser o caso da sua, assim criando no banco colunas com nomes os mais absurdos possíveis, tipo COLUMN1234, e internamente depois façam um de-para, tipo , se o usuário consultar o atributo tal no aplicativo, ele tem que ser fisicamente buscado na coluna COLUMN1234), a coisa muda de figura….
O que vc pode fazer nesse é tipo listar as N tabelas mais recentemente alteradas, tipo :

=> vou listar as (digamos) 10 últimas tabelas alteradas (excluindo as do SYS, que são internas e particulares do RDBMS) :

SQL> select * from (select owner, object_name, last_ddl_time from dba_objects where owner ‘SYS’ and object_type=’TABLE’ order by last_ddl_time desc)
2* where rownum

okdoc ??? Vc só precisaria de SQL dinâmico se, ** ALÉM ** de listar cada tabela que foi alterada, vc quisesse fazer um SELECT automaticamente pra cada uma, aí sim vc escreveria um LOOP parecendo com esse que vc esboçou montanbdo um texto com o SELECT numa variável e executaria esse comando montado via DBMS_SQL ou EXECUTE IMMEDIATE….
Ou ainda vc poderia dinamicamente gerar um arquivo com os SQLs assim construídos e executar esse arquivo no sqlplus…
Em qquer caso é um pouco complexo mas dá pra fazer se isso for a sua necessidade…

[]s

Chiappa

[Autor]

Posts