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)