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
  • SCHEMAADM authority on the schema of the procedure
  • DBADM authority

Syntax

Read syntax diagramSkip visual syntax diagramALTERprocedure-designator ALTER PARAMETERparameter-alteration
procedure-designator
Read syntax diagramSkip visual syntax diagramPROCEDUREprocedure-name(,data-type)SPECIFIC PROCEDUREspecific-name
parameter-alteration
Read syntax diagramSkip visual syntax diagramparameter-nameSET DATA TYPEdata-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)