Alters a domain definition.
The ALTER DOMAIN statement lets you change the character set,
data type, optional default value, optional collating sequence,
or optional formatting clauses associated with a domain name. Any
table or view definitions that refer to that domain reflect the
changes.
1 – Environment
You can use the ALTER DOMAIN 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
ALTER DOMAIN --> <domain-name> +------------------+---+
+-> IS data-type --+ |
+----------------------------------------------------+
+-+-------------------------------+------------------+
+-> SET DEFAULT value-expr -----+ |
+-> DROP DEFAULT ---------------+ |
+----------------------------------------------------+
+-+---------------------------------------------+-+
+---> COLLATING SEQUENCE IS <collation-name> -+ |
+---> NO COLLATING SEQUENCE ------------------+ |
+-------------------------------------------------+
+--+-----------------------+-+------------------------+->
+-> domain-constraint --+ ++> sql-and-dtr-clause -++
+----------<-----------+
data-type =
-+-> char-data-types -----------------------------------------+-->
+-> TINYINT --------------+-----+------------+---------------+
+-> SMALLINT -------------+ +-> ( <n> ) -+ |
+-> INTEGER --------------+ |
+-> BIGINT ---------------+ |
+-> FLOAT ----------------+ |
+-> NUMBER -+----------------------------------+-------------+
| +-> ( -+-> <p> -+-+----------+-> ) + |
| +-> * ---+ +-> , <d> -+ |
+-> LIST OF BYTE VARYING --+------------+--+--------------+--+
| +-> ( <n> ) -+ +-> AS BINARY -+ |
| +-> AS TEXT ---+ |
+-> DECIMAL -++------------------------------+---------------+
+-> NUMERIC -++-> ( --> <n> +----------+-> ) + |
| +-> , <n> -+ |
+-> REAL ----------------------------------------------------+
+-> DOUBLE PRECISION ----------------------------------------+
+-> date-time-data-types ------------------------------------+
char-data-types =
-+-> CHAR -------------++------------++--------------------------------+-+->
+-> CHARACTER --------++-> ( <n> ) -++-> CHARACTER SET char-set-name -+ |
+-> CHAR VARYING -----+ |
+-> CHARACTER VARYING + |
+-> VARCHAR --+> ( <n> ) ---+--------------------------------+----------+
+-> VARCHAR2 -+ +-> CHARACTER SET char-set-name -+ |
+-> LONG VARCHAR ------------------------------------------------------+
+-> NCHAR --------------+-+------------+--------------------------------+
+-> NATIONAL CHAR ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER -+ |
+-> NCHAR VARYING --------------+-+------------+------------------------+
+-> NATIONAL CHAR VARYING ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER VARYING -+ |
+-> RAW -> ( <n> ) -----------------------------------------------------+
+-> LONG -+--------+----------------------------------------------------+
+-> RAW -+
date-time-data-types =
--+-> DATE -+----------+-----------------+-->
| +-> ANSI -+ |
| +-> VMS ---+ |
+-> TIME ---> frac --------------------+
+-> TIMESTAMP --> frac ----------------+
+-> INTERVAL ---> interval-qualifier --+
literal =
--+-> numeric-literal ----+--->
+-> string-literal -----+
+-> date-time-literal --+
+-> interval-literal ---+
domain-constraint =
--+---------------------------------------------+->
+-> ADD CHECK ( predicate ) NOT DEFERRABLE --+
+-> DROP ALL CONSTRAINTS ---------------------+
sql-and-dtr-clause =
-+-> QUERY HEADER IS -+> <quoted-string> +------------------+->
| +------ / <--------+ |
+-> EDIT STRING IS <quoted-string> ------------------------+
| |
+-> QUERY NAME FOR -+-> DTR --------+-> IS <quoted-string> +
| +-> DATATRIEVE -+ |
+-> DEFAULT VALUE FOR -+-> DTR --------+-> IS <literal> ---+
| +-> DATATRIEVE -+ |
+-> NO QUERY HEADER ---------------------------------------+
+-> NO EDIT STRING ----------------------------------------+
+-> NO QUERY NAME ----+--> FOR -+-> DTR --------+----------+
+-> NO DEFAULT VALUE -+ +-> DATATRIEVE -+ |
+-> COMMENT IS -+-> <quoted-string> -+---------------------+
| +------ / <----------+ |
+-> RENAME TO <new-name> ----------------------------------+
3 – Arguments
3.1 – char-data-types
A valid SQL character data type. For more information on
character data types, see the Data_Types HELP topic.
3.2 – character-set-name
A valid character set name. For a list of allowable character set
names, see the Supported_Character_Sets HELP topic.
3.3 – COLLATING_SEQUENCE
Specifies a new collating sequence for the named domain.
The OpenVMS National Character Set (NCS) utility provides a
set of predefined collating sequences and also lets you define
collating sequences of your own. The COLLATING SEQUENCE clause
accepts both predefined and user-defined NCS collating sequences.
Before you use the COLLATING SEQUENCE clause in an ALTER DOMAIN
statement, you must first specify the NCS collating sequence for
SQL using the CREATE COLLATING SEQUENCE statement. The sequence
name argument in the COLLATING SEQUENCE clause must be the same
as the sequence name in the CREATE COLLATING SEQUENCE statement.
3.4 – COMMENT_IS
Adds a comment about the domain. SQL displays the text of the
comment when it executes a SHOW DOMAIN statement. Enclose the
comment in single quotation marks ( ') and separate multiple
lines in a comment with a slash mark (/).
3.5 – date-time-data-types
A data type that specifies a date, time, or interval. For more
information on date-time data types, see the Data_Types HELP
topic.
3.6 – DEFAULT value-expr
Provides a default value for a domain.
You can use any value expression including subqueries,
conditional, character, date/time, and numeric expressions as
default values. See Value Expressions for more information about
value expressions.
For more information about NULL, see the NULL_Keyword HELP topic.
The value expressions described in Value Expressions include
DBKEY and aggregate functions. However, the DEFAULT clause is
not a valid location for referencing a DBKEY or an aggregate
function. If you attempt to reference either, you receive a
compile-time error.
If you do not specify a DEFAULT for a column, it inherits the
DEFAULT from the domain. If you do not specify a default for
either the column or domain, SQL assigns NULL as the default
value.
3.7 – domain-constraint
Adds or modifies a constraint for the existing named domain.
Domain constraints specify that columns based on the domain
contain only certain data values or that data values can or
cannot be null.
Use the CHECK clause to specify that a value must be within a
specified range or that it matches a list of values. When you
specify a CHECK clause for a domain constraint, you ensure that
all values stored in columns based on the domain are checked
consistently.
To refer to the values of all columns of a domain constraint, use
the VALUE keyword. For example:
SQL> CREATE DOMAIN dom1 CHAR(1)
cont> CHECK (VALUE IN ('F','M'))
cont> NOT DEFERRABLE;
For any dialect other than SQL99, SQL92, ORACLE LEVEL 1 or
ORACLE LEVEL 2, you must specify that domain constraints are
NOT DEFERRABLE.
When you add (or modify) a domain constraint, SQL propagates
the new constraint definition to all the columns that are based
on the domain. If columns that are based on the domain contain
data that does not conform to the constraint, SQL returns the
following error:
%RDB-E-NOT_VALID, validation on field DATE_COL caused operation to fail
3.8 – domain-name
The name of a domain you want to alter. The domain name must be
unique among domain names in the database.
3.9 – DROP_DEFAULT
Deletes (drops) the default value of a domain.
3.10 – IS datatype
A valid SQL data type. For more information on data types, see
the Data_Types HELP topic.
3.11 – NO_COLLATING_SEQUENCE
Specifies that the named domain uses the standard default
collating sequence, that is, ASCII. Use the NO COLLATING SEQUENCE
clause to override the collating sequence defined for the schema
in the CREATE SCHEMA or ALTER SCHEMA statement, or the domain in
the CREATE DOMAIN statement.
3.12 – RENAME_TO
Changes the name of the domain being altered. See the RENAME for
further discussion. If the new name is the name of a synonym then
an error will be raised.
The RENAME TO clause requires synonyms be enabled for this
database. Refer to the ALTER DATABASE statement SYNONYMS ARE
ENABLED clause. Note that these synonyms may be deleted if they
are no longer used by database definitions or applications.
3.13 – SET_DEFAULT
Provides a default value for a column if the row that is inserted
does not include a value for that column. A column default
value overrides a domain default value. If you do not specify
a default value, SQL assigns NULL as the default value. For more
information about NULL, see the NULL_Keyword HELP topic.
3.14 – sql-and-dtr-clause
Optional SQL and DATATRIEVE formatting clause. For more
information on the formatting clauses, see the DATATRIEVE HELP
topic.
3.15 – value-expr
Specifies the default value of a domain.
4 – Examples
Example 1: Altering the domain POSTAL_CODE_DOM
This example alters the domain POSTAL_CODE_DOM so that it
accommodates longer postal codes:
SQL> --
SQL> -- The data type of the current domain POSTAL_CODE_DOM is CHAR(5):
SQL> --
SQL> SHOW DOMAIN POSTAL_CODE_DOM
POSTAL_CODE_DOM CHAR(5)
Comment: standard definition of ZIP
Rdb default:
SQL> --
SQL> -- Now, alter the domain to accommodate larger postal codes:
SQL> --
SQL> ALTER DOMAIN POSTAL_CODE_DOM IS CHAR(10);
SQL> --
SQL> -- The SHOW TABLES statement shows how changing the
SQL> -- domain POSTAL_CODE_DOM changes all the
SQL> -- columns that were created using the domain:
SQL> --
SQL> SHOW TABLE COLLEGES
Information for table COLLEGES
Comment on table COLLEGES:
names and addresses of colleges attended by employees
Columns for table COLLEGES:
Column Name Data Type Domain
----------- --------- ------
.
.
.
POSTAL_CODE CHAR(10) POSTAL_CODE_DOM
.
.
.
SQL> SHOW TABLE EMPLOYEES
Information for table EMPLOYEES
Comment on table EMPLOYEES:
personal information about each employee
Columns for table EMPLOYEES:
Column Name Data Type Domain
----------- --------- ------
.
.
.
POSTAL_CODE CHAR(10) POSTAL_CODE_DOM
Example 2: Altering the domain ID_DOM
The following example alters the data type for the domain ID_DOM,
which is a standard definition of the employee identification
field.
In Example 1, there were no indexes based on the domain POSTAL_
CODE_DOM. In this example, several indexes that refer to columns
were created based on ID_DOM. As the following example shows, you
must first delete the indexes before altering the domain:
SQL> -- The data type for the domain ID_DOM is CHAR(5):
SQL> --
SQL> SHOW DOMAIN ID_DOM
ID_DOM CHAR(5)
Comment: standard definition of employee id
SQL> --
SQL> -- The first attempt to alter the domain ID_DOM fails.
SQL> -- You must first delete all constraints that use the
SQL> -- field EMPLOYEE_ID.
SQL> --
SQL> ALTER DOMAIN ID_DOM CHAR(6);
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-FLDINCON, field EMPLOYEE_ID is referenced in constraint
RESUMES_FOREIGN1
-RDMS-F-FLDNOTCHG, field EMPLOYEE_ID has not been changed
SQL> ALTER TABLE RESUMES DROP CONSTRAINT RESUMES_FOREIGN1;
SQL> --
SQL> ALTER DOMAIN ID_DOM IS CHAR(6);
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-FLDINCON, field EMPLOYEE_ID is referenced in constraint
DEGREES_FOREIGN1
-RDMS-F-FLDNOTCHG, field EMPLOYEE_ID has not been changed
SQL> --
SQL> ALTER TABLE DEGREES DROP CONSTRAINT DEGREES_FOREIGN1;
.
.
.
SQL> -- You must then delete all indexes.
SQL> --
SQL> ALTER DOMAIN ID_DOM IS CHAR(6);
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-FLDINUSE, field EMPLOYEE_ID is referenced in index EMP_EMPLOYEE_ID
-RDMS-F-FLDNOTCHG, field EMPLOYEE_ID has not been changed
SQL> --
SQL> DROP INDEX EMP_EMPLOYEE_ID;
SQL> --
SQL> ALTER DOMAIN ID_DOM IS CHAR(6);
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-FLDINUSE, field EMPLOYEE_ID is referenced in index JH_EMPLOYEE_ID
-RDMS-F-FLDNOTCHG, field EMPLOYEE_ID has not been changed
SQL> --
SQL> DROP INDEX JH_EMPLOYEE_ID;
SQL> --
.
.
.
SQL> --
SQL> -- You can now alter the domain.
SQL> --
SQL> ALTER DOMAIN ID_DOM IS CHAR(6);
SQL> SHOW DOMAIN ID_DOM;
ID_DOM CHAR(6)
Comment: standard definition of employee id
Example 3: Specifying default values with the ALTER DOMAIN
statement
The following example alters domains, specifying default values
for those domains:
SQL> -- If no date is entered, use the NULL default.
SQL> --
SQL> ALTER DOMAIN DATE_DOM
cont> SET DEFAULT NULL;
SQL> --
SQL> -- If the street address takes only one line,
SQL> -- use "NONE" for the default for the second line.
SQL> --
SQL> ALTER DOMAIN ADDRESS_DATA_2_DOM
cont> SET DEFAULT 'NONE';
SQL> --
SQL> -- If most employees work full-time, make the code
SQL> -- for full-time, 1, the default work status.
SQL> --
SQL> ALTER DOMAIN STATUS_CODE_DOM
cont> SET DEFAULT '1';
Example 4: Specifying an edit string with the ALTER DOMAIN
statement
The following example specifies an EDIT STRING clause that
controls how SQL displays columns based on the domain MIDDLE_
INITIAL_DOM. The edit string in the example, "X.?'No middle
initial'", specifies that columns based on the domain are
displayed as one character followed by a period. If there is
no value for the column, SQL displays the string following the
question mark, 'No middle initial'.
SQL> ALTER DOMAIN MIDDLE_INITIAL_DOM
cont> EDIT STRING 'X.?''No middle initial';
SQL> SELECT MIDDLE_INITIAL FROM EMPLOYEES;
MIDDLE_INITIAL
A.
D.
No middle initial
No middle initial
.
.
.
Example 5: Specifying a new collating sequence with the ALTER
DOMAIN statement
The following example creates a domain with the predefined NCS
collating sequence FRENCH. You must first execute the CREATE
COLLATING SEQUENCE statement. The example then changes the
collating sequence to Finnish, and then specifies that the domain
has no collating sequence.
SQL> CREATE COLLATING SEQUENCE FRENCH FRENCH;
SQL> CREATE DOMAIN LAST_NAME_ALTER_TEST CHAR (10)-
cont> COLLATING SEQUENCE IS FRENCH;
SQL> --
SQL> SHOW DOMAIN LAST_NAME_ALTER_TEST
LAST_NAME_ALTER_TEST CHAR(10)
Collating sequence: FRENCH
SQL> --
SQL> -- Now, change the collating sequence to Finnish. You must first
SQL> -- execute the CREATE COLLATING SEQUENCE statement.
SQL> --
SQL> CREATE COLLATING SEQUENCE FINNISH FINNISH;
SQL> ALTER DOMAIN LAST_NAME_ALTER_TEST CHAR (10)-
cont> COLLATING SEQUENCE IS FINNISH;
SQL> --
SQL> SHOW DOMAIN LAST_NAME_ALTER_TEST
LAST_NAME_ALTER_TEST CHAR(10)
Collating sequence: FINNISH
SQL> --
SQL> -- Now, alter the domain so there is no collating sequence.
SQL> --
SQL> ALTER DOMAIN LAST_NAME_ALTER_TEST CHAR (10)-
cont> NO COLLATING SEQUENCE;
SQL>
SQL> SHOW DOMAIN LAST_NAME_ALTER_TEST
LAST_NAME_ALTER_TEST CHAR(10)
Assume the following for Examples 6 and 7:
o The database was created specifying the database default
character set as DEC_KANJI and the national character set
as KANJI.
o The domain DEC_KANJI_DOM was created specifying the database
default character set.
o The table COLOURS was created assigning the DEC_KANJI_DOM
domain to the column ROMAJI.
Example 6: Altering the domain DEC_KANJI_DOM
SQL> SET CHARACTER LENGTH 'CHARACTERS';
SQL> SHOW DOMAIN DEC_KANJI_DOM;
DEC_KANJI_DOM CHAR(8)
SQL> ALTER DOMAIN DEC_KANJI_DOM NCHAR(8);
SQL> SHOW DOMAIN DEC_KANJI_DOM;
DEC_KANJI_DOM CHAR(8)
KANJI 8 Characters, 16 Octets
SQL>
Example 7: Error altering a domain used in a table containing
data
In the following example, the column ROMAJI is based on the
domain DEC_KANJI_DOM. If the column ROMAJI contains data before
you alter the character set of the domain, SQL displays the
following error when you try to retrieve data after altering
the domain.
SQL> SELECT ROMAJI FROM COLOURS;
%RDB-F-CONVERT_ERROR, invalid or unsupported data conversion
-RDMS-E-CSETBADASSIGN, incompatible character sets prohibits the requested
assignment
SQL> --
SQL> -- To recover, use the ROLLBACK statement or reset the character set to
SQL> -- its original value.
SQL> --
SQL>ROLLBACK;
SQL> SELECT ROMAJI FROM COLOURS;
ROMAJI
kuro
shiro
ao
aka
ki
midori
6 rows selected
SQL>
Example 8: Modifying a domain constraint
The following example shows how to modify an existing constraint
on a domain:
SQL> SHOW DOMAIN TEST_DOM
TEST_DOM DATE ANSI
Rdb default: NULL
VALID IF: (VALUE > DATE'1900-01-01' OR
VALUE IS NULL)
SQL> --
SQL> -- Add the new domain constraint definition.
SQL> --
SQL> ALTER DOMAIN TEST_DOM
cont> ADD CHECK (VALUE > DATE'1985-01-01')
cont> NOT DEFERRABLE;
Example 9: Creating stored procedure domain dependencies
The following code fragment from a stored module shows a domain
in a parameter list and a domain in a stored procedure block:
SQL> create module SAMPLE
cont> procedure FIRST_NAME
cont> (in :id id_dom
cont> ,out :first_name char(40));
cont> begin
cont> declare :fn first_name_dom;
cont> select first_name into :fn
cont> from employees
cont> where employee_id = :id;
cont> -- return capitalized first name
cont> set :first_name =
cont> UPPER (substring (:fn from 1 for 1)) ||
cont> LOWER (substring (:fn from 2));
cont> end;
cont> end module;
SQL>
SQL> declare :first_name first_name_dom;
SQL> call FIRST_NAME ('00164', :first_name);
FIRST_NAME
Alvin
SQL>
SQL> alter domain id_dom
cont> char(10);
%RDB-E-NO_META_UPDATE, metadata update failed
-RDMS-F-RTNEXI, field "ID_DOM" is used in routine "FIRST_NAME"
-RDMS-F-FLDNOTCHG, field ID_DOM has not been changed
SQL>
SQL> alter domain first_name_dom
cont> char(60);
o Domain specified in a parameter list
When you specify a domain in a parameter list (id_number)
of a stored routine and you subsequently try to alter that
domain, the ALTER DOMAIN statement fails because SQL sets up a
dependency between the domain and the stored routine in which
the domain resides. Because the statement fails, Oracle Rdb
does not invalidate the stored routine. Oracle Rdb keeps this
domain parameter list dependency in RDB$PARAMETERS.
o Domain specified in a stored routine block
When you specify a domain (last_name) within a stored routine
block and you subsequently try to alter that domain, the ALTER
DOMAIN statement succeeds. Future calls to the stored routine
will use the new definition of the domain.
Example 10: Altering a Domain to Provide a Default Value
This examples demonstrates that the default value added to the
domain is propagated to the tables using that domain.
SQL> -- Display the current domain definition.
SQL> SHOW DOMAIN DEPARTMENT_NAME
DEPARTMENT_NAME CHAR(30)
Comment: Department name
Missing Value: None
SQL> -- Alter the domain to provide a default value
SQL> -- for DEPARTMENT_NAME.
SQL> ALTER DOMAIN DEPARTMENT_NAME
cont> SET DEFAULT 'Not Recorded';
SQL> -- Display the altered domain definition.
SQL> SHOW DOMAIN DEPARTMENT_NAME;
DEPARTMENT_NAME CHAR(30)
Comment: Department name
Oracle Rdb default: Not Recorded
Missing Value: None
SQL> -- Insert a record and omit the value for DEPARTMENT_NAME.
SQL> INSERT INTO DEPARTMENTS (DEPARTMENT_CODE)
cont> VALUES
cont> ('GOGO');
1 row inserted
SQL> COMMIT;
SQL> -- Select the newly inserted record to show that the
SQL> -- default for the DEPARTMENT_NAME domain was inserted.
SQL> SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_CODE='GOGO';
DEPARTMENT_CODE DEPARTMENT_NAME MANAGER_ID
BUDGET_PROJECTED BUDGET_ACTUAL
GOGO Not Recorded NULL
NULL NULL
1 row selected