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.

Latest Articles