Archive for the ‘Dynamic SQL’ Category

Custom Script to Identify Invalid Packages

Identifying Invalid Objects

SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

Manual Approach

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

DBMS_DDL

EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);

EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’,’MY_SCHEMA’,’MY_PACKAGE’);

EXEC DBMS_DDL.alter_compile(‘PROCEDURE’,’MY_SCHEMA’,’MY_PROCEDURE’);

EXEC DBMS_DDL.alter_compile(‘FUNCTION’, ‘MY_SCHEMA’, ‘MY_FUNCTION’);

EXEC DBMS_DDL.alter_compile(‘TRIGGER’, ‘MY_SCHEMA’, ‘MY_TRIGGER’);

DBMS_UTILITY

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects.

 

  • Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
  • The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
  • The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and  DBMS_RANDOM to be present and valid.
  • — Schema level.

    EXEC UTL_RECOMP.recomp_serial(‘SCOTT’);

    EXEC UTL_RECOMP.recomp_parallel(4, ‘SCOTT’);

    — Database level.

    EXEC UTL_RECOMP.recomp_parallel(4);

    — Using job_queue_processes value.

    EXEC UTL_RECOMP.recomp_parallel();

    EXEC UTL_RECOMP.recomp_parallel(NULL, ‘SCOTT’);

     

    Custom Script for Compiling Invalid Packages in File

    CREATE OR REPLACE PROCEDURE COMPILE_PACKAGES

    IS

    outfile   UTL_FILE.file_type;

    stmt      VARCHAR2 (150);

    BEGIN

      outfile := UTL_FILE.fopen (‘PLSQL_FILE’, ‘INVALID_OBJECTS.txt’, ‘w’);

      FOR cur_rec IN (SELECT owner,

                             object_name,

                             object_type,

                             DECODE(object_type, ‘PACKAGE’, 1,

                                                 ‘PACKAGE BODY’, 2, 2) AS                    recompile_order

                      FROM   dba_objects

                      WHERE  object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)

                      AND    status != ‘VALID’

                      ORDER BY 4)

      LOOP

        BEGIN

          IF cur_rec.object_type = ‘PACKAGE’ THEN

            stmt := ‘ALTER ‘ || cur_rec.object_type || ‘ "’ || cur_rec.owner || ‘"."’ || cur_rec.object_name || ‘" COMPILE’;

            dbms_output.PUT_LINE ( ‘Package statement: ‘||stmt);

            –EXECUTE IMMEDIATE stmt;

          ElSE

            stmt:= ‘ALTER PACKAGE "’ || cur_rec.owner ||

                ‘"."’ || cur_rec.object_name || ‘" COMPILE BODY’;

            –EXECUTE IMMEDIATE stmt;

            dbms_output.PUT_LINE ( ‘Package statement: ‘||stmt);

          END IF;

          UTL_FILE.PUT_LINE(outfile, stmt);

        EXCEPTION

          WHEN OTHERS THEN

            DBMS_OUTPUT.put_line(cur_rec.object_type || ‘ : ‘ || cur_rec.owner ||

                                 ‘ : ‘ || cur_rec.object_name);

        END;

      END LOOP;

      UTL_FILE.FCLOSE(outfile);

    END COMPILE_PACKAGES;

    Main Source of this article: Oracle Base link on Recompiling Objects

    Advertisements