DB2 10.5 for Linux, UNIX, and Windows

ALTER PROCEDURE (sourced) statement

The ALTER PROCEDURE (Sourced) statement modifies an existing sourced procedure by changing the data type of one or more parameters of the sourced procedure.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:

ALTERIN privilege on the schema of the procedure
Owner of the procedure, as recorded in the OWNER column of the SYSCAT.ROUTINES catalog view
DBADM authority

Syntax


>>-ALTER--| procedure-designator |------------------------------>

   .-------------------------------------------.   
   V                                           |   
>----ALTER PARAMETER--| parameter-alteration |-+---------------><

procedure-designator

|--+-PROCEDURE--procedure-name--+-------------------------+-+---|
   |                            '-(--+---------------+--)-' |   
   |                                 | .-,---------. |      |   
   |                                 | V           | |      |   
   |                                 '---data-type-+-'      |   
   '-SPECIFIC PROCEDURE--specific-name----------------------'   

parameter-alteration

|--parameter-name--SET DATA TYPE--data-type---------------------|

Description

procedure-designator: Uniquely identifies the procedure to be altered. The identified procedure must be a sourced procedure (SQLSTATE 42849). For more information, see Function, method, and procedure designators.
parameter-name: Identifies the parameter to be altered. The parameter-name must identify an existing parameter of the procedure (SQLSTATE 42703). The name must not identify a parameter that is otherwise being altered in the same ALTER PROCEDURE statement (SQLSTATE 42713).
data-type: Specifies the new local data type of the parameter. SQL data type specifications and abbreviations that are valid for the data-type definition of a CREATE TABLE statement can be specified. BLOB, CLOB, DBCLOB, DECFLOAT, XML, REFERENCE, and user-defined types are not supported (SQLSTATE 42815).

Example

Assume that federated procedure FEDEMPLOYEE has been created for a remote Oracle procedure named 'EMPLOYEE'. The data type of an input parameter named SALARY maps to a DOUBLE(8) in DB2®. Alter the data type of this parameter to DECIMAL(5,2).

   ALTER PROCEDURE FEDEMPLOYEE
     ALTER PARAMETER SALARY
     SET DATA TYPE DECIMAL(5,2)