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 –
Sometimes because of
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
Code
1.1 Create table and function.
0 1 2 3 4 5 6 7 8 |
--creating table test_alter CREATE TABLE test_alter ( ID number(5), AMOUNT number(5) ); |
Output
0 1 2 3 4 5 6 7 8 9 10 11 |
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
1.2 Check the status of function.
0 1 2 |
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.
0 1 2 3 4 |
ALTER TABLE test_alter DROP column amount; ALTER TABLE test_alter ADD amount number(5); |
Output
1.4 Check the status of function. It’s INVALID now.
0 1 2 |
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
0 1 2 |
ALTER FUNCTION GET_MAX_AMOUNT COMPILE; |
Output
1.6 Check the status of function.
0 1 2 |
select object_name, status from user_objects where object_name = 'GET_MAX_AMOUNT'; |
Output
OBJECT_NAME | STATUS |
---|---|
GET_MAX_AMOUNT | VALID |
Hi there everyone, it’s my first visit at this web page, and article is really fruitful in favor of me, keep up posting these types of articles or reviews.|