Altering Stored Procedures

Go Up to Altering and Dropping Stored Procedures

To change a stored procedure, use ALTER PROCEDURE. This statement changes the definition of an existing stored procedure while preserving its dependencies according to which metadata objects reference the stored procedure, and which objects the stored procedure references.

Changes made to a procedure are transparent to all client applications that use the procedure; you do not have to rebuild the applications. However, see Altering and Dropping Procedures in Use for issues of managing versions of stored procedures.

Only SYSDBA and the owner of a procedure can alter it.

Important:
Be careful about changing the type, number, and order of input and output parameters to a procedure, since existing code might assume that the procedure has its original format.

When you alter a procedure, the new procedure definition replaces the old one. To alter a procedure, follow these steps:

1. Copy the original data definition file used to create the procedure. Alternatively, use isql -extract to extract a procedure from the database to a file.
2. Edit the file, changing CREATE to ALTER, and changing the procedure definition as desired. Retain whatever is still useful.

Advance To:

• ALTER PROCEDURE syntax