REBIND_ROUTINE_PACKAGE procedure - rebind a package

Explicitly rebinding the associated package does not revalidate the routine. Revalidate an invalid routine by using automatic revalidation or explicitly by using the ADMIN_REVALIDATE_DB_OBJECTS procedure. Routine revalidation automatically rebinds the dependent package.

Syntax

There are two equally valid methods to invoke REBIND_ROUTINE_PACKAGE. The only difference between the two invocations is the method of specifying the routine name. In the first instance, the routine-name-string variable consists of identifier names separated by periods. In the second method, the routine is identified by separate values for each of the schema, module and name values.

Method 1:

>>-REBIND_ROUTINE_PACKAGE--(--type--,--------------------------->

>--routine-name-string--,--options--)--------------------------><

Method 2:

>>-REBIND_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

routine-name-string (method 1 only)

An input argument of type VARCHAR(386) which specifies the name of the routine or trigger. Trigger names consist of two parts separated by a period and are in the format schema.trigger where the schema is optional. Routine names consist of three part names separated by periods and are in the format schema.module.routine where schema and module are optional. If schema is not specified, the value defaults to the value of the CURRENT SCHEMA special register. If a two-part name is specified, the first part is initially interpreted as a schema name; if the routine is not found under that schema, the first part is interpreted as a module name, and an attempt is made to find the routine in a module of that name under the CURRENT SCHEMA. The schema, module or object names cannot include double quotation marks (") or periods(.).

schema (method 2 only)

An optional input argument of type VARCHAR(128) that 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 (method 2 only)

An optional input argument of type VARCHAR(128) that specifies the name of the module where the routine resides. Do not specify this parameter for triggers. Module routines are ignored if this parameter is not specified. This parameter is case sensitive.

name (method 2 only)

An input argument of type VARCHAR(128) that specifies the name of the routine or trigger. This parameter is case sensitive.

options

An optional input argument of type VARCHAR(1024) which specifies any list of rebind options following the REBIND command syntax. A single value of "ANY" or "CONSERVATIVE" is also supported for backward compatibility and is interpreted as the value for the RESOLVE rebind option.

The qualified name of the routine is used to determine which routine to retrieve. The routine that is found must be an SQL routine; otherwise, an error is returned (SQLSTATE 428F7). If a specific name is not used, more than one routine may be found, and an error is returned (SQLSTATE 42725). If this occurs, the specific name of the required routine must be used.

Authorization

Examples

Example 1: Rebind the package of routine UPDATE_EMPLOYEE using the RESOLVE, REOPT and APREUSE options.

Method 1:
CALL SYSPROC.REBIND_ROUTINE_PACKAGE (
  'P','UPDATE_EMPLOYEE','RESOLVE ANY REOPT ONCE APREUSE YES')
Method 2:
CALL SYSPROC.REBIND_ROUTINE_PACKAGE (
  'P','','','UPDATE_EMPLOYEE','RESOLVE ANY REOPT ONCE APREUSE YES')

Example 2: Rebind the package of routine UPDATE_EMPLOYEE with no options.

Method 1:
CALL SYSPROC.REBIND_ROUTINE_PACKAGE (
  'P','UPDATE_EMPLOYEE','')
Method 2:
CALL SYSPROC.REBIND_ROUTINE_PACKAGE (
  'P','','','UPDATE_EMPLOYEE','')

Example 3: Rebind the package of a compiled trigger.

Method 1:
CALL SYSPROC.REBIND_ROUTINE_PACKAGE (
  'T','DRICARD.MIN_SALARY','REOPT ALWAYS')
Method 2:
CALL SYSPROC.REBIND_ROUTINE_PACKAGE (
  'T','DRICARD','','MIN_SALARY','REOPT ALWAYS')

Example 4: Rebind the package of a compiled function using a three part name.

Method 1
CALL SYSPROC.REBIND_ROUTINE_PACKAGE (
  'F','DRICARD.MODULE.FUNCTION','REOPT ALWAYS')
Method 2
CALL SYSPROC.REBIND_ROUTINE_PACKAGE (
  'F','DRICARD','MODULE','FUNCTION','REOPT ALWAYS')