Modifying Subprograms

Contact Us or call 1-877-932-8228
Modifying Subprograms

Modifying Subprograms

Once a subprogram is created in the database, it might require changes due to new or modified requirements, changes in data, programmer errors or refactoring to improve the quality of a code base. In most situations, a stored procedure (or other subprogram) will require changes over time.

Code Sample:

Stored-Procedures-and-Functions/Demos/proc_create_or_replace_hello_world.sql
create or replace procedure test1
is 
begin
 dbms_output.put_line('Hello from a replaced stored procedure');
end;
/

exec test1

begin
  test1;
end;
/

Code Explanation

In order to avoid explicitly dropping a stored procedure and re-creating it after each change, the Oracle proprietary REPLACE keyword can be used so that an existing object is replaced by a new one with the same name and type.

Code Sample:

Stored-Procedures-and-Functions/Demos/function_create_or_replace_hello_world.sql
create or replace function test2
return varchar2
is
begin
  return 'Hello world from a function!';
end;
/

select test2
from dual;

begin
  dbms_output.put_line(test2());
end;
/

drop function test2;

Code Explanation

A function is distinguished from a stored procedure in that it returns a value. The type (but not the length or precision) of the return value must be specified, and the RETURN keyword is used to return the value in the body of the function. The function can be called from a query - a stored procedure cannot.

Next