1  SET_SCHEMA
   Specifies the default schema name for an SQL user session in
   dynamically prepared and executed or interactive SQL statements
   until another SET SCHEMA statement is issued.

   Within one multischema database, tables in different schemas can
   be used in a single SQL statement; tables in schemas in different
   databases cannot. If you omit the schema name when you specify
   an object in a multischema database, SQL uses the default schema
   name.
 

2  Environment
   You can use the SET SCHEMA statement:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled

   o  As part of a procedure in an SQL module

   o  In dynamic SQL as a statement to be dynamically executed
 

2  Format

 SET SCHEMA ---+-> schema-string-literal ------+->
               +-> <schema-parameter> ---------+
               +-> <schema-parameter-marker> --+



 schema-string-literal =
 
 --> ' --> schema-expression --> ' -->
 



 schema-expression  =
 
 --+------------------------------------------+-+
   +------> <catalog-name> ------------+-> . -+ |
   +-> " -> <alias>.<catalog-name> ->"-+        |
   |   +----------------------------------------+
   |   +--------------> <name-of-schema> ------+->
   +-> " -> <alias>.<name-of-schema> --> " ----+
 

 

2  Arguments
 

3  schema-expression

   Specifies the name of the default schema for a multischema
   database. If you omit the schema name when you specify an object
   in a multischema database, SQL uses the default schema name. If
   you do not specify a default schema name, the default uses the
   user name of the current user.

   See the User_Supplied_Names HELP topic for more information on
   schemas.
 

3  schema-parameter

   Specifies a host language variable in precompiled SQL or a formal
   parameter in an SQL module language procedure that specifies
   the default schema. The schema parameter must contain a schema
   expression.
 

3  schema-parameter-marker

   Specifies a parameter marker (?)  in a dynamic SQL statement.
   The schema parameter marker refers to a parameter that specifies
   the default schema. The schema parameter marker must specify a
   parameter that contains a schema expression.
 

3  schema-string-literal

   Specifies a character string literal that specifies the
   default schema. The schema string literal must contain a schema
   expression enclosed within single quotation marks.
 

2  Example
   Example 1: Setting schema and catalog defaults to create a table
   in a multischema database

   In this example, user ELLINGSWORTH attaches to two databases: the
   default database, personnel, and the multischema corporate_data
   database with alias CORP. User ELLINGSWORTH attempts to create
   a table in the corporate_data database, and receives an error
   message because the default schema is ELLINGSWORTH, which has
   not been created in the default catalog. User ELLINGSWORTH uses
   SET SCHEMA and SET CATALOG statements to change the defaults to
   catalog ADMINISTRATION and schema ACCOUNTING of the corporate_
   data database.

   Use the SHOW DATABASE statement to see the database settings.

   SQL> ATTACH 'FILENAME personnel';
   SQL> ATTACH 'ALIAS CORP FILENAME corporate_data';
   SQL>  SHOW SCHEMAS;
   Schemas in database with filename personnel
   No schemas found
   Schemas in database CORP
       "CORP.ADMINISTRATION".ACCOUNTING
       "CORP.ADMINISTRATION".PERSONNEL
       "CORP.ADMINISTRATION".RECRUITING
       "CORP.RDB$CATALOG".RDB$SCHEMA
   SQL> CREATE TABLE CORP.BUDGET (COL1 REAL);
   %SQL-F-SCHNOTDEF, Schema "CORP.RDB$CATALOG".CORP is not defined
   SQL> --
   SQL> -- SQL interprets CORP as schema name, and there is no
   SQL> -- CORP schema in the default database.
   SQL> --
   SQL> -- Add quotation marks to designate qualifier CORP as an alias,
   SQL> -- not the schema name.
   SQL> --
   SQL> SET QUOTING RULES 'SQL92';
   SQL> CREATE TABLE "CORP.BUDGET" (COL1 REAL);
   %SQL-F-SCHNOTDEF, Schema "CORP.RDB$CATALOG".ELLINGSWORTH is not defined
   SQL> --
   SQL> -- The default schema in the database with alias CORP
   SQL> -- is the user name ELLINGSWORTH, but there is no
   SQL> -- schema named ELLINGSWORTH.
   SQL> --
   SQL> -- Set the default schema to ACCOUNTING, and qualify it
   SQL> -- with a delimited identifier containing the alias CORP and
   SQL> -- the catalog ADMINISTRATION.  Now you can create the
   SQL> -- table BUDGET within schema ACCOUNTING without qualifying
   SQL> -- the table name.
   SQL> --
   SQL> SET SCHEMA '"CORP.ADMINISTRATION".ACCOUNTING';
   SQL> CREATE TABLE BUDGET (COL1 REAL);
   SQL> SHOW TABLES;
   User tables in database with filename personnel
        CANDIDATES
        COLLEGES
        .
        .
        .
        User tables in database with alias CORP
        "CORP.ADMINISTRATION".ACCOUNTING.BUDGET
        .
        .
        .