SQL in a Nutshell, 2nd Edition

Name

SET PATH Statement

Synopsis

The SET PATH statement changes the value of the CURRENT PATH setting to one or more schemas.

Platform	Command
DB2	Supported, with variations
MySQL	Not supported
Oracle	Not supported
PostgreSQL	Not supported
SQL Server	Not supported

SQL2003 Syntax

SET PATH schema_name [,...]

Keywords

schema_name [,...]

Defines one or more schemas as the current path.

Rules at a Glance

SET PATH defines one or more schemas used to qualify an unqualified routine name (that is, functions, procedures, and methods).

The following example sets the current path (i.e., schema name) for unqualified objects to scott:

SET PATH scott;

Then, whenever a routine is referenced during the current session, it will assume the scott schema if no schema is identified.

Programming Tips and Gotchas

When referencing multiple schema names, all the schemas must belong to the current database. (The schemas cannot be on a remote database.)

SET PATH does not apply the schema to unqualified objects like tables or views. It only applies to routines.

DB2

DB2 supports several variations on the ANSI standard:

SET {[CURRENT] PATH | CURRENT_PATH} =
   {schema_name | SYSTEM PATH | USER | CURRENT PATH | CURRENT_PATH} [,...]

where:

SET {[CURRENT] PATH | CURRENT_PATH} =

Sets the path, with a slight variation in syntax from the ANSI standard. Note that the equal sign (=) is required.

schema_name

Defines one or more user-specified paths as the current path. The schema_name may be a literal, a host variable, or ...