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.
1 – 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
(B)0[m[1;4mSET[m[1m [1;4mCATALOG[m[1m qqqwq> <catalog-string-literal> qqqwq> [m [1m tq> <catalog-parameter> qqqqqqqqu [m [1m mq> <catalog-parameter-marker> qj [m (B)0[m[1mcatalog-string-literal = [m [1m [m [1mqq> ' qq> catalog-expression qq> ' qq> [m [1m [m (B)0[m[1mcatalog-expression = [m [1m [m [1m [m [1m [m [1mqwqqqqqq> <name-of-catalog> qqqqqqqqqqqqqqqwqq> [m [1m x [m [1m x [m [1m mq> " q> <alias>.<name-of-catalog> qq> " qj [m
3 – Arguments
3.1 – 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.2 – 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.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.4 – 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.
4 – 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
.
.
.