No meu caso específico eu descobri que este problema ocorreu pois o time de "análise" utiliza uma ferramenta de UML (Enterprise Architect) para modelar um banco relacional quando deveria estar utilizando uma ferramenta de ER. A ferramenta definiu as colunas como não nulas, mas também criou check constraints para a forçar a restrição. Acontece que o Oracle (ao menos até a versão 10.2) não valida se constraints do tipo "check" possuem as mesmas clausulas de restrição.
Enfim vamos ao que interessa...
Para solucionar o problema escrevi o código de uma procedure que efetua a remoção das 2, ou mais, constraints do tipo "not null" na mesma coluna e por fim altera a tabela para redefinir a coluna, esta redefinição precisa ser feita pois com a remoção das contraints a coluna passaria a ser "nullable", por isto não basta só remover as constraints.
Antes da procedure de remoção das duplicações em si, é necessário também criar a procedure abaixo, para converter a coluna "search_condition" da view "all_constraints" de LONG para VARCHAR2, para que seja possível efetuar as comparações dos strings.
CREATE OR REPLACE FUNCTION f_get_cons_check( p_owner VARCHAR2,p_constraint VARCHAR2) RETURN VARCHAR2 AS l_cursor INTEGER DEFAULT dbms_sql.open_cursor; l_n NUMBER; l_long_val VARCHAR2(4000); l_long_len NUMBER; l_buflen NUMBER := 4000; l_curpos NUMBER := 0; BEGIN dbms_sql.parse( l_cursor, 'SELECT search_condition FROM all_constraints WHERE owner = :x AND constraint_name = :y', dbms_sql.native ); dbms_sql.bind_variable( l_cursor, ':x', p_owner ); dbms_sql.bind_variable( l_cursor, ':y', p_constraint ); dbms_sql.define_column_long(l_cursor, 1); l_n := dbms_sql.execute(l_cursor); IF (dbms_sql.fetch_rows(l_cursor)>0) THEN dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos , l_long_val, l_long_len ); END IF; dbms_sql.close_cursor(l_cursor); RETURN l_long_val; END f_get_cons_check;
Esta função é baseada no código do Thomas Kyte publicada na issue "Long to Varchar2 conversion" do "Ask Tom"
E então temos a procedure para remoção das constraints duplicadas. Observe que estou apenas imprimindo os comandos de DML para remoção das constraints e alteração da tabela, mas nada impede que se você se sentir seguro altere estas chamadas para um "execute immediate".
PROCEDURE p_remove_dup_notnull_cons(p_schema VARCHAR2) AS TYPE dup_constraints_type IS RECORD ( owner all_constraints.owner%TYPE, table_name all_constraints.table_name%TYPE, column_name all_cons_columns.column_name%TYPE, constraint_name all_constraints.constraint_name%TYPE, cnt_check NUMBER, ord NUMBER); TYPE ref_cursor IS REF CURSOR RETURN dup_constraints_type; c_dup_constraints ref_cursor; TYPE t_dup_constraints IS TABLE OF dup_constraints_type INDEX BY PLS_INTEGER; l_dup_constraints t_dup_constraints; l_row PLS_INTEGER; v_cnt INTEGER; v_ddl_modify_coln VARCHAR2(4000); BEGIN OPEN c_dup_constraints FOR SELECT owner, table_name, column_name, constraint_name, cnt_check, ord FROM( SELECT owner, table_name, column_name, constraint_name, count(*) over (partition by owner, table_name, column_name) as cnt_check, row_number() over (partition by owner, table_name, column_name order by last_change) as ord FROM (SELECT owner, table_name, constraint_name, last_change, trim('"' FROM substr(cons_check,1,instr(cons_check,' ')-1)) as column_name FROM (SELECT owner, table_name, constraint_name, last_change, f_get_cons_check( owner, constraint_name) as cons_check FROM all_constraints WHERE owner = p_schema AND constraint_type IN ('C','?')) WHERE cons_check LIKE '%IS NOT NULL')) --WHERE cnt_check > 1 ORDER BY table_name, column_name, ord; LOOP FETCH c_dup_constraints BULK COLLECT INTO l_dup_constraints LIMIT 1000; DBMS_OUTPUT.PUT_LINE('-- ' || l_dup_constraints.COUNT || ' DUPLICATED CONSTRAINTS FOUND'); EXIT WHEN l_dup_constraints.COUNT = 0; l_row := l_dup_constraints.FIRST; WHILE l_row IS NOT NULL LOOP IF l_dup_constraints(l_row).ord = 1 THEN -- GERA DDL PARA REDEFINIR A COLUNA COMO "NOT NULL" APÓS A REMOÇÃO DAS CONSTRAINTS v_ddl_modify_coln := f_ddl_mod_tab_column(l_dup_constraints(l_row).owner, l_dup_constraints(l_row).table_name, l_dup_constraints(l_row).column_name, ' NOT NULL ENABLE'); END IF; -- DROPA CONSTRAINT DUPLICADA DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || l_dup_constraints(l_row).owner || '.' || l_dup_constraints(l_row).table_name || ' DROP CONSTRAINT ' || l_dup_constraints(l_row).constraint_name || ';'); IF l_dup_constraints(l_row).ord = l_dup_constraints(l_row).cnt_check THEN -- EXECUTA DDL MODIFY PARA REDEFINIR A COLUNA COMO "NOT NULL" DBMS_OUTPUT.PUT_LINE(v_ddl_modify_coln || ';'); END IF; l_row := l_dup_constraints.NEXT(l_row); END LOOP; END LOOP; IF c_dup_constraints%ISOPEN THEN CLOSE c_dup_constraints; END IF; EXCEPTION WHEN OTHERS THEN IF c_dup_constraints%ISOPEN THEN CLOSE c_dup_constraints; END IF; RAISE; END p_remove_dup_notnull_cons;
Sinta-se livre para utilizar e modificar estas rotinas como bem entender, por sua conta e risco. Mas se for publica-la por favor referencie minha autoria.
Nenhum comentário:
Postar um comentário