VALIDATE

The VALIDATE stored procedure is used to validate and recompile database objects by object ID.

Syntax

DBMS_UTILITY.VALIDATE (
    OBJECT_ID     NUMBER,
    IS_RECOMPILE  BOOLEAN DEFAULT FALSE);

Parameters

Usage instructions

• The VALIDATE stored procedure first queries the sys.all_objects view for the object owner, name, and type based on the OBJECT_ID, and then performs validation and recompilation on the object.
• After the VALIDATE operation is completed, if the compilation is successful, the compilation results are written to the PL cache and disk cache, which can be used to warm up PL objects.
• Supported object types include PROCEDURE, FUNCTION, TRIGGER, PACKAGE, PACKAGE BODY, and TYPE.
• If the specified OBJECT_ID does not exist in sys.all_objects, the stored procedure returns silently without raising an error.
• This subprogram uses invoker's rights, allowing ordinary users to compile objects from other schemas.
• Because VALIDATE is a reserved SQL keyword, you cannot use the CALL DBMS_UTILITY.VALIDATE(...) syntax to invoke it. Instead, you must call it within a PL/SQL anonymous block.

Examples

Create a stored procedure named MY_PROC.

obclient> CREATE OR REPLACE PROCEDURE MY_PROC IS
BEGIN
  NULL;
END;
/

Query the object information of MY_PROC to obtain its OBJECT_ID.

obclient> SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE, STATUS FROM SYS.ALL_OBJECTS
  WHERE OBJECT_NAME = 'MY_PROC';

The return result is as follows:

+-----------+-------------+-------------+--------+
| OBJECT_ID | OBJECT_NAME | OBJECT_TYPE | STATUS |
+-----------+-------------+-------------+--------+
|    500002 | MY_PROC     | PROCEDURE   | VALID  |
+-----------+-------------+-------------+--------+
1 row in set

Call the VALIDATE stored procedure in a PL/SQL anonymous block.

obclient> DECLARE
  obj_id NUMBER;
BEGIN
  SELECT OBJECT_ID INTO obj_id FROM SYS.ALL_OBJECTS
    WHERE OBJECT_NAME = 'MY_PROC';
  DBMS_UTILITY.VALIDATE(obj_id);
END;
/

Query the status of MY_PROC again.

obclient> SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE, STATUS FROM SYS.ALL_OBJECTS
  WHERE OBJECT_NAME = 'MY_PROC';

The return result is as follows:

+-----------+-------------+-------------+--------+
| OBJECT_ID | OBJECT_NAME | OBJECT_TYPE | STATUS |
+-----------+-------------+-------------+--------+
|    500002 | MY_PROC     | PROCEDURE   | VALID  |
+-----------+-------------+-------------+--------+
1 row in set