SET SCHEMA statement

The SET SCHEMA statement changes the value of the CURRENT SCHEMA special register.

This statement 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

Read syntax diagramSkip visual syntax diagramSETCURRENTSCHEMA =schema-nameUSERSESSION_USERSYSTEM_USERCURRENT_USERhost-variablestring-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-aligned. 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 affect 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.