Estou tendo algumas dúvidas sobre o uso do Package DBMS_SQL para query dinamica, então desenvolvi uma rotina um pouco mais engessada com o Execute Immediate.
Por favor se alguém tiver um exemplo pacana com o uso do DBMS_SQL, ou alguma dica a respeito desse meu procedimento.
Este procedimento le uma tabela de tabelas do meu schema, depois le as colunas dessa tabela e identifica quais delas são PK fazendo Join com uma tabela minha de constraint, depois monta uma query dinamica e aonde está comentado ele deve gravar o nome da tabela e a query que foi gerada no loop mais interno.
CREATE OR REPLACE PROCEDURE QA_KEYRUS.PRC_VERIFICA_DUPLICIDADE_2
IS
V_ID_TABELA NUMBER;
V_NOME_TABELA VARCHAR2(50);
V_COUNT_TAB NUMBER := 1;
V_AUX_COUNT_TAB NUMBER;
V_ID_COLUNA NUMBER;
V_NOME_COLUNA VARCHAR2(50);
V_COUNT_COL NUMBER := 0;
V_AUX_COUNT_COL_MIN NUMBER := 0;
V_AUX_COUNT_COL_MAX NUMBER := 0;
V_TOTAL_LINHA_DUP NUMBER := 0;
V_QUERY VARCHAR2(20000) := 'SELECT ';
V_QUER2 VARCHAR2(20000);
BEGIN
SELECT MAX(ID_TABELA) AS ID_TABELA INTO V_AUX_COUNT_TAB FROM QA_KEYRUS.TABELAS;
LOOP
SELECT ID_TABELA, NOME_TABELA INTO V_ID_TABELA, V_NOME_TABELA
FROM QA_KEYRUS.TABELAS
WHERE ID_TABELA = V_COUNT_TAB;
LOOP
SELECT MIN(ID_COLUNA) AS ID_COLUNA INTO V_AUX_COUNT_COL_MIN FROM QA_KEYRUS.COLUNAS WHERE ID_TABELA = V_ID_TABELA;
SELECT MAX(ID_COLUNA) AS ID_COLUNA INTO V_AUX_COUNT_COL_MAX FROM QA_KEYRUS.COLUNAS WHERE ID_TABELA = V_ID_TABELA;
V_AUX_COUNT_COL_MIN := V_AUX_COUNT_COL_MIN + V_COUNT_COL;
SELECT A.ID_COLUNA, A.NOME_COLUNA INTO V_ID_COLUNA, V_NOME_COLUNA
FROM QA_KEYRUS.COLUNAS A
INNER JOIN
QA_KEYRUS.CONSTRAINTS B
ON A.ID_COLUNA = B.ID_COLUNA
WHERE A.ID_TABELA = V_ID_TABELA
AND A.ID_COLUNA = V_AUX_COUNT_COL_MIN
AND B.INDICADOR_CONSTRAINT = 'PK';
V_QUERY := V_QUERY || V_NOME_COLUNA || ',';
V_QUERY_2 := V_QUERY_2 || V_NOME_COLUNA ||',';
V_COUNT_COL := V_COUNT_COL +1;
EXIT WHEN V_COUNT_COL > V_AUX_COUNT_COL_MAX
END LOOP;
V_QUERY := V_QUERY || ' COUNT(*) FROM QA_KEYRUS.' || V_NOME_TABELA || ' GROUP BY ' || V_QUERY_2 || ' HAVING COUNT(*) > 1;';
EXECUTE IMMEDIATE V_QUERY;
/* AQUI VAI O INSERT COM A STRING QUERY, A TABELA QUE ESTA´SENDO TESTADA.*/
V_COUNT_TAB := V_COUNT_TAB +1;
EXIT WHEN V_COUNT_TAB > V_AUX_COUNT_TAB;
END LOOP;
END;