PART 2: Writing a PL/SQL program to use Oracle built-in packages/procedure to co
ID: 3915538 • Letter: P
Question
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.
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 VALIDExplanation / Answer
execute immediate:
dynamic sql is programming methodology for generating and running statements at runtime. there are two ways to write dynamic sql.
1.native dynamic sql
2. dbms_sql
native dynamic sql uses the "EXECUTE IMMEDIATE" command to create and execute the sql at run time. But the datatype and number of variables that to be used at run time need to be known before.
EXECUTE IMMEDIATE (<SQL>)
[INTO<variables>]
[USING <bind_variable_values>]
example for execute immediate usage:
DECLARE
sql VARCHAR2(500);
empname VARCHAR2(50):
empno NUMBER;
salar NUMBER;
manage NUMBER;
BEGIN
sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
EXECUTE IMMEDIATE sql INTO empname,empno,salar,manage
USING 1001;
Dbms_output.put_line('Employee Name:‘||empname);
Dbms_output.put_line('Employee Number:‘||empno);
Dbms_output.put_line(‘Salary:'||salai);
Dbms_output.put_line('Manager ID:‘||manage);
END;
/
output:
Employee Name : XXX
Employee Number: 1001
Salary: 15000
Manager ID: 1000
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.