1  SET_CATALOG

   Specifies the default catalog name for an SQL user session
   in dynamically prepared and executed or interactive SQL until
   another SET CATALOG statement is issued.

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

2  Environment
   You can use the SET CATALOG 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 CATALOG ---+-> <catalog-string-literal> ---+->
                +-> <catalog-parameter> --------+
                +-> <catalog-parameter-marker> -+



 catalog-string-literal =
 
 --> ' --> catalog-expression --> ' -->
 



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

 

2  Arguments
 

3  catalog-expression

   Specifies the name of the default catalog for a multischema
   database. If you omit the catalog name when you specify an object
   in a multischema database, SQL uses the default catalog name.
   If you do not specify a default catalog name, the default is
   RDB$CATALOG.

   If you qualify the catalog name with an alias, the alias and
   catalog name pair must be in uppercase characters and you must
   enclose the alias and catalog name pair within double quotation
   marks.

   See the User_Supplied_Names HELP topic for more information on
   catalogs.
 

3  catalog-parameter

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

3  catalog-parameter-marker

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

3  catalog-string-literal

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

2  Examples

   Example 1: Setting schema and catalog defaults for the default
   database

   In this example, the user attaches to the multischema corporate_
   data database, uses SET SCHEMA and SET CATALOG statements
   to change the defaults to catalog ADMINISTRATION and schema
   ACCOUNTING of the corporate_data database, and creates the table
   BUDGET in the schema ACCOUNTING.

   SQL> ATTACH 'FILENAME corporate_data';
   SQL> SHOW CATALOGS;
   Catalogs in database with filename corporate_data
       ADMINISTRATION
       RDB$CATALOG
   SQL>  SHOW SCHEMAS;
   Schemas in database with filename corporate_data
       ADMINISTRATION.ACCOUNTING
       ADMINISTRATION.PERSONNEL
       ADMINISTRATION.RECRUITING
       RDB$SCHEMA
   SQL> SET CATALOG 'ADMINISTRATION';
   SQL> SET SCHEMA 'ACCOUNTING';
   SQL> CREATE TABLE BUDGET (COL1 REAL);
   SQL> SHOW TABLES;
        BUDGET
        DAILY_HOURS
        DEPARTMENTS
        .
        .
        .
   SQL> --
   SQL> -- To see the qualified table names, set default
   SQL> -- to another schema and catalog.
   SQL> --
   SQL> SET CATALOG 'RDB$CATALOG';
   SQL> SET SCHEMA 'RDB$SCHEMA';
   SQL> SHOW TABLES
   User tables in database with filename corporate_data
        ADMINISTRATION.ACCOUNTING.BUDGET
        ADMINISTRATION.ACCOUNTING.DAILY_HOURS
        ADMINISTRATION.ACCOUNTING.DEPARTMENTS
        .
        .
        .

   Example 2: Setting a default catalog for a database with an alias

   In this example, the user attaches to the multischema corporate_
   data database using the alias CORP. Setting the default catalog
   allows you to shorten the table name because you can qualify it
   with just the schema.

   SQL> ATTACH 'ALIAS CORP FILENAME corporate_data';
   SQL> CREATE TABLE ACCOUNTING.PROJECT_7 (STATUS REAL);
   %SQL-F-DBHANDUNK, ACCOUNTING is not the alias of a known database
   SQL> --
   SQL> -- You cannot qualify the table name without the alias,
   SQL> -- so SQL assumes ACCOUNTING is the alias, not the schema.
   SQL> -- Unless you want to qualify the table name with
   SQL> -- both alias and catalog names, you must set the
   SQL> -- default catalog to ADMINISTRATION, which
   SQL> -- contains ACCOUNTING. You must enable ANSI/ISO quoting to do this.
   SQL> --
   SQL> SET QUOTING RULES 'SQL92';
   SQL> SET CATALOG '"CORP.ADMINISTRATION"';
   SQL> CREATE TABLE ACCOUNTING.PROJECT_7 (STATUS REAL);
   SQL> SHOW TABLES;
   User tables in database with filename corporate_data
        ACCOUNTING.BUDGET
      .
      .
      .
        ACCOUNTING.PROJECT_7
        ACCOUNTING.WORK_STATUS
      .
      .
      .