22.4. Datatypes

This section covers each of the XML Schema built-in datatypes and provides design issues, implementation considerations, and warnings when implementing an XML Schema datatype as a database column datatype. Both check constraints and triggers are listed, when appropriate, as both implementations are applicable in many cases.

22.4.1. string Datatype

The string datatype in XML Schema is defined to support, at a minimum, UTF-8 and UTF-16 representations. Oracle9i is the first version of the Oracle database to fully support these representations (Oracle8i has partial support for UTF-8). Oracle9i also supports 8-bit character sets such as ISO-Latin-8859-1 that might be appropriate if full international support is not required. Oraclea9i supports the following character column datatypes (each with a maximum length):

• CHAR: 2000 characters

• NCHAR: 2000 bytes

• VARCHAR2: 4000 characters

• NVARCHAR2: 4000 bytes

• CLOB: 4 gigabytes

• NCLOB: 4 gigabytes

in which the “N” versions of the character column datatypes are used for UTF-8 and UTF-16 representations. Depending on the maximum length specified and whether the text is of a variable length, different character datatypes should be used.

22.4.1.1. CLOB Support in Oracle

A CLOB should not be used lightly, because a VARCHAR2 is more space- and time-efficient. However, CLOB support in Oracle9i has greatly increased over Oracle8i. The PL/SQL functions in Oracle8i that support VARCHAR2 now support a CLOB as well. Unfortunately, PL/SQL limits the size of character variables to 32,767 characters or fewer. Above this size, DBMS_LOB package functions must be used. Table 22.1 lists the mapping when DBMS_LOB package functions must be used.

Implementations for the minLength constraining facet in this section cover both sets of functions.

Finally, the use of a before trigger for a CLOB is disallowed by Oracle because the :new value is not available. In addition, table check constraints are not allowed. As a result, using an after trigger forces application developers to roll back the insert or update statement manually.

22.4.1.2. length Constraining Facet

The database CHAR or NCHAR column datatypes are appropriate if there is a length constraining facet. Unlike their variable equivalents, space for these database column datatypes is allocated on a fixed basis rather than on a variable basis.

There is no built-in database support for the length constraining facet, but this can be added as a column constraint in table creation or as a trigger. See Section 22.4.1.3 for details.

22.4.1.3. minLength Constraining Facet

There is no built-in database support for the minLength constraining facet, but this can be added as a column constraint in table creation or as a trigger. Using the VARCHAR2 column datatype, Listing 22.7 implements this as a column constraint, and Listing 22.8 implements this as a trigger. Listing 22.9 implements the minLength, using a CLOB column datatype. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.7. StringExample Table Creation with a Column Constraint by Using VARCHAR2

CREATE TABLE StringExample ( 
value VARCHAR2(30) CHECK (LENGTH(value) >= 5) 
); 

Listing 22.8. StringExample Table Creation with Insert and Update Triggers by Using VARCHAR2

CREATE TABLE StringExample ( 
value VARCHAR2(30) 
); 

CREATE OR REPLACE TRIGGER InsertStringExample 
BEFORE INSERT 
ON StringExample 
FOR EACH ROW 
WHEN (LENGTH(new.value) < 5) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'String too small'), 
END; 

CREATE OR REPLACE TRIGGER UpdateStringExample 
BEFORE UPDATE 
ON StringExample 
FOR EACH ROW 
WHEN (LENGTH(new.value) < 5) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'String too small'), 

END; 

Listing 22.9. CLOBStringExample Table Creation with Insert and Update Triggers by Using a CLOB

CREATE TABLE CLOBStringExample ( 
value CLOB 
);  
CREATE OR REPLACE TRIGGER InsertCLOBStringExample 
AFTER INSERT 
ON CLOBStringExample 
FOR EACH ROW 
BEGIN 
IF DBMS_LOB.GETLENGTH(:new.value) < 5 THEN 
 RAISE_APPLICATION_ERROR(-20000, 'String too small'), 
END IF; 
END; 

CREATE OR REPLACE TRIGGER UpdateCLOBStringExample 
BEFORE UPDATE 
ON CLOBStringExample 
FOR EACH ROW 
BEGIN 
IF DBMS_LOB.GETLENGTH(:new.value) < 5 THEN 
 RAISE_APPLICATION_ERROR(-20000, 'String too small'), 
END IF; 
END; 

22.4.1.4. maxLength Constraining Facet

This facet is directly supported in the database because the maximum character length is specified at column creation. If the XML schema designer did not place a maxLength constraining facet, it is best to make the designer do so. If this is not possible, it is best to determine whether 4,000 characters/bytes are enough or whether a CLOB is required.

22.4.1.5. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.1.6. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.1.7. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.2. normalizedString Datatype

The normalizedString datatype is derived from string by restricting the allowable characters. To validate that a string is a normalizedString, a constraint or trigger may be created. The normalizedString datatype may not contain a carriage return (#xD), line feed (#xA), or tab (#x9). Listing 22.10 implements this datatype as a column constraint, and Listing 22.11 implements this as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.10. NormalizedStringExample Table Creation with a Column Constraint

CREATE TABLE NormalizedStringExample ( 
value VARCHAR2(30) 
    CHECK (INSTR(value, UNISTR('00D')) = 0 AND 
        INSTR(value, UNISTR('00A')) = 0 AND 
        INSTR(value, UNISTR('009')) = 0) 
); 

Listing 22.11. NormalizedStringExample Table Creation with Insert and Update Triggers

CREATE TABLE NormalizedStringExample ( 
value VARCHAR2(30) 
); 

CREATE OR REPLACE TRIGGER InsertNormalizedStringExample 
BEFORE INSERT 
ON NormalizedStringExample 
FOR EACH ROW 
WHEN (INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not normalizedString'), 
END; 

CREATE OR REPLACE TRIGGER UpdateNormalizedStringExample 
BEFORE UPDATE 
ON NormalizedStringExample 
FOR EACH ROW 
WHEN (INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not normalizedString'), 
END; 

22.4.2.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.2.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.2.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.2.4. length Constraining Facet

Refer to Section 22.4.1.2 for design and implementation issues.

22.4.2.5. minLength Constraining Facet

Refer to Section 22.4.1.3 for design and implementation issues.

22.4.2.6. maxLength Constraining Facet

Refer to Section 22.4.1.4 for design and implementation issues.

22.4.3. token Datatype

The token datatype is derived from string by restricting the allowable characters. To validate that a string is a token, a constraint or trigger may be created. The token datatype may not contain a carriage return (#xD), line feed (#xA), tab (#x9), two space characters in a row, or spaces surrounding the token. Listing 22.12 implements this datatype as a column constraint, and Listing 22.13 implements this as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.12. TokenExample Table Creation with a Column Constraint

CREATE TABLE TokenExample ( 
value VARCHAR2(30) 
   CHECK (INSTR(value, UNISTR('00D')) = 0 AND 
       INSTR(value, UNISTR('00A')) = 0 AND 
       INSTR(value, UNISTR('009')) = 0 AND 
       LENGTH(value) = LENGTH(TRIM(value)) AND 
       INSTR(value, ' ') = 0) 
); 

Listing 22.13. TokenExample Table Creation with Insert and Update Triggers

CREATE TABLE TokenExample ( 
value VARCHAR2(30) 
); 

CREATE OR REPLACE TRIGGER InsertTokenExample 
BEFORE INSERT 
ON TokenExample 
FOR EACH ROW 
WHEN (INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN   
RAISE_APPLICATION_ERROR(-20000, 'Not token'), 
END; 

CREATE OR REPLACE TRIGGER UpdateTokenExample 
BEFORE UPDATE 
ON TokenExample 
FOR EACH ROW 
WHEN (INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not token'), 
END; 

22.4.3.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.3.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.3.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.3.4. length Constraining Facet

Refer to Section 22.4.1.2 for design and implementation issues.

22.4.3.5. minLength Constraining Facet

Refer to Section 22.4.1.3 for design and implementation issues.

22.4.3.6. maxLength Constraining Facet

Refer to Section 22.4.1.4 for design and implementation issues.

22.4.4. language Datatype

Restricting a database schema to valid language identifiers requires some work to set up and maintain. Refer to RFC 1766: Tags for the Identification of Languages 1995, http://www.ietf.org/rfc/rfc1766.txt for specific details. As a quick summary, a langcode may be any of the following:

• A two-letter language code as defined by ISO 639.

• A language identifier beginning with the prefix ‘i-’(or ‘I-’); these are registered with the Internet Assigned Numbers Authority (IANA).

• A country code beginning with the prefixes as defined by ISO 3166.

• A language identifier beginning with the prefix ‘x-’ (or ‘X-’); these are assigned for private use.

For more complete, accurate, and up-to-date information, review the RFC.

To partially ensure that valid language values are the only ones used, the values from ISO 639, the IANA, and ISO 3166 must be inserted into the database and checked for each entry. Because these are enumerations that will change over time, a picklist implementation is used. Because the constraints listed for languages are too complex for check constraints, triggers will be created instead.

Listing 22.14 adds the language code domains to the PicklistDomain table and some of the entries required to the Picklist table. Listing 22.15 creates a contrived table that uses the language domains with triggers to enforce the XML Recommendation, Extensible Markup Language (XML) 1.0 (Second Edition) on languages.

Listing 22.14. PicklistDomain and Picklist Table Entries for Language Codes

INSERT INTO PicklistDomain 
(picklistDomainID, name, description) 
VALUES 
(2, 'TwoLanguageLetterCodes', 
'The two-letter language code as defined by [ISO 639],[ccc] 
Codes for the representation of names of languages'), 

INSERT INTO PicklistDomain 
(picklistDomainID, name, description) 
VALUES 
(3, 'IANACodes', 
'A language identifier registered with the Internet Assigned [ccc] 
Numbers Authority [IANA]; these begin with the prefix i- (or I-)'), 

INSERT INTO PicklistDomain 
(picklistDomainID, name, description) 
VALUES 
(4, 'TwoLanguageCountryCodes', 
'The country code from [ISO 3166], [ccc] 
Codes for the representation of names of countries.'), 

INSERT INTO Picklist 
(picklistID, picklistDomainID, name, description) 
VALUES 
(5, 2, 'en', 'English'), 

INSERT INTO Picklist 
(picklistID, picklistDomainID, name, description) 
VALUES 
(6, 3, 'I-', ''),   
INSERT INTO Picklist 
(picklistID, picklistDomainID, name, description) 
VALUES 
(7, 4, 'US', 'United States'), 

INSERT INTO Picklist 
(picklistID, picklistDomainID, name, description) 
VALUES 
(8, 4, 'GB', 'Great Britain'), 
COMMIT; 

Listing 22.15. Use of the Language Domains in a Table with Trigger Enforcement

CREATE TABLE LanguageExample ( 
value VARCHAR2(255) 
); 

CREATE OR REPLACE TRIGGER InsertLanguageExample 
BEFORE INSERT 
ON LanguageExample 
FOR EACH ROW 
DECLARE 
 I PLS_INTEGER; 
BEGIN 
I := 0; 

SELECT COUNT(1) 
INTO I 
FROM Picklist TwoLanguageLetterCodes, Picklist IANACodes, 
    Picklist TwoLanguageCountryCodes 
WHERE --Match up the appropriate picklist domains to get 
    --the right values. 
    TwoLanguageLetterCodes.picklistDomainID = 2 AND 
    IANACodes.picklistDomainID = 3 AND 
    TwoLanguageCountryCodes.picklistDomainID = 4 AND 
    (TwoLanguageLetterCodes.name = :new.value OR 
    IANACodes.name = :new.value OR 
    -- The language is private and starts with X- or x-
    INSTR(UPPER(:new.value), 'X-') = 1 OR 
     -- The language is more than two characters and 
     -- therefore must be followed by a country code 
     -- subcode. 
    (TwoLanguageLetterCodes.name = 
       SUBSTR(:new.value, 1, 2) AND 
     TwoLanguageCountryCodes.name = 
       SUBSTR(:new.value, 4, 2))); 
IF I = 0 THEN  
 RAISE_APPLICATION_ERROR(-20000, 'Not a language'), 
END IF; 
END; 

CREATE OR REPLACE TRIGGER UpdateLanguageExample BEFORE INSERT 
ON LanguageExample 
FOR EACH ROW 
WHEN (new.value <> old.value) 
DECLARE 
I PLS_INTEGER; 
BEGIN 
SELECT COUNT(1) 
INTO I 
FROM Picklist TwoLanguageLetterCodes, Picklist IANACodes, 
    Picklist TwoLanguageCountryCodes 
WHERE --Match up the appropriate picklist domains to get 
    --the right values. 
    TwoLanguageLetterCodes.picklistDomainID = 2 AND 
    IANACodes.picklistDomainID = 3 AND 
    TwoLanguageCountryCodes.picklistDomainID = 4 AND 
    (TwoLanguageLetterCodes.name = :new.value OR 
    IANACodes.name = :new.value OR 
    -- The language is private and starts with X- or x-
    INSTR(UPPER(:new.value), 'X-') = 1 OR 
     -- The language is more than two characters and 
     -- therefore must be followed by a country code 
     -- subcode. 
    (TwoLanguageLetterCodes.name = 
      SUBSTR(:new.value, 1, 2) AND 
     TwoLanguageCountryCodes.name = 
      SUBSTR(:new.value, 4, 2))); 
IF I = 0 THEN 
 RAISE_APPLICATION_ERROR(-20000, 'Not a language'), 
END IF; 
END; 

The two triggers could be refactored to call the same stored procedure.

22.4.4.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.4.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.4.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.4.4. length Constraining Facet

Refer to Section 22.4.1.2 for design and implementation issues.

22.4.4.5. minLength Constraining Facet

Refer to Section 22.4.1.3 for design and implementation issues.

22.4.4.6. maxLength Constraining Facet

Refer to Section 22.4.1.4 for design and implementation issues.

22.4.5. Name Datatype

The Name datatype is derived from token by restricting the first character to a letter or underscore or colon. Unfortunately, the allowable characters are over 250 ranges of Unicode characters that would be tedious to implement in Oracle9i.

An alternative is to determine the characters in the Name datatype required for your application, and use this set for compliance checking. If this set is small, enforcing this datatype is easier to implement. For example, if the character set is United States English, the list of allowable characters is ‘A’ to ‘Z’, ‘0’ to ‘9’, underscore, colon, period, and ASCII hyphen. Listing 22.16 implements this datatype as a column constraint, and Listing 22.17 implements this as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.16. NameExample Table Creation with a Column Constraint

CREATE TABLE NameExample ( 
value VARCHAR2(30) 
    CHECK (LENGTH(TRANSLATE(UPPER(value), 
        '_:.-ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=', 
        '_:.-ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) 
      = LENGTH(value) AND 
     (UPPER(SUBSTR(value, 1, 1)) 
       BETWEEN 'A' AND 'Z' OR 
      SUBSTR(value, 1, 1) IN ('_', ':')) AND 
     INSTR(value, UNISTR('00D')) = 0 AND 
     INSTR(value, UNISTR('00A')) = 0 AND 
     INSTR(value, UNISTR('009')) = 0 AND 
     LENGTH(value) = LENGTH(TRIM(value)) AND 
     INSTR(value, ' ') = 0) 
); 

Listing 22.17. NameExample Table Creation with Insert and Update Triggers

CREATE TABLE NameExample ( 
value VARCHAR2(30) 
);  
CREATE OR REPLACE TRIGGER InsertNameExample 
BEFORE INSERT 
ON NameExample 
FOR EACH ROW 
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '_:.-ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=', 
      '_:.-ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR 
  NOT (UPPER(SUBSTR(new.value, 1, 1)) 
    BETWEEN 'A' AND 'Z' OR 
  SUBSTR(new.value, 1, 1) IN ('_', ':')) OR 
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not Name'), 
END; 

CREATE OR REPLACE TRIGGER UpdateNameExample 
BEFORE UPDATE 
ON NameExample 
FOR EACH ROW 
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '_:.-ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=', 
      '_:.-ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR 
  NOT (UPPER(SUBSTR(new.value, 1, 1)) 
    BETWEEN 'A' AND 'Z' OR 
  SUBSTR(new.value, 1, 1) IN ('_', ':')) OR 
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not Name'), 
END; 

22.4.5.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.5.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.5.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.5.4. length Constraining Facet

Refer to Section 22.4.1.2 for design and implementation issues.

22.4.5.5. minLength Constraining Facet

Refer to Section 22.4.1.3 for design and implementation issues.

22.4.5.6. maxLength Constraining Facet

Refer to Section 22.4.1.4 for design and implementation issues.

22.4.6. NCName Datatype

The NCName datatype is derived from the Name datatype by restricting the first character to a letter or underscore. Unfortunately, the allowable characters are over 250 ranges of Unicode characters that would be tedious to implement in Oracle9i. NCName differs from the Name datatype in that the colon character is disallowed.

An alternative is to determine the characters in the NCName datatype required for your application, and use this set for compliance checking. If this set is small, enforcing this datatype is easier to implement. For example, if the character set is United States English, the list of allowable characters is ‘A’ to ‘Z’, ‘0’ to ‘9’, underscore, period, and ASCII hyphen. Listing 22.18 implements this datatype as a column constraint, and Listing 22.19 implements this as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.18. NCNameExample Table Creation with a Column Constraint

CREATE TABLE NCNameExample ( 
value VARCHAR2(30) 
   CHECK (LENGTH(TRANSLATE(UPPER(value), 
        '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=:', 
        '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) = 
      LENGTH(value) AND 
     (UPPER(SUBSTR(value, 1, 1)) 
       BETWEEN 'A' AND 'Z' OR 
      SUBSTR(value, 1, 1) = '_') AND 
     INSTR(value, UNISTR('00D')) = 0 AND 
     INSTR(value, UNISTR('00A')) = 0 AND 
     INSTR(value, UNISTR('009')) = 0 AND 
     LENGTH(value) = LENGTH(TRIM(value)) AND 
     INSTR(value, ' ') = 0) 
); 

Listing 22.19. NCNameExample Table Creation with Insert and Update Triggers

CREATE TABLE NCNameExample ( 
value VARCHAR2(30) 
); 

CREATE OR REPLACE TRIGGER InsertNCNameExample 
BEFORE INSERT 
ON NCNameExample 
FOR EACH ROW 
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=:', 
      '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR 
  NOT (UPPER(SUBSTR(new.value, 1, 1)) 
      BETWEEN 'A' AND 'Z' OR 
     SUBSTR(new.value, 1, 1) = '_') OR 
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not NCName'), 
END; 

CREATE OR REPLACE TRIGGER UpdateNCNameExample 
BEFORE UPDATE 
ON NCNameExample 
FOR EACH ROW 
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=:', 
      '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR 
  NOT (UPPER(SUBSTR(new.value, 1, 1)) 
      BETWEEN 'A' AND 'Z' OR 
     SUBSTR(new.value, 1, 1) = '_') OR 
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not NCName'), 
END; 

22.4.6.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.6.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.6.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.6.4. length Constraining Facet

Refer to Section 22.4.1.2 for design and implementation issues.

22.4.6.5. minLength Constraining Facet

Refer to Section 22.4.1.3 for design and implementation issues.

22.4.6.6. maxLength Constraining Facet

Refer to Section 22.4.1.4 for design and implementation issues.

22.4.7. ID Datatype

Although the ID datatype is derived from NCName, it has the same value space as NCName. Refer to Section 22.4.6 for details on how to implement this type.

22.4.8. IDREF Datatype

Although the IDREF datatype is derived from NCName, it has the same value space as NCName. Refer to Section 22.4.6 for details on how to implement this type.

22.4.9. IDREFS Datatype

Because the IDREFS datatype is a list of IDREF datatypes, the same constraints that apply to IDREF (which essentially is an NCName) are applicable. Refer to Section 22.4.6 for details on how to implement this type. Unlike NCName, however, there are three distinct ways to implement the IDREFS datatype:

• Create a single column to hold the list of IDREFS as a set of characters.

• Create a table to hold the IDREFS that are associated with a primary table.

• Create a single column of type VARRAY to hold the list of IDREFS.

Each of these approaches has its own merits. The first places little burden on the application developer or database designer and is applicable if the sole purpose is to insert, update, delete, and select the values in their entirety. The second requires more work from both the application developer and the database designer, but allows individual IDREF values that are part of the list to be easily inserted, updated, deleted, and selected. The third requires the most work from both the application developer and database designer and provides little benefit beyond the first approach. Its implementation will not be detailed.

In either the first or second approach, IDREFS is derived from the NCName datatype by restricting the first character to a letter or underscore. Unfortunately, the allowable characters are over 250 ranges of Unicode characters that would be tedious to implement in Oracle9i. An alternative is to determine the characters in the IDREFS datatype required for your application, and use this set for compliance checking. If this set is small, enforcing this datatype is easier to implement. For example, if the character set is United States English, the list of allowable characters is ‘A’ to ‘Z’, ‘0’ to ‘9’, space, period, ASCII hyphen, and underscore.

22.4.9.1. Single Column

In a single column scenario, the IDREFS datatype is essentially a multivalued NCNAME datatype where the values are maintained as a single string. Listing 22.20 implements IDREFS datatype as a column constraint, and Listing 22.21 implements this as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.20. IDREFSExample Single Column Table Creation with a Column Constraint

CREATE TABLE IDREFSExample ( 
value VARCHAR2(30) 
   CHECK (LENGTH(TRANSLATE(UPPER(value), 
        '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=:', 
        '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) = 
      LENGTH(value) AND 
     (UPPER(SUBSTR(value, 1, 1)) 
       BETWEEN 'A' AND 'Z' OR 
      SUBSTR(value, 1, 1) = '_') AND 
     INSTR(value, UNISTR('00D')) = 0 AND 
     INSTR(value, UNISTR('00A')) = 0 AND 
     INSTR(value, UNISTR('009')) = 0 AND 
     LENGTH(value) = LENGTH(TRIM(value)) AND 
     INSTR(value, ' ') = 0) 
); 

Listing 22.21. IDREFSExample Single Column Table Creation with Insert and Update Triggers

CREATE TABLE IDREFSExample ( 
value VARCHAR2(30) 
); 

CREATE OR REPLACE TRIGGER InsertIDREFSExample 
BEFORE INSERT 
ON IDREFSExample 
FOR EACH ROW   
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=:', 
      '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR 
  NOT (UPPER(SUBSTR(new.value, 1, 1)) 
      BETWEEN 'A' AND 'Z' OR 
     SUBSTR(new.value, 1, 1) = '_') OR 
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not IDREFS'), 
END; 

CREATE OR REPLACE TRIGGER UpdateIDREFSExample 
BEFORE UPDATE 
ON IDREFSExample 
FOR EACH ROW 
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=:', 
      '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR 
  NOT (UPPER(SUBSTR(new.value, 1, 1)) 
      BETWEEN 'A' AND 'Z' OR 
     SUBSTR(new.value, 1, 1) = '_') OR 
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not IDREFS'), 
END; 

22.4.9.2. Separate Table

In a separate table scenario, the IDREFS datatype is essentially a multivalued NCNAME datatype where each value is stored separately in a table row. In Listing 22.22 and Listing 22.23, a sequence, primary keys, and a foreign key are created. A sequence provides the unique values for the primary key of the IDREFSExample table. The sole column of the IDREFSExample table, IDREFSExampleID, is a foreign key to the IDREFSInternalExample table that also has listOrder as the second part of the primary key. The IDREFSInternalExample table holds the IDREF value. Listing 22.24 shows how an IDREFS example of ‘a b c’ would be inserted into the database.

Listing 22.22 implements IDREFS datatype as a column constraint, and Listing 22.23 implements this as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.22. IDREFS Example Tables Creation with a Column Constraint

--Start the sequence at 1, don't let it cycle, and have 
--the numbers in the sequence ordered. 
CREATE SEQUENCE Seq_IDREFS INCREMENT BY 1 START 
 WITH 1 MAXVALUE 999999999 MINVALUE 1 NOCYCLE ORDER; 

CREATE TABLE IDREFSExample ( 
IDREFSExampleID NUMBER(9) NOT NULL, 
PRIMARY KEY (IDREFSExampleID) 
); 

CREATE TABLE IDREFSInternalExample ( 
IDREFSInternalExampleID NUMBER(9) NOT NULL, 
listOrder        NUMBER(3) NOT NULL, 
value          VARCHAR2(30) 
 CHECK (LENGTH(TRANSLATE(UPPER(value), 
       '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=:', 
       '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) = 
     LENGTH(value) AND 
    (UPPER(SUBSTR(value, 1, 1)) 
     BETWEEN 'A' AND 'Z' OR 
    SUBSTR(value, 1, 1) = '_') AND 
    INSTR(value, UNISTR('00D')) = 0 AND 
    INSTR(value, UNISTR('00A')) = 0 AND 
    INSTR(value, UNISTR('009')) = 0 AND 
    LENGTH(value) = LENGTH(TRIM(value)) AND 
    INSTR(value, ' ') = 0), 
PRIMARY KEY (IDREFSInternalExampleID, listOrder), 
FOREIGN KEY (IDREFSInternalExampleID) 
REFERENCES IDREFSExample 
); 

Listing 22.23. IDREFS Example Tables Creation with Insert and Update Triggers

--Start the sequence at 1, don't let it cycle, and have 
--the numbers in the sequence ordered. 
CREATE SEQUENCE Seq_IDREFS INCREMENT BY 1 START 
 WITH 1 MAXVALUE 999999999 MINVALUE 1 NOCYCLE ORDER; 

CREATE TABLE IDREFSExample ( 
IDREFSExampleID NUMBER(9) NOT NULL,   
PRIMARY KEY (IDREFSExampleID) 
); 

CREATE TABLE IDREFSInternalExample ( 
IDREFSInternalExampleID NUMBER(9) NOT NULL, 
listOrder    NUMBER(3) NOT NULL, 
value     VARCHAR2(30), 
PRIMARY KEY (IDREFSInternalExampleID, listOrder), 
FOREIGN KEY (IDREFSInternalExampleID) 
REFERENCES IDREFSExample 
); 

CREATE OR REPLACE TRIGGER InsertIDREFSInternalExample 
BEFORE INSERT 
ON IDREFSInternalExample 
FOR EACH ROW 
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=:', 
      '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR 
  NOT (UPPER(SUBSTR(new.value, 1, 1)) 
      BETWEEN 'A' AND 'Z' OR 
     SUBSTR(new.value, 1, 1) = '_') OR 
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not IDREFS'), 
END; 

CREATE OR REPLACE TRIGGER UpdateIDREFSInternalExample 
BEFORE UPDATE 
ON IDREFSInternalExample 
FOR EACH ROW 
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=:', 
      '.-_ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR 
  NOT (UPPER(SUBSTR(new.value, 1, 1)) 
      BETWEEN 'A' AND 'Z' OR 
     SUBSTR(new.value, 1, 1) = '_') OR 
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0)  
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not IDREFS'), 
END; 

Listing 22.24. IDREFS Example Values Insertion

INSERT INTO IDREFSExample 
VALUES 
(Seq_IDREFS.NEXTVAL); 

INSERT INTO IDREFSInternalExample 
VALUES 
(Seq_IDREFS.CURRVAL, 1, 'a'), 

INSERT INTO IDREFSInternalExample 
VALUES 
(Seq_IDREFS.CURRVAL, 2, 'b'), 

INSERT INTO IDREFSInternalExample 
VALUES 
(Seq_IDREFS.CURRVAL, 3, 'c'), 

22.4.9.3. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.9.4. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.9.5. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.9.6. length Constraining Facet

Refer to Section 22.4.1.2 for design and implementation issues.

22.4.9.7. minLength Constraining Facet

Refer to Section 22.4.1.3 for design and implementation issues.

22.4.9.8. maxLength Constraining Facet

Refer to Section 22.4.1.4 for design and implementation issues.

22.4.10. ENTITY Datatype

Although the ENTITY datatype is derived from NCName, it has the same value space as NCName. Refer to Section 22.4.6 for details on how to implement this datatype.

22.4.11. ENTITIES Datatype

Although the ENTITIES datatype is a not derived from IDREFS, it has the same value space as IDREFS. Refer to Section 22.4.9 for details on how to implement this datatype.

22.4.12. NMTOKEN Datatype

The NMTOKEN datatype is derived from the token datatype and restricts the allowable characters. Unfortunately, the allowable characters are over 250 ranges of Unicode characters that would be tedious to implement in Oracle9i.

An alternative is to determine the characters in the NMTOKEN datatype for required for your application, and use this set for compliance checking. If this set is small, enforcing this datatype is easier to implement. For example, if the character set is United States English, the list of allowable characters is ‘A’ to ‘Z’, ‘0’ to ‘9’, underscore, colon, period, and ASCII hyphen. Listing 22.25 implements NMTOKEN as a column constraint, and Listing 22.26 implements it as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.25. NMTOKENExample Table Creation with a Column Constraint

CREATE TABLE NMTOKENExample ( 
value VARCHAR2(30) 
    CHECK (LENGTH(TRANSLATE(UPPER(value), 
        '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=', 
        '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) = 
       LENGTH(value) AND 
      INSTR(value, UNISTR('00D')) = 0 AND 
      INSTR(value, UNISTR('00A')) = 0 AND 
      INSTR(value, UNISTR('009')) = 0 AND 
      LENGTH(value) = LENGTH(TRIM(value)) AND 
      INSTR(value, ' ') = 0) 
); 

Listing 22.26. NMTOKENExample Table Creation with Insert and Update Triggers

CREATE TABLE NMTOKENExample ( 
value VARCHAR2(30) 
); 

CREATE OR REPLACE TRIGGER InsertNMTOKENExample 
BEFORE INSERT 
ON NMTokenExample 
FOR EACH ROW 
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=',  
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR 
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not NMTOKEN'), 
END; 

CREATE OR REPLACE TRIGGER UpdateNMTOKENExample 
BEFORE UPDATE 
ON NMTokenExample 
FOR EACH ROW 
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=', 
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR 
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not NMTOKEN'), 
END; 

22.4.12.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.12.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.12.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.12.4. length Constraining Facet

Refer to Section 22.4.1.2 for design and implementation issues.

22.4.12.5. minLength Constraining Facet

Refer to Section 22.4.1.3 for design and implementation issues.

22.4.12.6. maxLength Constraining Facet

Refer to Section 22.4.1.4 for design and implementation issues.

22.4.13. NMTOKENS Datatype

Because the NMTOKENS datatype is a list of NMTOKEN, the same constraints that apply to NMTOKEN are applicable. Refer to Section 22.4.12 for details on how to implement this datatype. Unlike NMTOKEN, however, there are three distinct ways to implement this datatype:

• Create a single column to hold the list of NMTOKENS as a set of characters.

• Create a table to hold the NMTOKENS that are associated with a primary table.

• Create a single column of type VARRAY to hold the list of NMTOKENS.

Each of these approaches has its own merits. The first places little burden on the application developer or database designer and is applicable if the sole purpose is to insert, update, delete, and select the values in their entirety. The second requires more work from both the application developer and database designer, but allows individual NMTOKEN values that are part of the list to be easily inserted, updated, deleted, and selected. The third requires the most work from both the application developer and database designer and provides little benefit beyond the first approach. Its implementation will not be detailed.

In either the first or second approach, NMTOKENS is derived from the NMTOKEN datatype by restricting the first character to a letter or underscore. Unfortunately, the allowable characters are over 250 ranges of Unicode characters that would be tedious to implement in Oracle9i. An alternative is to determine the characters in the NMTOKENS datatype required for your application, and use this set for compliance checking. If this set is small, enforcing this datatype is easier to implement. For example, if the character set is United States English, the list of allowable characters is ‘A’ to ‘Z’, ‘0’ to ‘9’, space, period, ASCII hyphen, underscore, and colon.

22.4.13.1. Single Column

In a single column scenario, the NMTOKENS datatype is essentially a multivalued NMTOKEN datatype where the values are maintained as a single string. Listing 22.27 implements this datatype as a column constraint, and Listing 22.28 implements this as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.27. NMTOKENSExample Single-Column Table Creation with a Column Constraint

CREATE TABLE NMTOKENSExample ( 
value VARCHAR2(30) 
    CHECK (LENGTH(TRANSLATE(UPPER(value), 
        '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=', 
        '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) = 
       LENGTH(value) AND 
      INSTR(value, UNISTR('00D')) = 0 AND 
      INSTR(value, UNISTR('00A')) = 0 AND 
      INSTR(value, UNISTR('009')) = 0 AND  
      LENGTH(value) = LENGTH(TRIM(value)) AND 
      INSTR(value, ' ') = 0) 
); 

Listing 22.28. NMTOKENSExample Single-Column Table Creation with Insert and Update Triggers

CREATE TABLE NMTOKENSExample ( 
value VARCHAR2(30) 
); 

CREATE OR REPLACE TRIGGER InsertNMTOKENSExample 
BEFORE INSERT 
ON NMTOKENSExample 
FOR EACH ROW 
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=', 
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR 
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not NMTOKENS'), 
END; 

CREATE OR REPLACE TRIGGER UpdateNMTOKENSExample 
BEFORE UPDATE 
ON NMTOKENSExample 
FOR EACH ROW 
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=', 
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR 
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not NMTOKENS'), 
END; 

22.4.13.2. Separate Table

In a separate table scenario, the NMTOKENS datatype is essentially a multivalued NMTOKEN datatype where each value is stored separately in a table row. In Listing 22.29 and Listing 22.30, a sequence, primary keys, and a foreign key are created. A sequence provides the unique values for the primary key of the NMTOKENSExample table. The sole column of the NMTOKENSExample table, NMTOKENSExampleID, is a foreign key to the NMTOKENSInternalExample table that also has listOrder as the second part of the primary key. The NMTOKENSInternalExample table holds the NMTOKEN value. Listing 22.31 shows how a NMTOKENS example of ‘a b c’ would be inserted into the database.

Listing 22.29 implements this datatype as a column constraint, and Listing 22.30 implements this as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.29. NMTOKENS Example Tables Creation with a Column Constraint

--Start the sequence at 1, don't let it cycle, and have 
--the numbers in the sequence ordered. 
CREATE SEQUENCE Seq_NMTOKENS INCREMENT BY 1 START 
 WITH 1 MAXVALUE 999999999 MINVALUE 1 NOCYCLE ORDER; 

CREATE TABLE NMTOKENSExample ( 
NMTOKENSExampleID NUMBER(9) NOT NULL, 
PRIMARY KEY (NMTOKENSExampleID) 
); 

CREATE TABLE NMTOKENSInternalExample ( 
NMTOKENSInternalExampleID NUMBER(9) NOT NULL, 
listOrder         NUMBER(3) NOT NULL, 
value           VARCHAR2(30) 
CHECK (LENGTH(TRANSLATE(UPPER(value), 
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=', 
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) = 
     LENGTH(value) AND 
    INSTR(value, UNISTR('00D')) = 0 AND 
    INSTR(value, UNISTR('00A')) = 0 AND 
    INSTR(value, UNISTR('009')) = 0 AND 
    LENGTH(value) = LENGTH(TRIM(value)) AND 
    INSTR(value, ' ') = 0), 
PRIMARY KEY (NMTOKENSInternalExampleID, listOrder), 
FOREIGN KEY (NMTOKENSInternalExampleID) 
REFERENCES NMTOKENSExample 
); 

Listing 22.30. NMTOKENS Example Tables Creation with Insert and Update Triggers

--Start the sequence at 1, don't let it cycle, and have 
--the numbers in the sequence ordered. 
CREATE SEQUENCE Seq_NMTOKENS INCREMENT BY 1 START 
 WITH 1 MAXVALUE 999999999 MINVALUE 1 NOCYCLE ORDER; 

CREATE TABLE NMTOKENSExample ( 
NMTOKENSExampleID NUMBER(9) NOT NULL, 
PRIMARY KEY (NMTOKENSExampleID) 
); 

CREATE TABLE NMTOKENSInternalExample ( 
NMTOKENSInternalExampleID NUMBER(9) NOT NULL, 
listOrder         NUMBER(3) NOT NULL, 
value           VARCHAR2(30), 
PRIMARY KEY (NMTOKENSInternalExampleID, listOrder), 
FOREIGN KEY (NMTOKENSInternalExampleID) 
REFERENCES NMTOKENSExample 
); 

CREATE OR REPLACE TRIGGER InsertNMTOKENSInternalExample 
BEFORE INSERT 
ON NMTOKENSInternalExample 
FOR EACH ROW 
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=', 
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR 
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not NMTOKENS'), 
END; 

CREATE OR REPLACE TRIGGER UpdateNMTOKENSInternalExample 
BEFORE UPDATE 
ON NMTOKENSInternalExample 
FOR EACH ROW 
WHEN (LENGTH(TRANSLATE(UPPER(new.value), 
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,/;''[]<>?"{}|~!@#$%^&*()+=', 
      '.-_:ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')) <> 
   LENGTH(new.value) OR   
  INSTR(new.value, UNISTR('00D')) <> 0 OR 
  INSTR(new.value, UNISTR('00A')) <> 0 OR 
  INSTR(new.value, UNISTR('009')) <> 0 OR 
  LENGTH(new.value) <> LENGTH(TRIM(new.value)) OR 
  INSTR(new.value, ' ') <> 0) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not NMTOKENS'), 
END; 

Listing 22.31. NMTOKENS Example Values Insertion

INSERT INTO NMTOKENSExample 
VALUES 
(Seq_NMTOKENS.NEXTVAL); 

INSERT INTO NMTOKENSInternalExample 
VALUES 
(Seq_NMTOKENS.CURRVAL, 1, 'a'), 

INSERT INTO NMTOKENSInternalExample 
VALUES 
(Seq_NMTOKENS.CURRVAL, 2, 'b'), 

INSERT INTO NMTOKENSInternalExample 
VALUES 
(Seq_NMTOKENS.CURRVAL, 3, 'c'), 

22.4.13.3. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.13.4. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.13.5. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.13.6. length Constraining Facet

Refer to Section 22.4.1.2 for design and implementation issues.

22.4.13.7. minLength Constraining Facet

Refer to Section 22.4.1.3 for design and implementation issues.

22.4.13.8. maxLength Constraining Facet

Refer to Section 22.4.1.4 for design and implementation issues.

22.4.14. decimal Datatype

The decimal datatype can be represented as an Oracle NUMBER datatype. Oracle guarantees portability of numbers that have a precision of up to 38 total digits. Numbers that have more digits should be avoided, if possible. Also note that XML schema processors are only required to support 18 total digits.

Database designers should avoid having an XML schema document with decimal-derived datatypes that have neither totalDigits nor min/max exclusive/inclusive constraining facets defined. These might already be implicit if the development language is Java, Visual Basic, and so on, because language datatypes have an upper and lower bound. If the constraining facets are not explicitly or implicitly defined, determining the intention of the XML schema designer avoids having potential decimal values dependent on the instance of the XML schema processor, incredibly large or small numbers, and so on.

22.4.14.1. totalDigits Constraining Facet

Listing 22.32 creates the TotalDigitsExample table with a value column with 18 total digits.

Listing 22.32. totalDigits Constraining Facet Example Table Creation

CREATE TABLE TotalDigitsExample ( 
value  NUMBER(18) 
); 

22.4.14.2. fractionDigits Constraining Facet

Listing 22.33 creates the FractionDigitsExample table with a value column having 18 total digits and 2 fractional digits.

Listing 22.33. fractionDigits Constraining Facet Example Table Creation

CREATE TABLE FractionDigitsExample ( 
value  NUMBER(18, 2) 
); 

22.4.14.3. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.14.4. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.14.5. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.14.6. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but this can be added as a column constraint in table creation or as a trigger. Listing 22.34 implements the maxInclusive constraining facet in a column constraint, and Listing 22.35 implements it as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.34. maxInclusive Constraining Facet Example Table Creation with a Column Constraint

CREATE TABLE MaxInclusiveExample ( 
value NUMBER(3, 2) CHECK (value <= 5) 
); 

Listing 22.35. maxInclusive Constraining Facet Example Table Creation with Insert and Update Triggers

CREATE TABLE MaxInclusiveExample ( 
value  NUMBER(3, 2) 
); 

CREATE OR REPLACE TRIGGER InsertMaxInclusiveExample 
BEFORE INSERT 
ON MaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 5) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Number too large'), 
END; 

CREATE OR REPLACE TRIGGER UpdateMaxInclusiveExample 
BEFORE UPDATE 
ON MaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 5) 
BEGIN  
RAISE_APPLICATION_ERROR(-20000, 'Number too large'), 
END; 

22.4.14.7. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but this can be added as a column constraint in table creation or as a trigger. Listing 22.36 implements the maxExclusive constraining facet in a column constraint, and Listing 22.37 implements it as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.36. maxExclusive Constraining Facet Example Table Creation with a Column Constraint

CREATE TABLE MaxExclusiveExample ( 
value NUMBER(3, 2) CHECK (value < 5) 
); 

Listing 22.37. maxExclusive Constraining Facet Example Table Creation with Insert and Update Triggers

 
CREATE TABLE MaxExclusiveExample ( 
value  NUMBER(3, 2) 
); 

CREATE OR REPLACE TRIGGER InsertMaxExclusiveExample 
BEFORE INSERT 
ON MaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value > 4) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Number too large'), 
END; 

CREATE OR REPLACE TRIGGER UpdateMaxExclusiveExample 
BEFORE UPDATE 
ON MaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value > 4) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, ' Number too large'), 
END; 

22.4.14.8. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but this can be added as a column constraint in table creation or as a trigger. Listing 22.38 implements the minInclusive constraining facet in a column constraint, and Listing 22.39 implements it as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.38. minInclusive Constraining Facet Example Table Creation with a Column Constraint

CREATE TABLE MinInclusiveExample ( 
value  NUMBER(18, 2) CHECK (value >= 5) 
); 

Listing 22.39. minInclusive Constraining Facet Example Table Creation with Insert and Update Triggers

CREATE TABLE MinInclusiveExample ( 
value  NUMBER(18, 2) 
); 

CREATE OR REPLACE TRIGGER InsertMinInclusiveExample BEFORE INSERT 
ON MinInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 5) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Number too small'), 
END; 

CREATE OR REPLACE TRIGGER UpdateMinInclusiveExample BEFORE UPDATE 
ON MinInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 5) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, ' Number too small'), 
END; 

22.4.14.9. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but this can be added as a column constraint in table creation or as a trigger. Listing 22.40 implements the minExclusive constraining facet in a column constraint, and Listing 22.41 implements it as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.40. minExclusive Constraining Facet Example Table Creation with a Column Constraint

CREATE TABLE MinExclusiveExample ( 
value  NUMBER(18, 2) CHECK (value < 5) 
); 

Listing 22.41. minExclusive Constraining Facet Example Table Creation with Insert and Update Triggers

CREATE TABLE MinExclusiveExample ( 
value  NUMBER(18, 2) 
); 

CREATE OR REPLACE TRIGGER InsertMinExclusiveExample 
BEFORE INSERT 
ON MinExclusiveExample 
FOR EACH ROW 
WHEN (new.value > 4) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Number too small'), 
END; 

CREATE OR REPLACE TRIGGER UpdateMinExclusiveExample 
BEFORE UPDATE 
ON MinExclusiveExample 
FOR EACH ROW 
WHEN (new.value > 4) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, ' Number too small'), 
END; 

22.4.15. integer Datatype

The integer datatype is derived from the decimal datatype by restricting the values to have 0 fractionDigits. Refer to Section 22.4.14 for design and implementation issues of this datatype.

22.4.16. nonPositiveInteger Datatype

The nonPositiveInteger datatype is derived by restriction from the integer datatype with a maxInclusive of 0. Refer to Section 22.4.14 for design and implementation issues of this type.

22.4.17. negativeInteger Datatype

The negativeInteger datatype is derived by restriction from the nonPositiveInteger number with a maxInclusive of −1. Refer to Section 22.4.14 for design and implementation issues of this type.

22.4.18. long Datatype

The long datatype is derived by restriction from the integer datatype with a maxInclusive of 9223372036854775807 and a minInclusive of −9223372036854775808. Refer to Section 22.4.14 for design and implementation issues of this type.

22.4.19. int Datatype

The int datatype is derived by restriction from the long datatype with a maxInclusive of 2147483647 and a minInclusive of −2147483648. Refer to Section 22.4.14 for design and implementation issues of this type.

22.4.20. short Datatype

The short datatype is derived by restriction from the int datatype with a maxInclusive of 32767 and a minInclusive of −32768. Refer to Section 22.4.14 for design and implementation issues of this type.

22.4.21. byte Datatype

The byte datatype is derived by restriction from the short datatype with a maxInclusive of 127 and a minInclusive of −128. Refer to Section 22.4.14 for design and implementation issues of this type.

22.4.22. nonNegativeInteger Datatype

The nonNegativeInteger datatype is derived by restriction from the integer datatype with a minInclusive of 0. Refer to Section 22.4.14 for design and implementation issues of this type.

22.4.23. unsignedLong Datatype

The unsignedLong datatype is derived by restriction from the nonNegativeInteger datatype with a maxInclusive of 18446744073709551615 and a minInclusive of 0. Refer to Section 22.4.14 for design and implementation issues of this type.

22.4.24. unsignedInt Datatype

The unsignedInt datatype is derived by restriction from the unsignedLong number with a maxInclusive of 4294967295 and a minInclusive of 0. Refer to Section 22.4.14 for design and implementation issues of this type.

22.4.25. unsignedShort Datatype

The unsignedShort datatype is derived by restriction from the unsignedInt datatype with a maxInclusive of 65535 and a minInclusive of 0. Refer to Section 22.4.14 for design and implementation issues of this type.

22.4.26. unsignedByte Datatype

The unsignedByte datatype is derived by restriction from the unsignedShort datatype with a maxInclusive of 255 and a minInclusive of 0. Refer to Section 22.4.14 for design and implementation issues of this type.

22.4.27. positiveInteger Datatype

The positiveInteger datatype is derived by restriction from the integer datatype with a minInclusive of 1. Refer to Section 22.4.14 for design and implementation issues of this type.

22.4.28. float Datatype

Oracle does not support the IEEE 754-1985 specification for numbers on which this datatype is based. The values NOT-A-NUMBER, POSITIVE INFINITY, and NEGATIVE INFINITY simply cannot be represented directly. Refer to Section 22.4.14 on approximating the implementation of the float datatype. This representation accurately represent floats for most business applications.

If a complete representation is required, a number of solutions are possible:

• Use one of the character column datatypes instead—a good solution when the data only needs to be stored and retrieved.

• Use a set of numbers outside the min/max inclusion/exclusion range to signify each of the possible unrepresentable values—a good solution when the floats need to be manipulated inside the database, but requires implementation of IEEE 754-1985 rules for addition of NOT-A-NUMBER and POSITIVE INFINITY, and so on.

• Create database columns for each of the possible unrepresentable values—a good solution when the floats need to be manipulated inside the database and the IEEE 754-1985 value needs to be made explicit (by creating a separate column), but requires implementation of IEEE 754-1985 rules for addition of NOT-A-NUMBER and POSITIVE INFINITY, and so on.

For each of these solutions, you can create Java stored procedures that take floats or floats represented by characters and perform the arithmetic operations in Java. The Java stored procedure then returns an appropriate float or an appropriate float represented by characters.

22.4.29. double Datatype

Oracle does not support the IEEE 754-1985 specification for numbers on which this datatype is based. The values NOT-A-NUMBER, POSITIVE INFINITY, and NEGATIVE INFINITY simply cannot be represented directly. Refer to Section 22.4.14 on approximating the implementation of the double datatype. This representation accurately represent floats for most business applications.

If a complete representation is required, refer to Section 22.4.28 for a series of options.

22.4.30. hexBinary Datatype

The hexBinary datatype can be represented naturally in one of three ways: as a VARCHAR2, CLOB, or BLOB. When the minLength or length has been specified at fewer than 4,000 characters, VARCHAR2 has significant performance and ease-of-use advantages over a CLOB or BLOB from an application development perspective. At over 4,000 characters, a CLOB or BLOB must be used. A BLOB is much more compact; constraining facets are more difficult to implement. A CLOB is space inefficient, but allows a straightforward implementation of the constraining facets.

A BLOB is not recommended when a constraining facet occurs. Unfortunately, a BLOB value must be decoded to check the constraint in after-insert and after-update triggers. A before trigger is not allowed. The result is slow performance and the requirement for the application to manually rollback any errors. When there are no constraining facets, a BLOB is a good implementation. To convert a hexBinary value to a BLOB, call the UTL_ENCODE.UUENCODE PL/SQL procedure. To convert a BLOB to a hexBinary value, call the UTL_ENCODE.UUDECODE PL/SQL procedure.

22.4.30.1. length Constraining Facet

Refer to Section 22.4.1.2 for design and implementation issues.

22.4.30.2. minLength Constraining Facet

Refer to Section 22.4.1.3 for design and implementation issues.

22.4.30.3. maxLength Constraining Facet

Refer to Section 22.4.1.4 for design and implementation issues.

22.4.30.4. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.30.5. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.30.6. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.31. base64Binary Datatype

Although base64Binary is not derived by restriction from the hexBinary datatype, it has almost identical design issues and implementation details. Review Section 22.4.30 for design and implementation issues of this datatype. Note that a base64Binary value can be converted to a BLOB by using the UTL_ENCODE.BASE64_UUENCODE procedure. To convert a BLOB to a base64Binary value, call the UTL_ENCODE.BASE64_UUDECODE procedure.

22.4.32. QName Datatype

The QName datatype is a combination of the anyURI datatype and the NCName datatype. QName is probably best represented as two columns in a table. Refer to Sections 22.4.33 and 22.4.6 for design and implementation issues of this type.

22.4.33. anyURI Datatype

The anyURI datatype can now be implemented directly in Oracle9i. The PL/SQL SYS package includes a UriType type and a URI factory class. However, from an XML schema perspective, there is little to recommend the use of the SYS.UriType over the VARCHAR2 datatype, because Oracle performs little or no syntax checking of the URI. In addition, many of the older—but still widely used—database drivers do not support Oracle objects. The anyURI datatype is probably best treated as a string datatype, because it has the same constraining facets. Refer to Section 22.4.1 for information on designing and implementing the anyURI datatype.

Using the SYS.UriType column datatype in Oracle has potential benefits for an application. Oracle supports functionality with respect to URLs and internal database URIs that can aid in application development as the location of data within the database can now be specified with a URI.

22.4.34. NOTATION Datatype

Although NOTATION datatype is not derived by restriction from the QName datatype, it has the same value space. Refer to Section 22.4.32 for design and implementation issues of this type.

22.4.35. boolean Datatype

Oracle does not yet support the boolean datatype as defined in the SQL-92 standard. As a result, a boolean datatype should be mapped to either a database NUMBER or VARCHAR column datatype.

The boolean datatype has the following legal literals: ‘true’, ‘false’, ‘1’, and ‘0’. Having all four literals in the database is problematic because there are only two values in the value space. The database designer should communicate with the XML schema designer to determine if all four literals will exist in XML instances. If all four literals are possible, then consider using a database NUMBER column datatype to store the value and using a DECODE statement to map ‘true’ to 1 and ‘false’ to 0. The following SQL fragment can accomplish this:

DECODE(?booleanValue, 'true', 1, 'false', 0, 1, 1, 0, 0) 

where the ?booleanValue bind variable is replaced by the appropriate Boolean value.

22.4.35.1. Representation of the boolean Datatype as True and False

In this scenario, the boolean datatype is represented by the strings of ‘true’ and ‘false’. Listing 22.42 implements this as a column constraint, and Listing 22.43 implements this as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.42. BooleanStringExample Table Creation with a Column Constraint

CREATE TABLE BooleanStringExample ( 
value  VARCHAR(5) CHECK (value = 'true' OR value = 'false') 
); 

Listing 22.43. BooleanStringExample Table Creation with Insert and Update Triggers

CREATE TABLE BooleanStringExample ( 
value  VARCHAR(5) 
); 

CREATE OR REPLACE TRIGGER InsertBooleanStringExample 
BEFORE INSERT 
ON BooleanStringExample 
FOR EACH ROW 
WHEN (new.value <> 'true' AND new.value <> 'false') 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not a boolean'), 
END; 

CREATE OR REPLACE TRIGGER UpdateBooleanStringExample 
BEFORE UPDATE 
ON BooleanStringExample  
FOR EACH ROW 
WHEN (new.value <> 'true' AND new.value <> 'false') 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not a boolean'), 
END; 

22.4.35.2. Representation of the boolean Datatype as 1 and 0

In this scenario, the boolean datatype is represented by the numbers 0 and 1. Listing 22.44 implements this as a column constraint, and Listing 22.45 implements this as a trigger. Refer to Section 22.3 regarding the decision to use column constraints or triggers.

Listing 22.44. BooleanNumberExample Table Creation with a Column Constraint

CREATE TABLE BooleanNumberExample ( 
value  NUMBER(1) CHECK (value IN (0, 1)) 
); 

Listing 22.45. BooleanNumberExample Table Creation with Insert and Update Triggers

CREATE TABLE BooleanNumberExample ( 
value  NUMBER(1) 
); 

CREATE OR REPLACE TRIGGER InsertBooleanNumberExample 
BEFORE INSERT 
ON BooleanNumberExample 
FOR EACH ROW 
WHEN (new.value NOT IN (0, 1)) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not boolean'), 
END; 

CREATE OR REPLACE TRIGGER UpdateBooleanNumberExample 
BEFORE UPDATE 
ON BooleanNumberExample 
FOR EACH ROW 
WHEN (new.value NOT IN (0, 1)) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Not boolean'), 
END; 

22.4.35.3. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.35.4. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.36. duration Datatype

The duration datatype represents a duration of time that is either positive or negative and may include years, months, hours, minutes, seconds, and fractional seconds. The duration value space is partially ordered. For example, one year is greater than 364 days, not comparable with 365 days, not comparable with 366 days, and less than 367 days (because some years are equal to 365 days and others are equal to 366 days).

Validating a duration requires pattern matching through the Java JVM in Oracle or a significant effort in hand-coded PL/SQL. Validating the minimum and maximum constraining facets requires a significant amount of hand-coded PL/SQL, again because duration values are only partially ordered.

If the database designer can determine that the XML instances will be constrained to one duration components (‘Y’, ‘M’, ‘D’, and so on), then the durations are totally ordered and can be represented as a decimal datatype or integer datatype. Refer to Sections 22.4.14 and 22.4.15 for design and implementation issues of the duration subtypes.

The duration datatype is probably best treated as a string datatype, with validation left to the application developer. If validation is required by the database, refer to Section 22.2.1.

22.4.36.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.36.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.36.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.36.4. maxInclusive Constraining Facet

Refer to Section 22.4.14.6 for design and implementation issues.

22.4.36.5. maxInclusive Constraining Facet

Refer to Section 22.4.14.7 for design and implementation issues.

22.4.36.6. minInclusive Constraining Facet

Refer to Section 22.4.14.8 for design and implementation issues.

22.4.36.7. minExclusive Constraining Facet

Refer to Section 22.4.14.9 for design and implementation issues.

22.4.37. dateTime Datatype

The dateTime datatype represents a specific instance in time. Oracle9i now has a TIMESTAMP column datatype that represents a date, time of day, and an optional time zone. A dateTime value may or may not include a time zone. The database designer should confer with the XML schema designer to determine if time-zone values will exist in XML instances and, if so, whether the values need to be preserved or the dateTime values can be reduced to their canonical form. This section on the dateTime datatype assumes that they exist in the XML instance and must be preserved.

Listing 22.46 implements a dateTime table creation.

Listing 22.46. DateTimeExample Table Creation

CREATE TABLE DateTimeExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

To insert a row into the table with a dateTime datatype, Oracle needs to convert a value from its ISO 8601 standard format to an internal Oracle representation.

Listing 22.47 is a SQL statement that inserts a dateTime into the DateTimeExample table (?datetimeValue is the dateTime bind variable).

Listing 22.47. DateTimeExample Example Insert

INSERT INTO DateTimeExample 
(value) 
SELECT TO_TIMESTAMP_TZ('2001-12-21T12:45:30Z+05:00', 
           'YYYY-MM-DD"T"HH24:MI:SS"Z"TZH:TZM') 
FROM dual; 

Listing 22.48 is a SQL statement that retrieves all of the values from the DateTimeExample table.

Listing 22.48. DateTimeExample Values Retrieval via a Select Statement

SELECT TO_CHAR(value, 'YYYY-MM-DD"T"HH24:MI:SS"Z"TZH:TZM') 
FROM DateTimeExample; 

22.4.37.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.37.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.37.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.37.4. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing 22.49 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling of PL/SQL conversion functions.

Listing 22.49. DatetimeMaxInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE DatetimeMaxInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertDatetimeMaxInclusive 
BEFORE INSERT 
ON DatetimeMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 
  TO_TIMESTAMP_TZ('2001-12-21T12:45:30Z+05:00', 
          'YYYY-MM-DD"T"HH24:MI:SS"Z"TZH:TZM'))  
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Datetime too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateDatetimeMaxInclusive BEFORE UPDATE 
ON DatetimeMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 
  TO_TIMESTAMP_TZ('2001-12-21T12:45:30Z+05:00', 
          'YYYY-MM-DD"T"HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Datetime too late'), 
END; 

22.4.37.5. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing 22.50 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling of PL/SQL conversion functions.

Listing 22.50. DatetimeMaxExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE DatetimeMaxExclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertDatetimeMaxExclusive 
BEFORE INSERT 
ON DatetimeMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= 
  TO_TIMESTAMP_TZ('2001-12-21T12:45:30Z+05:00', 
          'YYYY-MM-DD"T"HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Datetime too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateDatetimeMaxExclusive 
BEFORE UPDATE 
ON DatetimeMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= 
  TO_TIMESTAMP_TZ('2001-12-21T12:45:30Z+05:00', 
          'YYYY-MM-DD"T"HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Datetime too late'), 
END; 

22.4.37.6. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing 22.51 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling of PL/SQL conversion functions.

Listing 22.51. DatetimeMinInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE DatetimeMinInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertDatetimeMinInclusive BEFORE INSERT 
ON DatetimeMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < 
  TO_TIMESTAMP_TZ('2001-12-21T12:45:30Z+05:00', 
          'YYYY-MM-DD"T"HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Datetime too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateDatetimeMinInclusive BEFORE UPDATE 
ON DatetimeMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < 
  TO_TIMESTAMP_TZ('2001-12-21T12:45:30Z+05:00', 
          'YYYY-MM-DD"T"HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Datetime too early'), 
END; 

22.4.37.7. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing 22.52 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling of PL/SQL conversion functions.

Listing 22.52. DatetimeMinExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE DatetimeMinExclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
);  
CREATE OR REPLACE TRIGGER InsertDatetimeMinExclusive 
BEFORE INSERT 
ON DatetimeMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= 
  TO_TIMESTAMP_TZ('2001-12-21T12:45:30Z+05:00', 
          'YYYY-MM-DD"T"HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Datetime too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateDatetimeMinExclusive 
BEFORE UPDATE 
ON DatetimeMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= 
  TO_TIMESTAMP_TZ('2001-12-21T12:45:30Z+05:00', 
          'YYYY-MM-DD"T"HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Datetime too early'), 
END; 

22.4.38. date Datatype

The date datatype represents a Gregorian calendar date. Oracle has a DATE column datatype that represents a date and time without a time-zone offset and a TIMESTAMP column datatype that represents both date and time with a time-zone offset. The database designer should confer with the XML schema designer to determine if time-zone values will exist in the XML instances because implementations using the DATE column datatype are simpler. This section on the date datatype assumes that time zones exist in the XML instances and must be preserved.

Listing 22.53 implements a date table creation.

Listing 22.53. DateExample Table Creation

CREATE TABLE DateExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

To insert a row into the table with a date datatype, Oracle needs to convert it from its ISO 8601 standard format to an internal Oracle representation. Listing 22.54 is a SQL statement that inserts a date into the DateExample table (?dateValue is the date bind variable).

Listing 22.54. DateExample Example Insert

INSERT INTO DateExample 
(value) 
SELECT TO_TIMESTAMP_TZ(?dateValue, 'YYYY-MM-DD"Z"TZH:TZM') 
FROM dual; 

Listing 22.55 is a SQL statement that retrieves all of the values from the DateExample table.

Listing 22.55. DateExample Values Retrieval via a Select Statement

SELECT TO_CHAR(value, 'YYYY-MM-DD"Z"TZH:TZM') 
FROM DateExample; 

22.4.38.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.38.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.38.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.38.4. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing 22.56 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.56. DateMaxInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE DateMaxInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertDateMaxInclusiveExample 
BEFORE INSERT 
ON DateMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 
  TO_TIMESTAMP_TZ('2001-12-24Z05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Date too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateDateMaxInclusiveExample 
BEFORE UPDATE 
ON DateMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 
  TO_TIMESTAMP_TZ('2001-12-24Z05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Date too late'), 
END; 

22.4.38.5. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing 22.57 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.57. DateMaxExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE DateMaxExclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertDateMaxExclusiveExample 
BEFORE INSERT 
ON DateMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= 
  TO_TIMESTAMP_TZ('2001-12-24Z05:00', 
          'YYYY-MM-DD"Z"TZH:TZM'))  
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Date too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateDateMaxExclusiveExample 
BEFORE UPDATE 
ON DateMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= 
  TO_TIMESTAMP_TZ('2001-12-24Z05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Date too late'), 
END; 

22.4.38.6. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing 22.58 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.58. DateMinInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE DateMinInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertDateMinInclusiveExample 
BEFORE INSERT 
ON DateMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < 
  TO_TIMESTAMP_TZ('2001-12-24Z05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Date too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateDateMinInclusiveExample 
BEFORE UPDATE 
ON DateMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < 
  TO_TIMESTAMP_TZ('2001-12-24Z05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN  
RAISE_APPLICATION_ERROR(-20000, 'Date too early'), 
END; 

22.4.38.7. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing 22.59 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.59. DateMinExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE DateMinExclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertDateMinExclusiveExample 
BEFORE INSERT 
ON DateMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= 
  TO_TIMESTAMP_TZ('2001-12-24Z05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Date too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateDateMinExclusiveExample 
BEFORE UPDATE 
ON DateMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= 
  TO_TIMESTAMP_TZ('2001-12-24Z05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Date too early'), 
END; 

22.4.39. gYear Datatype

The gYear datatype represents a Gregorian calendar year. Without the optional time zone, gYear is essentially an integer datatype. Refer to Sections 22.4.14 and 22.4.15 for details implementing gYear when no time-zone offsets are required.

If time-zone offsets are required, the representation becomes much more complex. Oracle9i now has a TIMESTAMP column datatype that represents both date and time with a time-zone offset. The database designer should confer with the XML schema designer to determine if time-zone values will exist in XML instances and, if so, whether the values need to be preserved or the gYear values can be reduced to their canonical form.

Listing 22.60 implements a gYear table creation.

Listing 22.60. GYearExample Table Creation

CREATE TABLE GYearExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

To insert a row into the table with a gYear datatype, Oracle needs to convert the datatype from its XML Schema format to an internal Oracle representation. Listing 22.59 is a SQL statement that inserts a gYear into the GYearExample table (?gYearValue is the gYear bind variable). Note that January 1st was arbitrarily chosen as the day of the year.

Listing 22.61. GYearExample Example Insert

INSERT INTO gYearExample 
(value) 
SELECT TO_TIMESTAMP_TZ(?gYearValue || '01-01', 
               'YYYY"Z"TZH:TZM MM-DD') 
FROM dual; 

Listing 22.62 is a SQL statement that retrieves all of the values from the GYearExample table.

Listing 22.62. GYearExample Values Retrieval via a Select Statement

SELECT TO_CHAR(value, 'YYYY"Z"TZH:TZM') 
FROM GYearExample; 

22.4.39.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.39.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.39.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.39.4. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing 22.63 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling of PL/SQL conversion functions. This could be obviated in this case by not using the ISO 8601 dateTime format and using the default Oracle external date format, but this is not shown here.

Listing 22.63. GYearMaxInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GYearMaxInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGYearMaxInclusive 
BEFORE INSERT 
ON GYearMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 
  TO_TIMESTAMP_TZ('2001-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYear too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGYearMaxInclusive 
BEFORE UPDATE 
ON GYearMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 
  TO_TIMESTAMP_TZ('2001-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Date too late'), 
END; 

22.4.39.5. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing 22.64 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling of PL/SQL conversion functions. This could be obviated in this case by not using the ISO 8601 dateTime format and using the default Oracle external date format, but this is not shown here.

Listing 22.64. GYearMaxExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GYearMaxExclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGYearMaxExclusive 
BEFORE INSERT 
ON GYearMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= 
  TO_TIMESTAMP_TZ('2001-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYear too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGYearMaxExclusive 
BEFORE UPDATE 
ON GYearMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= 
  TO_TIMESTAMP_TZ('2001-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYear too late'), 
END; 

22.4.39.6. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing 22.65 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling of PL/SQL conversion functions. This could be obviated in this case by not using the ISO 8601 dateTime format and using the default Oracle external date format, but this is not shown here.

Listing 22.65. GYearMinInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GYearMinInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGYearMinInclusive BEFORE INSERT 
ON GYearMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < 
  TO_TIMESTAMP_TZ('2001-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYear too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGYearMinInclusive BEFORE UPDATE 
ON GYearMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < 
  TO_TIMESTAMP_TZ('2001-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYear too early'), 
END; 

22.4.39.7. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing 22.66 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling of PL/SQL conversion functions. This could be obviated in this case by not using the ISO 8601 dateTime format and using the default Oracle external date format, but this is not shown here.

Listing 22.66. GYearMinExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GYearMinExclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGYearMinExclusive 
BEFORE INSERT 
ON GYearMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= 
  TO_TIMESTAMP_TZ('2001-01-01Z+05:00',   
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYear too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGYearMinExclusive 
BEFORE UPDATE 
ON GYearMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= 
  TO_TIMESTAMP_TZ('2001-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYear too early'), 
END; 

22.4.40. gYearMonth Datatype

The gYearMonth datatype represents a Gregorian calendar month. Oracle9i now has a TIMESTAMP column datatype that represents both date and time with a time-zone offset. The database designer should confer with the XML schema designer to determine if time-zone values will exist in XML instances and, if so, whether the values need to be preserved or the gYearMonth values can be reduced to their canonical form. Listing 22.67 implements a gYearMonth table creation.

Listing 22.67. GYearMonthExample Table Creation

CREATE TABLE GYearMonthExample ( 
value TIMESTAMP WITH TIME ZONE 
); 

To insert a row into the table with a gYearMonth datatype, Oracle needs to convert it from its XML Schema format to an internal Oracle representation. Listing 22.68 is a SQL statement that inserts a gYearMonth into the GYearMonthExample table (?gYearMonthValue is the gYearMonth bind variable). Note that the 1st was arbitrarily chosen as the day of the month.

Listing 22.68. GYearMonthExample Example Insert

INSERT INTO gYearMonthExample 
(value) 
SELECT TO_TIMESTAMP_TZ(?gYearMonthValue || '01', 
           'YYYY-MM"Z"TZH:TZM DD') 
FROM dual; 

Listing 22.69 is a SQL statement that retrieves all of the values from the GYearMonthExample table.

Listing 22.69. GYearMonthExample Values Retrieval via a Select Statement

SELECT TO_CHAR(value, 'YYYY-MM"Z"TZH:TZM') 
FROM GYearMonthExample; 

22.4.40.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.40.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.40.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.40.4. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing 22.70 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions. This could be obviated in this case by not using the ISO 8601 dateTime format and using the default Oracle external date format, but this is not shown here.

Listing 22.70. GYearMonthMaxInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GYearMonthMaxInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGYearMonthMaxInclusive 
BEFORE INSERT 
ON GYearMonthMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 
  TO_TIMESTAMP_TZ('1999-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYearMonth too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGYearMonthMaxInclusive 
BEFORE UPDATE   
ON GYearMonthMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 
  TO_TIMESTAMP_TZ('1999-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYearMonth too late'), 
END; 

22.4.40.5. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing 22.71 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions. This could be obviated in this case by not using the ISO 8601 dateTime format and using the default Oracle external date format, but this is not shown here.

Listing 22.71. GYearMonthMaxExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GYearMonthMaxExclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGYearMonthMaxExclusive 
BEFORE INSERT 
ON GYearMonthMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= 
  TO_TIMESTAMP_TZ('1999-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYearMonth too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGYearMonthMaxExclusive 
BEFORE UPDATE 
ON GYearMonthMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= 
  TO_TIMESTAMP_TZ('1999-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYearMonth too late'), 
END; 

22.4.40.6. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing 22.72 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions. This could be obviated in this case by not using the ISO 8601 dateTime format and using the default Oracle external date format, but this is not shown here.

Listing 22.72. GYearMonthMinInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GYearMonthMinInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGYearMonthMinInclusive 
BEFORE INSERT 
ON GYearMonthMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < 
  TO_TIMESTAMP_TZ('1999-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYearMonth too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGYearMonthMinInclusive 
BEFORE UPDATE 
ON GYearMonthMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < 
  TO_TIMESTAMP_TZ('1999-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYearMonth too early'), 
END; 

22.4.40.7. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing 22.73 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions. This could be obviated in this case by not using the ISO 8601 dateTime format and using the default Oracle external date format, but this is not shown here.

Listing 22.73. GYearMonthMinExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GYearMonthMinExclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGYearMonthMinExclusive 
BEFORE INSERT 
ON GYearMonthMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= 
  TO_TIMESTAMP_TZ('1999-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYearMonth too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGYearMonthMinExclusive 
BEFORE UPDATE 
ON GYearMonthMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= 
  TO_TIMESTAMP_TZ('1999-01-01Z+05:00', 
          'YYYY-MM-DD"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GYearMonth too early'), 
END; 

22.4.41. time Datatype

The time datatype represents an instance of time that recurs every day. A time value may or may not include a time zone. Oracle9i now has a TIMESTAMP column datatype that represents a date, time of day, and an optional time zone. The database designer should confer with the XML schema designer to determine if time-zone values will exist in XML instances and, if so, whether the values need to be preserved or the time values can be reduced to their canonical form. Listing 22.74 implements a time table creation.

Listing 22.74. TimeExample Table Creation

CREATE TABLE TimeExample ( 
value TIMESTAMP WITH TIME ZONE 
); 

To insert a row into the table with a time datatype, Oracle needs to convert it from its XML Schema format to an internal Oracle representation. In addition, because Oracle needs a date for the insert, it is appropriate to use a date that indicates that no date was specified. Oracle has a minimum date of January 1, 4712 B.C. It is represented by the number 1 and is translated into January 1, 4712 B.C. by the ‘J’ datetime format element. A hard-coded date is especially useful when min/max inclusion/exclusion facets have been specified.

Listing 22.75 is a SQL statement that inserts a time into the TimeExample table (?timeValue is the time bind variable).

Listing 22.75. TimeExample Example Insert

INSERT INTO TimeExample 
(value) 
SELECT TO_TIMESTAMP_TZ('1 ' || ?timeValue, 
           'J HH24:MI:SS"Z"TZH:TZM') 
FROM dual; 

Listing 22.76 is a SQL statement that retrieves all of the values from the TimeExample table.

Listing 22.76. TimeExample Values Retrieval via a Select Statement

SELECT TO_CHAR(value, 'HH24:MI:SS"Z"TZH:TZM') 
FROM TimeExample; 

22.4.41.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.41.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.41.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.41.4. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing 22.77 shows how this can be enforced with a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling of PL/SQL conversion functions.

Listing 22.77. TimeMaxInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE TimeMaxInclusiveExample ( 
value TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertTimeMaxInclusiveExample 
BEFORE INSERT 
ON TimeMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > TO_TIMESTAMP_TZ('1 12:45:30Z+05:00', 
                'J HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Time too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateTimeMaxInclusiveExample 
BEFORE UPDATE 
ON TimeMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > TO_TIMESTAMP_TZ('1 12:45:30Z+05:00', 
                'J HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Time too late'), 
END; 

22.4.41.5. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing 22.78 shows how this can be enforced with a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling of PL/SQL conversion functions.

Listing 22.78. TimeMaxExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE TimeMaxExclusiveExample ( 
value TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertTimeMaxExclusiveExample 
BEFORE INSERT 
ON TimeMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= TO_TIMESTAMP_TZ('1 12:45:30Z+05:00',  
                 'J HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Time too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateTimeMaxExclusiveExample 
BEFORE UPDATE 
ON TimeMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= TO_TIMESTAMP_TZ('1 12:45:30Z+05:00', 
                 'J HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Time too late'), 
END; 

22.4.41.6. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing 22.79 shows how this can be enforced with a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling of PL/SQL conversion functions.

Listing 22.79. TimeMinInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE TimeMinInclusiveExample ( 
value TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertTimeMinInclusiveExample 
BEFORE INSERT 
ON TimeMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < TO_ TIMEZONE_TZ('1 12:45:30Z+05:00', 
                'J HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Time too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateTimeMinInclusiveExample 
BEFORE UPDATE 
ON TimeMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < TO_ TIMEZONE_TZ('1 12:45:30Z+05:00', 
                'J HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Time too early'), 
END; 

22.4.41.7. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing 22.80 shows how this can be enforced with a trigger. Note that Oracle limits the values of he check clause to constant values and does not allow calling of PL/SQL conversion functions.

Listing 22.80. TimeMinExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE TimeMinExclusiveExample ( 
value TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertTimeMinExclusiveExample 
BEFORE INSERT 
ON TimeMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= TO_TIMEZONE_TZ('1 12:45:30Z+05:00', 
                'J HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Time too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateTimeMinExclusiveExample 
BEFORE UPDATE 
ON TimeMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= TO_TIMEZONE_TZ('1 12:45:30Z+05:00', 
                'J HH24:MI:SS"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Time too early'), 
END; 

22.4.42. gMonth Datatype

The gMonth datatype represents a Gregorian calendar month that recurs every year. Without the optional time zone, gMonth is essentially an integer datatype with a minInclusive of 0 and a maxInclusive of 11. Refer to Sections 22.4.14 and 22.4.15 for details implementing gMonth when no time-zone offsets are required.

If time-zone offsets are required, the representation becomes much more complex. Oracle9i now has a TIMESTAMP column datatype that represents both date and time with a time-zone offset. The database designer should confer with the XML schema designer to determine if time-zone values will exist in XML instances and, if so, whether the values need to be preserved or the gMonth values can be reduced to their canonical form.

Listing 22.81 implements a gMonth table creation.

Listing 22.81. GMonthExample Table Creation

CREATE TABLE GMonthExample ( 
value TIMESTAMP WITH TIME ZONE 
); 

To insert a row into the table with a gMonth datatype, Oracle needs to convert the datatype from its XML Schema format to an internal Oracle representation. In addition, because Oracle needs a date for the insert, it is appropriate to use a date that indicates that no date was specified. Oracle has a minimum date of January 1, 4712 B.C. It is represented by the number 1 and is translated into January 1, 4712 B.C. by the ‘J’ dateTime format element. A hard-coded date is especially useful when min/max inclusion/exclusion facets have been specified. Listing 22.82 is a SQL statement that inserts a gMonth into the GMonthExample table (?gMonthValue is a gMonth bind variable without the time zone and ?timezoneValue is the time-zone bind variable). FROM_TZ converts a time zone without a time-zone indicator to one with a time-zone indicator.

Listing 22.82. GMonthExample Example Insert

INSERT INTO gMonthExample 
(value) 
SELECT FROM_TZ(CAST(ADD_MONTHS(TO_DATE('1', 'J'), 
               ?gMonthValue - 1) 
        AS TIMESTAMP), ?timezoneValue) 
FROM dual; 

Listing 22.83 is a SQL statement that retrieves all of the values from the GMonthExample table.

Listing 22.83. GMonthExample Values Retrieval via a Select Statement

SELECT TO_CHAR(value, 'MM"Z"TZH:TZM') 
FROM GMonthExample; 

22.4.42.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.42.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.42.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.42.4. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing 22.84 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.84. GMonthMaxInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GMonthMaxInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGMonthMaxInclusive 
BEFORE INSERT 
ON GMonthMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE('1', 'J'), 10) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonth too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGMonthMaxInclusive 
BEFORE UPDATE 
ON GMonthMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE('1', 'J'), 10) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, ''GMonth too late'), 
END; 

22.4.42.5. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing 22.85 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.85. GMonthMaxExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GMonthMaxExclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGMonthMaxExclusive 
BEFORE INSERT 
ON GMonthMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE('1', 'J'), 10) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonth too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGMonthMaxExclusive 
BEFORE UPDATE 
ON GMonthMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE('1', 'J'), 10) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonth too late'), 
END; 

22.4.42.6. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing 22.86 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.86. GMonthMinInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GMonthMinInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
);   
CREATE OR REPLACE TRIGGER InsertGMonthMinInclusive 
BEFORE INSERT 
ON GMonthMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE('1', 'J'), 10) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonth too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGMonthMinInclusive 
BEFORE UPDATE 
ON GMonthMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE('1', 'J'), 10) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonth too early'), 
END; 

22.4.42.7. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing 22.87 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.87. GmonthMinExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GMonthMinExclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGMonthMinExclusive 
BEFORE INSERT 
ON GMonthMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE('1', 'J'), 10) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonth too early'), 
END;  

CREATE OR REPLACE TRIGGER UpdateGMonthMinExclusive 
BEFORE UPDATE 
ON GMonthMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE('1', 'J'), 10) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonth too early'), 
END; 

22.4.43. gDay Datatype

The gDay datatype represents a Gregorian calendar day of the month. Without the optional time zone, gDay is essentially an integer datatype that has a minInclusive of 1 and a maxExclusive of 31. Refer to Sections 22.4.14 and 22.4.15 for details implementing gDay when no time-zone offsets are required.

If time-zone offsets are required, the representation becomes much more complex. Oracle9i now has a TIMESTAMP column datatype that represents both date and time with a time zone. The database designer should confer with the XML schema designer to determine if time-zone values will exist in XML instances and, if so, whether the values need to be preserved or the gDay values can be reduced to their canonical form. Listing 22.88 implements a gDay table creation.

Listing 22.88. GDayExample Table Creation

CREATE TABLE GDayExample ( 
value TIMESTAMP WITH TIME ZONE 
); 

To insert a row into the table with a gDay datatype, Oracle needs to convert the datatype from its XML Schema format to an internal Oracle representation. Listing 22.89 is a SQL statement that inserts a gDay into the GDayExample table (?gDayValue is a gDay bind variable).

Listing 22.89. GDayExample Example Insert

INSERT INTO gDayExample 
(value) 
BEGIN 
SELECT TO_TIMESTAMP_TZ(?gDayValue, 'J"Z"TZH:TZM') 
FROM dual; 

Listing 22.90 is a SQL statement that retrieves all of the values from the GDayExample table.

Listing 22.90. GDayExample Values Retrieval via a Select Statement

SELECT TO_CHAR(value, 'DD"Z"TZH:TZM') 
FROM GDayExample; 

22.4.43.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.43.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.43.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.43.4. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing 22.91 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.91. GDayMaxInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GDayMaxInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGDayMaxInclusive 
BEFORE INSERT 
ON GDayMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > TO_TIMESTAMP_TZ('20Z05:00', 'J"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GDay too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGDayMaxInclusive 
BEFORE UPDATE 
ON GDayMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > TO_TIMESTAMP_TZ('20Z05:00', 'J"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Date too late'), 
END; 

22.4.43.5. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing 22.92 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.92. GDayMaxExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GDayMaxExclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGDayMaxExclusive 
BEFORE INSERT 
ON GDayMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= TO_TIMESTAMP_TZ('20Z05:00', 'J"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GDay too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGDayMaxExclusive 
BEFORE UPDATE 
ON GDayMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= TO_TIMESTAMP_TZ('20Z05:00', 'J"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GDay too late'), 
END; 

22.4.43.6. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing 22.93 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.93. GDayMinInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GDayMinInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGDayMinInclusive 
BEFORE INSERT 
ON GDayMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < TO_TIMESTAMP_TZ('20Z05:00', 'J"Z"TZH:TZM'))   
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GDay too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGDayMinInclusive 
BEFORE UPDATE 
ON GDayMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < TO_TIMESTAMP_TZ('20Z05:00', 'J"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GDay too early'), 
END; 

22.4.43.7. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing 22.94 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.94. GDayMinInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GDayMinExclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGDayMinExclusive 
BEFORE INSERT 
ON GDayMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= TO_TIMESTAMP_TZ('20Z05:00', 'J"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GDay too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGDayMinExclusive 
BEFORE UPDATE 
ON GDayMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= TO_TIMESTAMP_TZ('20Z05:00', 'J"Z"TZH:TZM')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GDay too early'), 
END; 

22.4.44. gMonthDay Datatype

The gMonthDay datatype represents a Gregorian calendar day in a year. Oracle9i now has a TIMESTAMP column datatype that represents both date and time with a time-zone offset. The database designer should confer with the XML schema designer to determine if time-zone values will exist in XML instances and, if so, whether the values need to be preserved or the gMonthDay values can be reduced to their canonical form. Listing 22.95 implements a gMonthDay table creation.

Listing 22.95. GMonthDayExample Table Creation

CREATE TABLE GMonthDayExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

To insert a row into the table with a gMonthDay datatype, Oracle needs to convert the datatype from its XML Schema format to an internal Oracle representation.

Listing 22.96 is a SQL statement that inserts a gMonthDay into the GMonthDayExample table, including offsetting for the time-zone indicator. In addition, because Oracle automatically uses today’s year for the insert by default, it is appropriate to use a date that indicates that no date was specified. Oracle has a minimum date of January 1, 4712 B.C. It is represented by the number 1 and is translated into January 1, 4712 B.C. by the ‘J’ dateTime format element.

Listing 22.96. GMonthDayExample Example Insert

INSERT INTO GMonthDayExample 
(value) 
SELECT FROM_TZ(CAST(ADD_MONTHS(TO_DATE(?dayValue, 'J'), 
          ?monthValue - 1) 
    AS TIMESTAMP), ?timezoneValue) 
FROM dual; 

The ?dayValue bind variable is the day portion of a gMonthDay, the ?monthValue bind variable is the month portion of a gMonthDay, and the ?timeZone bind variable is the time-zone portion in a gMonthDay.

Listing 22.97 is a SQL statement that retrieves all of the values from the GMonthDayExample table.

Listing 22.97. GMonthDayExample Values Retrieval via a Select Statement

SELECT TO_CHAR(value, 'MM-DD"Z"TZH:TZM') 
FROM GMonthDayExample; 

22.4.44.1. pattern Constraining Facet

Refer to Section 22.2.1 for design and implementation issues.

22.4.44.2. enumeration Constraining Facet

Refer to Section 22.2.2 for design and implementation issues.

22.4.44.3. whiteSpace Constraining Facet

Refer to Section 22.2.3 for design and implementation issues.

22.4.44.4. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing 22.98 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.98. GMonthDayMaxInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GMonthDayMaxInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGMonthDayMaxInclusive 
BEFORE INSERT 
ON GMonthDayMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE(5, 'J'), 8) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonthDay too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGMonthDayMaxInclusive 
BEFORE UPDATE 
ON GMonthDayMaxInclusiveExample 
FOR EACH ROW 
WHEN (new.value > 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE(5, 'J'), 8) 
    AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'Date too late'), 
END; 

22.4.44.5. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing 22.99 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.99. GMonthDayMaxExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GMonthDayMaxExclusiveExample ( 
value TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGMonthDayMaxExclusive 
BEFORE INSERT 
ON GMonthDayMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= 
 FROM_TZ(CAST(ADD_MONTHS(TO_DATE(5, 'J'), 8) 
    AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonthDay too late'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGMonthDayMaxExclusive 
BEFORE UPDATE 
ON GMonthDayMaxExclusiveExample 
FOR EACH ROW 
WHEN (new.value >= 
 FROM_TZ(CAST(ADD_MONTHS(TO_DATE(5, 'J'), 8) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonthDay too late'), 
END; 

22.4.44.6. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing 22.100 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.100. GMonthDayMinInclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GMonthDayMinInclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
);   

CREATE OR REPLACE TRIGGER InsertGMonthDayMinInclusive 
BEFORE INSERT 
ON GMonthDayMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE(5, 'J'), 8) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonthDay too early'), 
END; 

CREATE OR REPLACE TRIGGER UpdateGMonthDayMinInclusive 
BEFORE UPDATE 
ON GMonthDayMinInclusiveExample 
FOR EACH ROW 
WHEN (new.value < 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE(5, 'J'), 8) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonthDay too early'), 
END; 

22.4.44.7. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing 22.101 shows how this can be enforced as a trigger. Note that Oracle limits the values of the check clause to constant values and does not allow calling PL/SQL conversion functions.

Listing 22.101. GMonthDayMinExclusiveExample Table Creation with Insert and Update Triggers

CREATE TABLE GMonthDayMinExclusiveExample ( 
value  TIMESTAMP WITH TIME ZONE 
); 

CREATE OR REPLACE TRIGGER InsertGMonthDayMinExclusive 
BEFORE INSERT 
ON GMonthDayMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE(5, 'J'), 8) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonthDay too early'), 
END;  

CREATE OR REPLACE TRIGGER UpdateGMonthDayMinExclusive 
BEFORE UPDATE 
ON GMonthDayMinExclusiveExample 
FOR EACH ROW 
WHEN (new.value <= 
  FROM_TZ(CAST(ADD_MONTHS(TO_DATE(5, 'J'), 8) 
       AS TIMESTAMP), '+05:00')) 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'GMonthDay too early'), 
END;