Recompiling PL/SQL Objects using DBMS_DDL package

In this article, we shall see how we can recompile PL/SQL objects, which are already stored in the database, using the ALTER_COMPILE procedure of DBMS_DDL package.

ALTER_COMPILE Procedure

This procedure is equivalent to the following SQL statement:

ALTER PROCEDURE|FUNCTION|PACKAGE [<schema>.] <name> COMPILE [BODY]

Syntax

DBMS_DDL.ALTER_COMPILE (
   type   VARCHAR2, 
   schema VARCHAR2, 
   name   VARCHAR2);

Where:

Type	The type of the database object. Legal Values are; PROCEDURE - Recompiles specified procedure FUNCTION - Recompiles specified function PACKAGE - Recompiles specified package specification and body PACKAGE_BODY - Recompiles specified package body TRIGGER - Recompiles specified trigger
Schema	The name of the schema containing the object(s) you wish to compile If NULL, then use current schema (case-sensitive).
name	Name of the object (case-sensitive) you wish to compile

Here, if you recompile a program, Oracle will first recompile any objects upon which that program depends and which are marked INVALID.

Example I:

Compile Procedure ACCESS_RIGHTS located in MYUSER schema.

DBMS_DDL.ALTER_COMPILE('PROCEDURE','MYUSER','ACCESS_RIGHTS');

Example II:

In almost every instance, the names of the PL/SQL objects are stored in uppercase. If you specify an object name in double quotes while creating it, then the same case has to be specified while compiling.

If you specify an incorrect case, the following error is displayed.

SQL> dbms_ddl.alter_compile('PROCEDURE','MYUSER','access_rights');

BEGIN dbms_ddl.alter_compile('PROCEDURE','MYUSER','access_rights'); END;

*
ERROR at line 1:
ORA-20000: Unable to compile PROCEDURE "MYUSER"."access_rights", 
insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_DDL", line 68
ORA-06512: at line 1

So if you create an object as below:

CREATE or REPLACE PROCEDURE "My_Object" IS
BEGIN
	...

then use:

DBMS_DDL.ALTER_COMPILE('PROCEDURE','MYUSER','My_Object');

You can choose to use ALTER PROCEDURE <procedure name> COMPILE command to compile objects. One big advantage of using the DBMS_DDL package is that you can use the latter within PL/SQL block or program. This gives you full flexibility to put it to a powerful use (please refer to the example below).

Example III:

In this example we will get all the INVALID objects and re-compiles them.

set termout on
set serverout on
DECLARE
	cursor cur_invalid_objects is
select object_name, object_type from user_objects where status='INVALID';
	rec_columns cur_invalid_objects%ROWTYPE;
	err_status NUMERIC;
BEGIN
	dbms_output.enable(10000);
	open cur_invalid_objects;
	loop

		fetch cur_invalid_objects into rec_columns;
		EXIT WHEN cur_invalid_objects%NOTFOUND;

dbms_output.put_line
('Recompiling ' || rec_columns.object_type || '  ' || rec_columns.object_name);

dbms_ddl.alter_compile(rec_columns.object_type,NULL,rec_columns.object_name);

	end loop;
	close cur_invalid_objects;
EXCEPTION
When others then
begin
	err_status := SQLCODE;
dbms_output.put_line(' Recompilation failed : ' || SQLERRM(err_status));
	
if ( cur_invalid_objects%ISOPEN) then
		CLOSE cur_invalid_objects;
	end if;
	
exception when others then
		null;
end;
end;
/

Summary

The ALTER_COMPILE thus allows compiling of the objects automatically instead of manually, finding all the objects, which are INVALID, and then compiling them one by one.