I found a web page which lists all the possibilities to (re)compile database packages (http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php). I tried them all but noticed that the the ones involving PL/SQL always result into a build error. For the moment the manual approach using simple SQL seems to work. ex:
ALTER PACKAGE my_package COMPILE; ALTER PACKAGE my_package COMPILE BODY;
This is an example of an PL/SQL i tried:
SELECT COUNT(*) FROM
dba_objects WHERE status = 'INVALID' AND owner='NOVAONE' AND object_type='PACKAGE BODY' ORDER BY owner, object_type, object_name; Recompile all packages manually using PL/SQL. EXEC DBMS_DDL.alter_compile('PACKAGE', 'NOVAONE', 'PCK_IBGE_CLEAN_DATA'); EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'NOVAONE', 'PCK_IBGE_CLEAN_DATA');
Bonus track : The procedure Oracle for compile_invalids
CREATE OR REPLACE PROCEDURE Compile_Invalids(v_object_type IN VARCHAR2 DEFAULT NULL, v_object_name IN VARCHAR2 DEFAULT NULL) AS – Exception - Error compilation erreurCompilation_exception EXCEPTION; erreurCompilation_ex_msg CONSTANT VARCHAR2(250) := ‘ORA-24344: success with compilation error’; PRAGMA EXCEPTION_INIT(erreurCompilation_exception, -24344); top_error BOOLEAN := FALSE;
PROCEDURE compile_invalid_object(the_object_name IN VARCHAR2, the_object_type IN VARCHAR2) IS BEGIN IF the_object_type=‘TYPE BODY’ THEN EXECUTE IMMEDIATE ‘alter type ‘||the_object_name||’ compile body’; ELSIF the_object_type=‘PACKAGE BODY’ THEN EXECUTE IMMEDIATE ‘alter package ‘||the_object_name||’ compile body’; ELSE EXECUTE IMMEDIATE ‘alter ‘||the_object_type||’ ‘||the_object_name||’ compile’; END IF; EXCEPTION WHEN erreurCompilation_exception THEN top_error := TRUE; WHEN OTHERS THEN NULL; END; BEGIN
IF (v_object_name IS NOT NULL AND v_object_type IS NOT NULL) THEN compile_invalid_object(the_object_name => UPPER(v_object_name), the_object_type => UPPER(v_object_type)); RETURN; END IF;
IF v_object_name IS NULL AND v_object_type IS NOT NULL THEN FOR rec IN ( SELECT object_name, object_type FROM user_objects WHERE 1=1 AND status=‘INVALID’ AND object_type = UPPER(v_object_type) AND object_type IN (‘FUNCTION’,‘PACKAGE’,‘PACKAGE BODY’,‘PROCEDURE’,‘TRIGGER’,‘TYPE’,‘VIEW’,‘TYPE BODY’)) LOOP compile_invalid_object(the_object_name => rec.object_name, the_object_type => UPPER(v_object_type)); END LOOP; RETURN; END IF;
IF v_object_name IS NOT NULL AND v_object_type IS NULL THEN
FOR rec IN ( SELECT object_name, object_type FROM user_objects WHERE 1=1 AND status=‘INVALID’ AND object_name = UPPER(v_object_name) ) LOOP compile_invalid_object(the_object_name => rec.object_name, the_object_type => rec.object_type); END LOOP; RETURN; END IF;
– v_object_type is null and v_object_n,name is null FOR rec IN (SELECT object_name, object_type FROM user_objects WHERE status=‘INVALID’ AND object_type IN (‘FUNCTION’,‘PACKAGE’,‘PACKAGE BODY’,‘PROCEDURE’,‘TRIGGER’,‘TYPE’,‘VIEW’,‘TYPE BODY’)) LOOP compile_invalid_object(the_object_name => rec.object_name, the_object_type => rec.object_type); END LOOP;
– if you want a error when compilation is not ok IF top_error THEN raise_application_error(-20999,erreurCompilation_ex_msg,FALSE); END IF;