SQL$HELP72.HLB  —  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.

1  –  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 

  (B)0SET SCHEMA qqqwq> schema-string-literal qqqqqqwq>  
                tq> <schema-parameter> qqqqqqqqqu    
                mq> <schema-parameter-marker> qqj    

  (B)0schema-string-literal =               
                                        
  qq> ' qq> schema-expression qq> ' qq> 
                                        

  (B)0schema-expression  =                                 
                                                    
  qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk  
    tqqqqqq> <catalog-name> qqqqqqqqqqqqwq> . qj x  
    tq> " q> <alias>.<catalog-name> q>"qj        x  
    x   lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  
    x   mqqqqqqqqqqqqqq> <name-of-schema> qqqqqqwq> 
    mq> " q> <alias>.<name-of-schema> qq> " qqqqj   
                                                    

3  –  Arguments

3.1  –  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.2  –  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.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.4  –  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.

4  –  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
         .
         .
         .
Close Help