Oracle PL/SQL – ALTER FUNCTION Statement

This article will help you to understand “Oracle PL/SQL – ALTER FUNCTION Statement” with example and description.

Oracle PL/SQL – ALTER FUNCTION statement explicitly recompiles a standalone function.

Sometimes because of ALTER TABLE on table being used in function, the function becomes INVALID. We can check the status of any object of database from user_objects table. Let’s understand this with example.

Example

1. ALTER function example

First we will create table test_alter. Then we will create function get_max_amount.

In this function, we are using amount column of test_alter. Now when we drop this column from test_alter table, the function status becomes INVALID.

When we add it to test_alter table, The status remains INVALID. So we can recompile it using ALTER FUNCTION statement.

Code

1.1 Create table and function.

Output

table TEST_ALTER created.

Output

FUNCTION GET_MAX_AMOUNT compiled

1.2 Check the status of function.

Output

OBJECT_NAME	STATUS
GET_MAX_AMOUNT	VALID

1.3 Drop column and add it back to table.

Output

table TEST_ALTER altered. table TEST_ALTER altered.

1.4 Check the status of function. It’s INVALID now.

Output

OBJECT_NAME	STATUS
GET_MAX_AMOUNT	INVALID

1.5 Compile function using ALTER FUNCTION

Output

function GET_MAX_AMOUNT altered.

1.6 Check the status of function.

Output

OBJECT_NAME	STATUS
GET_MAX_AMOUNT	VALID

References

1. Alter Function :- Oracle official docs