IMPACT OF CHANGING REFERENCED OBJECT ON FUNCTIONS
If we add any column to the table which is used in a stored function then the function get invalidated. But Oracle automatically compiled the invalid object when it is called for execution.
CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPLOYEE_ID NUMBER)
RETURN 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;
--
RETURN V_EMP_NAME;
EXCEPTION
WHEN OTHERS THEN
V_EMP_NAME := 'EMPLOYEE DOES NOT EXISTS';
RETURN V_EMP_NAME;
END;
/ SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEES_COPY', 'GET_EMP_NAME'); Now adding new column in the table EMPLOYEES_COPY.
ALTER TABLE EMPLOYEES_COPY ADD (FULL_NAME VARCHAR2(100));
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEES_COPY', 'GET_EMP_NAME'); Now if we invoke the function through a SQL query then Oracle will automatically compile the function.
SELECT GET_EMP_NAME(100) FROM DUAL;
Now if we check the status of the function then it will show VALID.
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEES_COPY', 'GET_EMP_NAME'); Now if we change the definition of LAST_NAME column to see the impact on function GET_EMP_NAME.
DESC EMPLOYEES_COPY;
ALTER TABLE EMPLOYEES_COPY MODIFY (LAST_NAME VARCHAR2(120));
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEES_COPY', 'GET_EMP_NAME'); SELECT GET_EMP_NAME(100) FROM DUAL;
SELECT *
FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('EMPLOYEES_COPY', 'GET_EMP_NAME'); So we can conclude that any change in referenced table will make the stored function invalidated. But once try to we execute the function it will automatically be compiled by Oracle.
RELATED TOPICS: