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