Edi Yanto (何 萬 新)

Operations such as upgrades, patches and DDL changes can invalidate schema objects.  For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base.

In order to compile a program, you must own that program (in other words, the schema you specify is the owner of the program for which you request compilation) or your schema must have been granted the ALTER ANY PROCEDURE privilege to compile another schema’s programs.

GRANT ALTER ANY PROCEDURE TO name_of_user;

DBMS_DDL.ALTER_COMPILE

CREATE OR REPLACE PROCEDURE recompile_objects
(status_in IN VARCHAR2 := ‘INVALID’,
name_in IN VARCHAR2 := ‘%’,
type_in IN VARCHAR2 := ‘%’,
schema_in IN VARCHAR2 := USER)
IS
v_objtype VARCHAR2(100);

CURSOR obj_cur IS
SELECT owner, object_name, object_type
FROM ALL_OBJECTS
WHERE status LIKE UPPER (status_in)
AND object_name LIKE UPPER (name_in)
AND object_type LIKE UPPER (type_in)
AND owner LIKE UPPER (schema_in)
ORDER BY
DECODE (object_type,
‘PACKAGE’, 1,
‘FUNCTION’, 2,
‘PROCEDURE’, 3,
‘PACKAGE BODY’, 4);
BEGIN
FOR rec IN obj_cur
LOOP
IF rec.object_type = ‘PACKAGE’
THEN
v_objtype := ‘PACKAGE SPECIFICATION’;
ELSE
v_objtype := rec.object_type;
END IF;

DBMS_DDL.ALTER_COMPILE (v_objtype, rec.owner, rec.object_name);

DBMS_OUTPUT.PUT_LINE
(‘Compiled ‘ || v_objtype || ‘ of ‘ ||
rec.owner || ‘.’ || rec.object_name);
END LOOP;
END recompile_objects;
/

The ALTER_COMPILE procedure may raise any of the following exceptions:

ORA-20000

Insufficient privileges or object does not exist. You must either own the specified object or be granted the CREATE ANY PROCEDURE privilege.

ORA-20001

Remote object, cannot compile. You can only recompile objects on the local database instance.

ORA-20002

Bad value for object type. You need to provide one of the values listed in the previous section.

Notice that these exceptions are not defined in the specification of the package. Instead, ALTER_COMPILE simply calls RAISE_APPLICATION_ERROR with one of the above error numbers. These error numbers may therefore conflict with your own -20NNN error number usages. If you embed calls to ALTER_COMPILE inside your application or utility, watch out for the confusion such conflicts can cause.

Custom Script Using EXECUTE IMMEDIATE

CREATE OR REPLACE PROCEDURE recompile_object
AS
CURSOR my_cur
IS
SELECT   *
FROM dba_objects
WHERE status = ‘INVALID’
ORDER BY owner, object_type, object_name;
BEGIN
FOR cur IN my_cur
LOOP
BEGIN
IF cur.object_type = ‘PACKAGE BODY’
THEN
EXECUTE IMMEDIATE    ‘ALTER PACKAGE ‘
|| cur.owner
|| ‘.’
|| cur.object_name
|| ‘ COMPILE BODY’;
ELSE
EXECUTE IMMEDIATE    ‘ALTER ‘
|| cur.object_type
|| ‘ ‘
|| cur.owner
|| ‘.’
|| cur.object_name
|| ‘ COMPILE’;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
DBMS_OUTPUT.put_line (   cur.object_type
|| ‘ : ‘
|| cur.owner
|| ‘ : ‘
|| cur.object_name
);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END recompile_object;
/