PART 2: Writing a PL/SQL program to use Oracle built-in packages/procedure to compile the objects.

In order to use DBMS_DDL.ALTER_COMPILE procedure, we will modify a column of a table which is being referenced in the procedure.

Note: Any modification to an underlying table will make associated PL/SQL objects INVALID.

ALTER TABLE JOB_HISTORY MODIFY (department_id NUMBER(5));

Run the same SQL statement and check if any related PL/SQL object is INVALID.

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS

FROM USER_OBJECTS

WHERE INITCAP(OBJECT_TYPE) IN

('Procedure', 'Function', 'Package', Trigger);

Create anonymous block to compile all INVALID objects using DBMS_DDL.ALTER_COMPILE. In order to do so, we need to find out all INVALID objects using a cursor.

SET SERVEROUTPUT ON

DECLARE

CURSOR C_INVALID_OBJECTS

AS

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS

FROM USER_OBJECTS

WHERE STATUS = 'INVALID';

R_INVALID_OBJECTS C_INVALID_OBJECTS%ROWTYPE;

BEGIN

OPEN C_INVALID_OBJECTS;

FETCH C_INVALID_OBJECTS;

LOOP

EXECUTE DBMS_DDL.ALTER_COMPILE(R_INVALID_OBJECTS.OBJECT_TYPE, HR, R_INVALID_OBJECTS.OBJECT_NAME);

EXIT WHEN R_INVALID_OBJECTS%NOTFOUND;

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(No data found);

END;

Step 2

Complete the remaining part as directed in the comment inside the BEGIN section of the anonymous block.

Compile and run the program by clicking on the green triangle. If you receive errors, correct them until the program runs correctly.

Refer table 10.7 in the book and add an exception to the above program. Alter the program to compile a table and demonstrate the exception catches (ORA-20002: Not a valid object type value.). (60%)

What is the use of EXECUTE IMMEDIATE? Give an example of its usage here in the context of the above example. (40%)

HR SELECT OBJECT NAME, OBUECT TYPE, STATUS FROM USER_OBJECIS WHERE INITCAP (OBJECT_TYPE) IN ('Procedure Function',Package' , 'Trigger') ?? (B)? | All Rows Fetched : 4 in 0.141 seconds SOL 1 UPDATE_JOB_HISTORY TRIGGER 2 ADD JOB HISTORY 3 SECURE EMPLOYEES TRIGGER OBJECT_NAMEOBJECT TYPE STATUS INVALID PROCEDURE INVALID VALID PROCEDURE VALID