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