DBMS

Compile PL / SQL objects with DBMS_DDL

This article, we will see how to recompile the object stored in the database with the alter_compile process in the DBMS_DDL package.

ALTER_COMPILE process This process is equivalent to the following statement alter procedure | function | package [.] compile [body] syntax dbms_ddl.alter_compile (Type Varchar2, schema varcha2, name varcha2); parameter:

Type database object Type. The correct value is as follows: Procedure - recompile the specified process Function - recompile the specified function package - recompile the specified header and the encapsulation package_body - recompile the specified buffer TRIGGER - recompile the specified trigger

Schema

The name of the program is compiled, if null, the current scheme name (case sensitive) Name

Re-compiling the name of the object (case sensitive)

Now, if you want to compile a program, Oracle first recomesces all objects that are identified by the INVALID.

Example 1: ACCESS_RIGHTS process for compiling the Myuser scheme: dbms_ddl.alter_compile ('procedure', 'myuser', 'access_rights');

Example 2: Almost every instance, the PL / SQL object name is stored in uppercase, if you specify a double quoted object name, then you should specify the same manner when compiling.

If you specify incorrectly, you will report the following error 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 Privies or Does Not Exist ORA-06512: AT "sys.dbms_ddl", line 68 ora-06512: At line 1

So if you create the following object: create or replace procedure "My_Object" is Begin ... DBMS_DDL.alter_Compile ('procedure', 'myuser', 'my_Object');

You can also use the alter procedure Compile command to compile the object, with the maximum advantage of the dbms_ddl package is that you can compile in later programs or PL / SQL, which will have full flexibility to create strong use value (please Refer to the following example)

Example 3: This example will get all invalid object and recompile 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 EXCEPTION WHEN OTHERS THEN NULL; END; END; /

Summary: ALTER_COMPILE can automatically compile objects, find those invalid objects to compile one by one.