Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 25, 2003

Recompiling PL/SQL Objects using DBMS_DDL package

By Ajay Gursahani

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.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM