ALTER_ROUTINE_PACKAGE procedure

This procedure alters values for the package associated with a compiled SQL routine or a compiled trigger, without the need for rebinding.

It is functionally equivalent to the ALTER PACKAGE statement, except that it takes an object name instead of a package name as an argument. The ALTER_ROUTINE_PACKAGE procedure can be invoked from the command line or called from an application.

Read syntax diagramSkip visual syntax diagramALTER_ROUTINE_PACKAGE(type ,schema,module, name,options)

The schema is SYSPROC.

Procedure parameters

type
An input argument of type CHAR(2) that specifies the type of routine or compiled trigger, using one of the following values:
  • 'P ' for a procedure
  • 'SP' for the specific name of a procedure
  • 'F' for a compiled function
  • 'SF' for a specific name of a compiled function
  • 'T' for a compiled trigger
schema
An optional input argument of type VARCHAR(128), which specifies the schema of the routine or trigger. If a schema is not specified, the value will default to the value of the CURRENT SCHEMA special register. This parameter is case sensitive.
module
An optional input argument of type VARCHAR(128), which specifies the name of the module where the routine resides. This parameter cannot be specified for triggers. If this parameter is not specified, then module routines are ignored. This parameter is case sensitive.
name
An input argument of type VARCHAR(128), which specifies the name of the routine or trigger. This parameter is case sensitive.
options
An input argument of type VARCHAR(1024), which specifies a list of any options supported by the ALTER PACKAGE statement. At least one ALTER PACKAGE clause must be supplied within the options parameter.

Authorization

One of the following authorities is required to execute the procedure:
  • EXECUTE privilege on the procedure
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.

Examples

Alter the underlying package for an existing stored procedure by the name of UPDATE_EMPLOYEE.

CALL SYSPROC.ALTER_ROUTINE_PACKAGE ('P','','','UPDATE_EMPLOYEE',
   'ACCESS PLAN REUSE YES OPTIMIZATION PROFILE AYYANG.INDEXHINTS')

Alter the package for a compiled trigger called MIN_SALARY, in the DRICARD schema.

CALL SYSPROC.ALTER_ROUTINE_PACKAGE ('T','DRICARD','','MIN_SALARY',
   'OPTIMIZATION PROFILE AYYANG.INDEXHINTS')

Alter the package for a compiled function, using a three part name.

CALL SYSPROC.ALTER_ROUTINE_PACKAGE ('F','DRICARD','MODULE','FUNCTION','APREUSE YES')