The ALTER_COMPILE process is equivalent to the following statement ALTERPROCEDURE | FUNCTION | PACKAGE [.] COMPILE [BODY] syntax DBMS_DDL.ALTER_COMPILE (typeVARCHAR2, schemaVARCHAR2, nameVARCHAR2); parameter: If you want to re-COMPILE a program, Oracle will first recompile the identified invali
The ALTER_COMPILE process is equivalent to the following statement: alter procedure | FUNCTION | PACKAGE [.] COMPILE [BODY] syntax DBMS_DDL.ALTER_COMPILE (type VARCHAR2, schema VARCHAR2, name VARCHAR2); parameter: If you want to re-COMPILE a program, Oracle will first recompile the identified invali
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 VARCHAR2,
Name
VARCHAR2 );
Parameters:
If you want
CompileFor a program, Oracle will first
CompileAll
Object.
Example 1:
CompileACCESS_RIGHTS process of the myuser solution:
DBMS_DDL.ALTER_COMPILE ('processed', 'myuser', 'Access _ rights ');
Example 2:
In almost every instance, PL/SQL
ObjectThe name is stored in the upper-write mode. If you specify a double quotation mark
ObjectName, when
CompileThe same method should be specified.
If the specified value is incorrect, the following error is reported:
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
Therefore, if you create the following
Object:
CREATE or replace procedure "My_Object" IS
BEGIN
...
Use:
DBMS_DDL.ALTER_COMPILE ('Procedure ', 'myuser', 'My _ object ');
You can also use ALTER PROCEDURE COMPILE command
Compile ObjectThe biggest advantage of using the DBMS_DDL package is that it can be used in future programs or PL/SQL
Compile, This will have full flexibility to create strong use value (see the following example)
Example 3:
In this example, all invalid
ObjectAnd
Repeat Compile
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
('Referencing' | 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:
ALTER_COMPILE can replace manual and automatic
Compile ObjectTo search for invalid
ObjectIn this way
Compile.
