The
SET CURRENT PACKAGE PATH statement assigns a value to the CURRENT
PACKAGE PATH special register.
This statement is not under transaction control.
Invocation
This statement can only be embedded
in an application program. It is an executable statement that cannot
be dynamically prepared.
Authorization
None required.
Syntax
.-=-.
>>-SET CURRENT PACKAGE PATH--+---+------------------------------>
.-,------------------------.
V |
>----+-schema-name----------+-+--------------------------------><
+-CURRENT PACKAGE PATH-+
+-+-CURRENT PATH-+-----+
| '-CURRENT_PATH-' |
+-+-CURRENT USER-+-----+
| '-CURRENT_USER-' |
+-SESSION_USER---------+
+-SYSTEM_USER----------+
+-USER-----------------+
+-host-variable--------+
'-string-constant------'
Description
- schema-name
- Identifies a schema. The name must not be a delimited identifier
that is empty or that contains only blanks (SQLSTATE 42815).
- CURRENT PACKAGE PATH
- The value of the CURRENT PACKAGE PATH special register before
this statement executes.
- CURRENT PATH
- The value of the CURRENT PATH special register.
- CURRENT USER
- The value of the CURRENT USER special register.
- SESSION_USER
- The value of the SESSION_USER special register.
- SYSTEM_USER
- The value of the SYSTEM_USER special register.
- USER
- The value of the USER special register.
- host-variable
- Contains one or more schema names, separated by commas. The host
variable must:
- Be a character-string variable (CHAR or VARCHAR). The actual length
of the contents of the host variable must not exceed the length of
the CURRENT PACKAGE PATH special register.
- Not be the null value. If an indicator variable is provided, its
value must not indicate a null value.
- Contain an empty or blank string, or one or more schema names
separated by commas.
- Be padded on the right with blanks if the actual length of the
host variable is greater than the content.
- Not contain CURRENT PACKAGE PATH, CURRENT PATH, CURRENT_PATH,
CURRENT USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, PATH, or USER.
- Not contain a delimited identifier that is empty or that contains
only blanks.
- string-constant
- Specifies a character string constant that contains zero, one,
or more schema names that are separated by commas. The string constant
must:
- Have a length that does not exceed the maximum length of the CURRENT
PACKAGE PATH special register.
- Not contain CURRENT PACKAGE PATH, CURRENT PATH, CURRENT_PATH,
CURRENT USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, PATH, or USER.
- Not contain a delimited identifier that is empty or that contains
only blanks.
Rules
- If the same schema appears more than once in the list, the first
occurrence of the schema is used (SQLSTATE 01625).
- The number of schemas that can be specified is limited by the
total length of the CURRENT PACKAGE PATH special register. The special
register string is built by taking each specified schema name and
removing trailing blanks, delimiting the name with double quotation
marks, and separating the schema names with commas. The length of
the resulting list cannot exceed the maximum length of the special
register (SQLSTATE 0E000).
- A schema name that does not conform to the rules for an ordinary
identifier (for example, a schema name that contains lowercase characters
or characters that cannot be specified in an ordinary identifier),
must be specified as a delimited schema name, and must not be specified
within a host variable or string constant.
- To indicate that the current value of a special register (specified
as a single keyword) is to be used in the package path, specify the
name of the special register as a keyword. If the name of the special
register is specified as a delimited identifier instead (for example,
"USER"), it is interpreted as a schema name of that value ('USER').
- The following rules are used to determine whether a value specified
in a SET CURRENT PACKAGE PATH statement is a variable or a schema
name:
- If name is the same as a parameter or
SQL variable in the SQL procedure, name is
interpreted as a parameter or SQL variable, and the value in name is
assigned to the package path.
- If name is not the same as a parameter
or SQL variable in the SQL procedure, name is
interpreted as a schema name, and the value in name is
assigned to the package path.
Notes
- Transaction considerations: The SET CURRENT PACKAGE
PATH statement is not a commitable operation. ROLLBACK has no effect
on the CURRENT PACKAGE PATH special register.
- Existence checking of schemas: No validation that
the specified schemas exist is made at the time that the CURRENT PACKAGE
PATH special register is set. For example, a schema that is misspelled
is not detected, which could affect the way subsequent SQL operates.
At package execution time, authorization to a matching package is
checked, and if this authorization check fails, an error is returned
(SQLSTATE 42501).
- Contents of host variable or string constant: The
contents of a host variable or a string constant are interpreted as
a list of schema names. If multiple schema names are specified, they
must be separated by commas. Each schema name in the list must conform
to the rules for forming an ordinary identifier, or be specified as
a delimited identifier. The contents of the host variable or string
constant are not folded to uppercase.
- Restrictions specific to embedded SQL for COBOL applications: A
maximum of ten literal (non-host variable) values can appear on the
right side of a SET CURRENT PACKAGE PATH statement. Such values can
have a maximum length of 130 (non-delimited) or 128 (delimited).
Examples
- Example 1: Set the CURRENT PACKAGE PATH special register
to the following list of schemas: MYPKGS, 'ABC E', SYSIBM
SET CURRENT PACKAGE PATH = MYPKGS, 'ABC E', SYSIBM
The
following statement sets a host variable to the value of the resulting
list: SET :hvpklist = CURRENT PACKAGE PATH
The
value of the host variable is: "MYPKGS", "ABC E", "SYSIBM".
- Example 2: Set the CURRENT PACKAGE PATH special register
to the following list of schemas: "SCH4","SCH5", where :hvar1 contains
'SCH4,SCH5'.
SET CURRENT PACKAGE PATH :hvar1
The
value of the CURRENT PACKAGE PATH special register after this statement
executes is: "SCH4","SCH5".
- Example 3: Set the CURRENT PACKAGE PATH special register
to the following list of schemas: "SCH1","SCH#2","SCH3","SCH4","SCH5",
where :hvar1 contains 'SCH4,SCH5'.
SET CURRENT PACKAGE PATH = SCH1,'SCH#2',"SCH3",:hvar1
The
value of the CURRENT PACKAGE PATH special register after this statement
executes is: "SCH1","SCH#2","SCH3","SCH4","SCH5".
- Example 4: Clear the CURRENT PACKAGE PATH special register.
SET CURRENT PACKAGE PATH = ''
- Example 5: Temporarily append the "SCH_PROD" schema (contained
in the :prodschema host variable) and the "SCH_PROD2" schema (contained
in the :prod2schema host variable) to the end of the CURRENT PACKAGE
PATH special register for execution of the SUMMARIZE procedure. Then,
switch the CURRENT PACKAGE PATH special register back to its previous
value.
SET :oldCPP = CURRENT PACKAGE PATH
SET CURRENT PACKAGE PATH = CURRENT PACKAGE PATH,:prodschema,:prod2schema
CALL SUMMARIZE(:V1,:V2)
SET CURRENT PACKAGE PATH = :oldCPP
- Example 6: Set the CURRENT PACKAGE PATH special register
to a list of delimited schema names: "MY.SCHEMA" (imbedded period),
"OLD SCHEMA" (imbedded blank). Use a single host variable containing
both delimited identifiers:
hv = '"MY.SCHEMA", "OLD SCHEMA"'
SET CURRENT PACKAGE PATH = :hv
or use a single
string constant containing both delimited identifiers: SET CURRENT PACKAGE PATH = '"MY.SCHEMA", "OLD SCHEMA"'
or
use a list of delimited schemas: SET CURRENT PACKAGE PATH = 'MY.SCHEMA', 'OLD SCHEMA'