The SET SCHEMA statement changes the value of the CURRENT
SCHEMA special register. It is not under transaction control. If the
package is bound with the DYNAMICRULES BIND option, this statement
does not affect the qualifier used for unqualified database object
references.
Invocation
The statement can be embedded
in an application program or issued interactively. It is an executable
statement that can be dynamically prepared.
Authorization
None required.
Syntax
.-CURRENT-. .-=-.
>>-SET--+---------+--SCHEMA--+---+--+-schema-name-----+--------><
+-USER------------+
+-SESSION_USER----+
+-SYSTEM_USER-----+
+-CURRENT_USER----+
+-host-variable---+
'-string-constant-'
Description
- schema-name
- This one-part name identifies a schema that exists at the application
server. The length must not exceed 128 bytes (SQLSTATE 42815).
No validation that the schema exists is made at the time that the
schema is set. If a schema-name is misspelled,
the error will not be caught, and that could affect the way that subsequent
SQL statements execute.
- USER
- The value in the USER special register.
- SESSION_USER
- The value in the SESSION_USER special register.
- SYSTEM_USER
- The value in the SYSTEM_USER special register.
- CURRENT_USER
- The value in the CURRENT_USER special register.
- host-variable
- A variable of type CHAR or VARCHAR. The length of the contents
of the host-variable must not exceed 128
bytes (SQLSTATE 42815). It cannot be set to null. If host-variable has
an associated indicator variable, the value of that indicator variable
must not indicate a null value (SQLSTATE 42815).
The characters
of the host-variable must be left justified.
When specifying the schema-name with a host-variable,
all characters must be specified in the exact case intended as there
is no conversion to uppercase characters.
- string-constant
- A character string constant with a maximum length of 128 bytes.
Rules
- If the value specified does not conform to the rules for a schema-name,
an error (SQLSTATE 3F000) is raised.
- The value of the CURRENT SCHEMA special register is used as the
schema name in all dynamic SQL statements, with the exception of the
CREATE SCHEMA statement, where an unqualified reference to a database
object exists.
- The QUALIFIER bind option specifies the schema name for use as
the qualifier for unqualified database object names in static SQL
statements.
Notes
- The initial value of the CURRENT SCHEMA special register is equivalent
to USER.
- Setting the CURRENT SCHEMA special register does not effect the
CURRENT PATH special register. Hence, the CURRENT SCHEMA will not
be included in the SQL path and functions, procedures and user-defined
type resolution may not find these objects. To include the current
schema value in the SQL path, whenever the SET SCHEMA statement is
issued, also issue the SET PATH statement including the schema name
from the SET SCHEMA statement.
- CURRENT SQLID is accepted as a synonym for CURRENT SCHEMA and
the effect of a SET CURRENT SQLID statement will be identical to that
of a SET CURRENT SCHEMA statement. No other effects, such as statement
authorization changes, will occur.
Examples
Example 1: The following
statement sets the CURRENT SCHEMA special register.
SET SCHEMA RICK
Example
2: The following example retrieves the current value of the CURRENT
SCHEMA special register into the host variable called CURSCHEMA.
EXEC SQL VALUES (CURRENT SCHEMA) INTO :CURSCHEMA;
The
value would be RICK, set by the previous example.