Oracle PL/SQL - ALTER FUNCTION Statement

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.


--creating table test_alter

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

--creating table test_alter

CREATE TABLE test_alter

(

ID number(5),

AMOUNT number(5)

);

Output

table TEST_ALTER created.


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;
/

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;

Output

FUNCTION GET_MAX_AMOUNT compiled

1.2 Check the status of function.


select object_name, status from user_objects where object_name = 'GET_MAX_AMOUNT';

select object_name, status from user_objects where object_name = 'GET_MAX_AMOUNT';

Output

OBJECT_NAME	STATUS
GET_MAX_AMOUNT	VALID

1.3 Drop column and add it back to table.


ALTER TABLE test_alter DROP column amount;

ALTER TABLE test_alter ADD amount number(5);

ALTER TABLE test_alter DROP column amount;

ALTER TABLE test_alter ADD amount number(5);

Output

table TEST_ALTER altered. table TEST_ALTER altered.

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


select object_name, status from user_objects where object_name = 'GET_MAX_AMOUNT';

select object_name, status from user_objects where object_name = 'GET_MAX_AMOUNT';

Output

OBJECT_NAME	STATUS
GET_MAX_AMOUNT	INVALID

1.5 Compile function using ALTER FUNCTION


ALTER FUNCTION GET_MAX_AMOUNT COMPILE;

ALTER FUNCTION GET_MAX_AMOUNT COMPILE;

Output

function GET_MAX_AMOUNT altered.

1.6 Check the status of function.


select object_name, status from user_objects where object_name = 'GET_MAX_AMOUNT';

select object_name, status from user_objects where object_name = 'GET_MAX_AMOUNT';

Output

OBJECT_NAME	STATUS
GET_MAX_AMOUNT	VALID

References

Alter Function :- Oracle official docs

Facebook

Twitter