IMPACT OF CHANGING REFERENCED OBJECT ON PROCEDURE
If we add any column to the table which is used in a stored procedure then the procedure gets invalidated. But Oracle automatically compiled the invalid object when it is called for execution.
CREATE OR REPLACE PROCEDURE GET_EMP_DATA_PROC(P_EMPLOYEE_ID NUMBER,
P_EMP_DETAILS OUT VARCHAR2)
IS
V_EMP_NAME VARCHAR2(100);
BEGIN
SELECT FIRST_NAME||' '||LAST_NAME
INTO V_EMP_NAME
FROM EMPLOYEES_COPY
WHERE EMPLOYEE_ID = P_EMPLOYEE_ID;
--
P_EMP_DETAILS := V_EMP_NAME;
EXCEPTION
WHEN OTHERS THEN
V_EMP_NAME := 'EMPLOYEE DOES NOT EXISTS';
P_EMP_DETAILS := V_EMP_NAME;
END;
/
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEES_COPY', 'GET_EMP_DATA_PROC');
ALTER TABLE EMPLOYEES_COPY ADD (FULL_NAME VARCHAR2(100));
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEES_COPY', 'GET_EMP_DATA_PROC');
Calling the procedure GET_EMP_DATA_PROC.
SET SERVEROUTPUT ON;
DECLARE
V_EMP_DETAILS VARCHAR2(100);
BEGIN
GET_EMP_DATA_PROC(100, V_EMP_DETAILS);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME -> '||V_EMP_DETAILS);
END;
/
Now if we check the status of the procedure then it will show VALID.
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEES_COPY', 'GET_EMP_DATA_PROC');
ALTER TABLE EMPLOYEES_COPY MODIFY (LAST_NAME VARCHAR2(130));
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEES_COPY', 'GET_EMP_DATA_PROC');
SET SERVEROUTPUT ON;
DECLARE
V_EMP_DETAILS VARCHAR2(100);
BEGIN
GET_EMP_DATA_PROC(100, V_EMP_DETAILS);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME -> '||V_EMP_DETAILS);
END;
/
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEES_COPY', 'GET_EMP_DATA_PROC');
So we can conclude that any change in referenced table will make the stored procedure as invalid. But once try to we execute the procedure it will automatically be compiled by Oracle.
RELATED TOPICS:
- MANAGING DEPENDENCIES IN ORACLE
- IMPACT OF CHANGING REFERENCED OBJECT ON VIEW
- IMPACT OF CHANGING REFERENCED OBJECT ON FUNCTIONS
