How to... compile a database schema

>

The exception we get when using the ‘call’ statement and executing this locally:

...

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');      

Is there something i do wrong?


grts





Hi,

I was wondering if you ever got an answer on this?

Have you tried …

 

    1. ll dbms_utility.compile_schema(‘SCHEMA_NAME’)


               
        BEGIN COMPILE_INVALIDS; END;
   
  

Bonus track : The procedure Oracle for compile_invalids

  1. 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;

    END Compile_Invalids;
    /