Oracle PL/SQL – ALTER function example

The ALTER FUNCTION statement explicitly recompile a standalone function. Sometimes, ALTER TABLE on the table being used in function will cause the function becomes INVALID, we need to recompile (alter function) it to make it VALID again.

1. ALTER function example

First, we will create table test_alter, function get_max_amount. In this function, we are using ‘amount’ column of test_alter. Now when we drop the ‘amount’ column from the test_alter table, the function status becomes INVALID automatic.

Even we add the ‘amount’ column back to the test_alter table, the function status remains INVALID. To make the function VALID again, we need to recompile it using ALTER FUNCTION statement.

1.1 Create table and function.


--creating table test_alter

CREATE TABLE test_alter
(
  ID number(5),
  AMOUNT number(5)
);

CREATE OR REPLACE FUNCTION get_max_amount RETURN NUMBER IS
maxAmount NUMBER(5);

BEGIN

  select MAX(amount) into maxAmount from test_alter;

  RETURN maxAmount;
END get_max_amount;
/

1.2 Check the status of function.


select object_name, status from user_objects where object_name = 'GET_MAX_AMOUNT';
-- output : VALID

1.3 Drop column and add it back to table, check the function status.


-- drop column
ALTER TABLE test_alter DROP column amount;

select object_name, status from user_objects where object_name = 'GET_MAX_AMOUNT';
-- output : INVALID

-- add column
ALTER TABLE test_alter ADD amount number(5);

select object_name, status from user_objects where object_name = 'GET_MAX_AMOUNT';
-- output : INVALID

1.4 Recompile the function with ALTER FUNCTION


ALTER FUNCTION GET_MAX_AMOUNT COMPILE;
-- Output : function GET_MAX_AMOUNT altered.

-- Check the function status again!
select object_name, status from user_objects where object_name = 'GET_MAX_AMOUNT';
-- output : VALID

References

  1. Alter Function :- Oracle official docs

About the Author

author image
Dhaval Dadhaniya
Dhaval Dadhaniya is a software engineer by profession and reader/writter by passion. He is working in a well reputed MNC as an applications Developer with 5 years of experience. He have good understanding and knowledge of Java, Database, Spring, Hibernate and exploring other technologies related to Software development. Befriend him on Facebook

Comments

Leave a Reply

avatar
newest oldest most voted
Navneet
Guest
Navneet

One of the useful article.

Keep up good work