D.2. Discussion of the Consequences

The database representation and validation for XML Schema datatypes is affected by using Oracle8i. In particular, the normalizedString datatype and all the subtypes derived by restriction are affected, as well as the time datatype, dateTime datatype, date datatype, gMonth datatype, gYear datatype, gYearMonth datatype, gDay datatype, and gMonthDay datatype. gMonth, gYear, and gDay are unaffected if time-zone offsets are not required, because they can simply be represented as positiveInteger datatypes with minInclusive and maxInclusive facet restrictions. Refer to Section 22.4.37 for more information about the need to implement time-zone offsets.

Each of these XML Schema datatypes is discussed in this section, with various implementation options, when applicable.

D.2.1. normalizedString Datatype and Subtypes Derived by Restriction

Without the support for Unicode strings in Oracle9i, the PL/SQL function UNISTR is not implemented. To validate that a set of characters is a normalizedString datatype, the CHR function must be used instead. Unfortunately, the CHR function is based on the type of machine used (ASCII, EBCDIC, and so on) and on the character set defined for the database. Listing D.1 iterates Listing 22.10 in Oracle9i, and Listing D.2 maps it to Oracle8i when the character set is defined as ‘WE8ISO8859P1’.

Listing D.1. Oracle9i 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 D.2. Oracle8i NormalizedStringExample Table Creation with a Column Constraint
CREATE TABLE NormalizedStringExample ( 
value  VARCHAR2(30) 
    CHECK (INSTR(value, CHR(13)) = 0 AND 
       INSTR(value, CHR(10)) = 0 AND 
       INSTR(value, CHR(9)) = 0) 
);

D.2.2. dateTime Datatype

Without native time-zone support, the XML Schema dateTime datatype can be represented as a database DATE column datatype with an INTERVAL offset. This does not maintain the time zone of the original dateTime value. Listing D.3 implements a dateTime datatype table creation.

Listing D.3. DateTimeExample Table Creation
CREATE TABLE DateTimeExample ( 
value  DATE 
);

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 D.4 is a SQL statement that inserts a dateTime value into the DateTimeExample table excluding offsetting for the time-zone indicator. When the SQL executes, the ?datetimeValue bind variable is the dateTime value.

Listing D.4. DateTimeExample Example Insert with No Time-Zone Offset
INSERT INTO DateTimeExample 
(value) 
SELECT TO_DATE(SUBSTR(?datetimeValue, 1, 19), 
       'YYYY-MM-DD"T"HH24:MI:SS') 
FROM  dual;

Listing D.5 is a SQL statement that inserts a dateTime into the DateTimeExample table, including the offset for the time-zone indicator. When the SQL executes, the ?timeZoneSign bind variable is the twentieth character in a dateTime value, the ?datetimeValue bind variable is the first 19 characters in a dateTime value, and the ?timeZone bind variable is the last 4 characters in a dateTime value.

Listing D.5. DateTimeExample Table Insert with a Time-Zone Offset
INSERT INTO DateTimeExample 
(value) 
SELECT (CASE WHEN ?timeZoneSign= '-' 
      THEN TO_DATE(?datetimeValue, 'YYYY-MM-DD"T"HH24:MI:SS') 
        - INTERVAL ?timeZone HOUR TO MINUTE 
      ELSE TO_DATE(?datetimeValue, 'YYYY-MM-DD"T"HH24:MI:SS') 
        + INTERVAL ?timeZone HOUR TO MINUTE 
   END) 
FROM  dual;

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

Listing D.6. DateTimeExample Values Retrieval via a Select Statement
SELECT TO_CHAR(value, 'YYYY-MM-DD"T"HH24:MI:SS') 
FROM  DateTimeExample;

D.2.2.1. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing D.7 shows how this can be enforced as a column constraint.

Listing D.7. DatetimeMaxInclusiveExample Table Creation with a Column Constraint
CREATE TABLE DatetimeMaxInclusiveExample ( 
value DATE 
CHECK (value <= TO_DATE('2001-12-24T12:23:45', 
            'YYYY-MM-DD"T"HH24:MI:SS') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.2.2. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing D.8 shows how this can be enforced as a column constraint.

Listing D.8. DatetimeMaxExclusiveExample Table Creation with a Column Constraint
CREATE TABLE DatetimeMaxExclusiveExample ( 
value DATE   
CHECK (value < TO_DATE('2001-12-24T12:23:45', 
            'YYYY-MM-DD"T"HH24:MI:SS') 
    + INTERVAL '05:00' HOUR TO MINUTE) 

);

D.2.2.3. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing D.9 shows how this can be enforced as a column constraint.

Listing D.9. DatetimeMinInclusiveExample Table Creation with a Column Constraint
CREATE TABLE DatetimeMinInclusiveExample ( 
value DATE 
CHECK (value >= TO_DATE('2001-12-24T12:23:45', 
            'YYYY-MM-DD"T"HH24:MI:SS') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.2.4. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing D.10 shows how this can be enforced as a column constraint.

Listing D.10. DatetimeMinExclusiveExample Table Creation with a Column Constraint
CREATE TABLE DatetimeMinExclusiveExample ( 
value DATE 
CHECK (value > TO_DATE('2001-12-24T12:23:45', 
            'YYYY-MM-DD"T"HH24:MI:SS') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.3. date Datatype

Without native time-zone support, the XML Schema date datatype can be represented as a database DATE column datatype with an INTERVAL offset. This does not maintain the time-zone of the original date value. Listing D.11 implements a date datatype table creation.

Listing D.11. DateExample Table Creation
CREATE TABLE DateExample ( 
value  DATE 
);

To insert a row into the table with a date datatype, Oracle needs to convert a value from its ISO 8601 standard format to an internal Oracle representation. Listing D.12 is a SQL statement that inserts a date into the DateExample table, excluding offsetting for the time-zone indicator. When the SQL executes, the ?dateValue bind variable is the date value.

Listing D.12. DateExample Example Insert with No Time-Zone Offset
INSERT INTO DateExample 
(value) 
SELECT TO_DATE(SUBSTR(?dateValue, 1, 10), 'YYYY-MM-DD') 
FROM  dual;

Listing D.13 is a SQL statement that inserts a date into the DateExample table, including the offset for the time-zone indicator. When the SQL executes, the ?timeZoneSign bind variable is the eleventh character in a date value, the ?dateValue bind variable is the first 10 characters in a date value, and the ?timeZone bind variable is the last 4 characters in a date value.

Listing D.13. DateExample Table Insert with a Time-Zone Offset
INSERT INTO DateExample 
(value) 
SELECT (CASE WHEN ?timeZoneSign= '-' 
      THEN TO_DATE(?dateValue, 'YYYY-MM-DD') 
        - INTERVAL ?timeZone HOUR TO MINUTE 
      ELSE TO_DATE(?dateValue, 'YYYY-MM-DD') 
        + INTERVAL ?timeZone HOUR TO MINUTE 
   END) 
FROM  dual;

Listing D.14 is a SQL statement that retrieves of the values from the DateExample table.

Listing D.14. DateExample Values Retrieval via a Select Statement
SELECT TO_CHAR(value, 'YYYY-MM-DD"T"HH24:MI:SS') 
FROM  DateTimeExample;

D.2.3.1. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing D.15 shows how this can be enforced as a column constraint.

Listing D.15. DateMaxInclusiveExample Table Creation with a Column Constraint
CREATE TABLE DateMaxInclusiveExample ( 
value DATE 
CHECK (value <= TO_DATE('2001-12-24T12:23:45', 
            'YYYY-MM-DD"T"HH24:MI:SS') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.3.2. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing D.16 shows how this can be enforced as a column constraint.

Listing D.16. DateMaxExclusiveExample Table Creation with a Column Constraint
CREATE TABLE DateMaxExclusiveExample ( 
value DATE 
CHECK (value < TO_DATE('2001-12-24T12:23:45', 
            'YYYY-MM-DD"T"HH24:MI:SS') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.3.3. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing D.17 shows how this can be enforced as a column constraint.

Listing D.17. DateMinInclusiveExample Table Creation with a Column Constraint
CREATE TABLE DateMinInclusiveExample ( 
value DATE 
CHECK (value >= TO_DATE('2001-12-24T12:23:45', 
            'YYYY-MM-DD"T"HH24:MI:SS') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.3.4. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing D.18 shows how this can be enforced as a column constraint.

Listing D.18. DateMinExclusiveExample Table Creation with a Column Constraint
CREATE TABLE DateMinExclusiveExample ( 
value DATE 
CHECK (value > TO_DATE('2001-12-24T12:23:45',  
            'YYYY-MM-DD"T"HH24:MI:SS') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.4. gYear Datatype

Without native time-zone support, the gYear datatype can be represented as a DATE column datatype with an INTERVAL offset. This does not maintain the time zone of the original gYear value. Listing D.19 implements a gYear table creation.

Listing D.19. GYearExample Table Creation
CREATE TABLE GYearExample ( 
value  DATE 
);

To insert a row into the table with a gYear datatype, Oracle needs to convert a value from its XML Schema format to an internal Oracle representation. Listing D.20 is a SQL statement that inserts a gYear into the GYearExample table. When the SQL executes, the ?timeZoneSign bind variable is the third character in a gYear value, the ?gYearValue bind variable is the first 4 characters in a gYear value, and the ?timeZone bind variable is the last 4 characters in a gYear value. Note that when a month is not specified, Oracle chooses the current month. However, Oracle defaults to the first day of the month if the day is not specified.

Listing D.20. GYearExample Example Insert
INSERT INTO gYearExample 
SELECT (CASE WHEN ?timeZoneSign= '-' 
      THEN TO_DATE(?gYearValue || '-01', 'YYYY-MM') 
        - INTERVAL ?timeZone HOUR TO MINUTE 
      ELSE TO_DATE(?gYearValue || '-01', 'YYYY-MM') 
        + INTERVAL ?timeZone HOUR TO MINUTE 
   END) 
FROM  dual;

Listing D.21 is a SQL statement that retrieves of the values from the GYearExample table.

Listing D.21. GYearExample Values Retrieval via a Select Statement
SELECT TO_CHAR(value, 'IYYY-MM-DD"T"HH24:MI:SS') 
FROM  GYearExample;

D.2.4.1. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing D.22 shows how this can be enforced as a column constraint.

Listing D.22. GYearMaxInclusiveExample Table Creation with a Column Constraint
CREATE TABLE GYearMaxInclusiveExample ( 
value DATE 
CHECK (value <= TO_DATE(1999 || '-01', 'YYYY-MM') 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.4.2. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing D.23 shows how this can be enforced as a column constraint.

Listing D.23. GYearMaxExclusiveExample Table Creation with a Column Constraint
CREATE TABLE GYearMaxExclusiveExample ( 
value DATE 
CHECK (value < TO_DATE(1999 || '-01', 'YYYY-MM') 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.4.3. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing D.24 shows how this can be enforced as a column constraint.

Listing D.24. GYearMinInclusiveExample Table Creation with a Column Constraint
CREATE TABLE GYearMinInclusiveExample ( 
value DATE 
CHECK (value >= TO_DATE(1999 || '-01', 'YYYY-MM') 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.4.4. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing D.25 shows how this can be enforced as a column constraint.

Listing D.25. GYearMinExclusiveExample Table Creation with a Column Constraint
CREATE TABLE GYearMinExclusiveExample ( 
value DATE  
CHECK (value > TO_DATE(1999 || '-01', 'YYYY-MM') 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.5. gYearMonth Datatype

Without native time-zone support, the gYearMonth datatype can be represented as a DATE column datatype with an INTERVAL offset. This does not maintain the time zone of the original gYearMonth value. Listing D.26 implements a gYearMonth table creation.

Listing D.26. GYearMonthExample Table Creation
CREATE TABLE GYearMonthExample ( 
value  DATE 
);

To insert a row into the table with a gYearMonth datatype, Oracle needs to convert a value from its ISO 8601 standard format to an internal Oracle representation. Listing D.27 inserts a gYearMonth into the GYearMonthExample table, excluding offsetting for the time-zone indicator. When the SQL executes, the ?gYearMonthValue bind variable is the gYearMonth value.

Listing D.27. GYearMonthExample Example Insert with No Time-Zone Offset
INSERT INTO GYearMonthExample 
(value) 
SELECT TO_DATE(SUBSTR(?gYearMonthValue, 1, 7), 'YYYY-MM') 
FROM  dual;

Listing D.28 inserts a gYearMonth into the GYearMonthExample table, including the offset for the time-zone indicator. When the SQL executes, the ?timeZoneSign bind variable is the eighth character in a gYearMonth value, the ?gYearMonthValue bind variable is the first 7 characters in a gYearMonth value, and the ?timeZone bind variable is the last 4 characters in a gYearMonth value.

Listing D.28. GYearMonthExample Table Insert with a Time-Zone Offset
INSERT INTO GYearMonthExample 
SELECT (CASE WHEN ?timeZoneSign= '-' 
      THEN TO_DATE(?gYearMonthValue, 'YYYY-MM') 
        - INTERVAL ?timeZone HOUR TO MINUTE 
      ELSE TO_DATE(?gYearMonthValue, 'YYYY-MM') 
        + INTERVAL ?timeZone HOUR TO MINUTE 
   END) 
FROM  dual;

To insert a row into the table with a gYearMonth datatype, Oracle needs to convert a value from its XML Schema format to an internal Oracle representation. Listing D.29 is a SQL statement that inserts a gYearMonth into the GYearMonthExample table.

Listing D.29. GYearMonthExample Example Insert
INSERT INTO GYearMonthExample 
SELECT (CASE WHEN ?timeZoneSign= '-' 
             THEN TO_DATE(?gYearMonthValue, 'YYYY-MM') 
                  - INTERVAL ?timeZone HOUR TO MINUTE 
             ELSE TO_DATE(?gYearMonthValue, 'YYYY-MM') 
                  + INTERVAL ?timeZone HOUR TO MINUTE 
        END) 
FROM   dual;

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

Listing D.30. GYearMonthExample Values Retrieval via a Select Statement
SELECT TO_CHAR(value, 'IYYY-MM-DD"T"HH24:MI:SS') 
FROM  GYearMonthExample;

D.2.5.1. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing D.31 shows how this can be enforced as a column constraint.

Listing D.31. GYearMonthMaxInclusiveExample Table Creation with a Column Constraint
CREATE TABLE GYearMonthMaxInclusiveExample ( 
value DATE 
CHECK (value <= TO_DATE('2001-12', 'YYYY-MM') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.5.2. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing D.32 shows how this can be enforced as a column constraint.

Listing D.32. GYearMonthMaxExclusiveExample Table Creation with a Column Constraint
CREATE TABLE GYearMonthMaxExclusiveExample ( 
value DATE 
CHECK (value < TO_DATE('2001-12', 'YYYY-MM') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.5.3. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing D.33 shows how this can be enforced as a column constraint.

Listing D.33. GYearMonthMinInclusiveExample Table Creation with a Column Constraint
CREATE TABLE GYearMonthMinInclusiveExample ( 
value DATE 
CHECK (value >= TO_DATE('2001-12', 'YYYY-MM') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.5.4. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing D.34 shows how this can be enforced as a column constraint.

Listing D.34. GYearMonthMinExclusiveExample Table Creation with a Column Constraint
CREATE TABLE GYearMonthMinExclusiveExample ( 
value DATE 
CHECK (value > TO_DATE('2001-12', 'YYYY-MM') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.6. time Datatype

Without native time-zone support, the XML Schema time datatype can be represented as a database DATE column datatype with an INTERVAL offset. This does not maintain the time zone of the original time value. Listing D.35 implements a time datatype table creation.

Listing D.35. TimeExample Table Creation
CREATE TABLE TimeExample ( 
value  DATE 
);

To insert a row into the table with a time datatype, Oracle needs to convert a value 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 D.36 is a SQL statement that inserts a time into the TimeExample table, excluding offsetting for the time-zone indicator. When the SQL executes, the ?timeValue bind variable is the time value.

Listing D.36. TimeExample Table Insert with No Time-Zone Offset
INSERT INTO TimeExample 
(value) 
SELECT TO_DATE('1 ' || SUBSTR(?timeValue, 1, 8), 'J HH24:MI:SS') 
FROM  dual;

Listing D.37 is a SQL statement that inserts a time into the TimeExample table, including the offset for the time-zone indicator. When the SQL executes, the ?timeZoneSign bind variable is the ninth character in a time value, the ?timeValue bind variable is the first 8 characters in a time value, and the ?timeZone bind variable is the last 4 characters in a time value.

Listing D.37. TimeExample Table Insert with a TimeZone Offset
INSERT INTO TimeExample 
(value) 
SELECT (CASE WHEN ?timeZoneSign = '-' 
      THEN TO_DATE('1 ' || ?timeValue, 'J HH24:MI:SS') 
        - INTERVAL ?timeZone HOUR TO MINUTE 
      ELSE TO_DATE('1 ' || ?timeValue, 'J HH24:MI:SS') 
        + INTERVAL ?timeZone HOUR TO MINUTE 
    END) 
FROM  dual;

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

Listing D.38. TimeExample Values Retrieval via a Select Statement
SELECT TO_CHAR(value, 'HH24:MI:SS') 
FROM  TimeExample;

D.2.6.1. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing D.39 shows how this can be enforced as a column constraint.

Listing D.39. TimeMaxInclusiveExample Table Creation with a Column Constraint
CREATE TABLE TimeMaxInclusiveExample ( 
value DATE 
CHECK (value <= TO_DATE('1 12:45:30', 'J HH24:MI:SS') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.6.2. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing D.40 shows how this can be enforced as a column constraint.

Listing D.40. TimeMaxExclusiveExample Table Creation with a Column Constraint
CREATE TABLE TimeMaxExclusiveExample ( 
value DATE 
CHECK (value < TO_DATE('1 12:45:30', 'J HH24:MI:SS') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.6.3. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing D.41 shows how this can be enforced as a column constraint.

Listing D.41. TimeMinInclusiveExample Table Creation with a Column Constraint
CREATE TABLE TimeMinInclusiveExample ( 
value DATE 
CHECK (value >= TO_DATE('1 12:45:30', 'J HH24:MI:SS') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.6.4. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing D.42 shows how this can be enforced as a column constraint.

Listing D.42. TimeMinExclusiveExample Table Creation with a Column Constraint
CREATE TABLE TimeMinExclusiveExample ( 
value DATE 
CHECK (value > TO_DATE('1 12:45:30', 'J HH24:MI:SS') 
    + INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.7. gMonth Datatype

Without native time-zone support, the XML Schema gMonth datatype can be represented as a database DATE column datatype with an INTERVAL offset. This does not maintain the time zone of the original gMonth value. Listing D.43 implements a gMonth table creation.

Listing D.43. GMonthExample Table Creation
CREATE TABLE GMonthExample ( 
value  DATE 
);

To insert a row into the table with a gMonth datatype, Oracle needs to convert a value from its XML Schema format to an internal Oracle representation. Listing D.44 is a SQL statement that inserts a gMonth into the GMonthExample table. When the SQL executes, the ?timeZoneSign bind variable is the third character in a gMonth value, the ?gMonthValue bind variable is the first 2 characters in a gMonth value, and the ?timeZone bind variable is the last 4 characters in a gMonth value.

Listing D.44. GMonthExample Example Insert
INSERT INTO gMonthExample 
SELECT (CASE WHEN ?timeZoneSign= '-' 
      THEN ADD_MONTHS(TO_DATE('1', 'J'), ?gMonthValue - 1) 
        - INTERVAL ?timeZone HOUR TO MINUTE 
      ELSE ADD_MONTHS(TO_DATE('1', 'J'), ?gMonthValue - 1) 
        + INTERVAL ?timeZone HOUR TO MINUTE 
    END) 
FROM  dual;

Listing D.45 is a SQL statement that retrieves of the values from the GMonthExample table.

Listing D.45. GMonthExample Values Retrieval via a Select Statement
SELECT TO_CHAR(value, 'IYYY-MM-DD"T"HH24:MI:SS') 
FROM  GMonthExample;

D.2.7.1. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing D.46 shows how this can be enforced as a column constraint.

Listing D.46. GMonthMaxInclusiveExample Table Creation with a Column Constraint
CREATE TABLE GMonthMaxInclusiveExample ( 
value DATE 
CHECK (value <= ADD_MONTHS(TO_DATE('1', 'J'), 10) 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.7.2. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing D.47 shows how this can be enforced as a column constraint.

Listing D.47. GMonthMaxExclusiveExample Table Creation with a Column Constraint
CREATE TABLE GMonthMaxExclusiveExample ( 
value DATE 
CHECK (value < ADD_MONTHS(TO_DATE('1', 'J'), 10) 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.7.3. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing D.48 shows how this can be enforced as a column constraint.

Listing D.48. GMonthMinInclusiveExample Table Creation with a Column Constraint
CREATE TABLE GMonthMinInclusiveExample ( 
value DATE 
CHECK (value >= ADD_MONTHS(TO_DATE('1', 'J'), 10) 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.7.4. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing D.49 shows how this can be enforced as a column constraint.

Listing D.49. GMonthMinExclusiveExample Table Creation with a Column Constraint
CREATE TABLE GMonthMinExclusiveExample ( 
value DATE 
CHECK (value > ADD_MONTHS(TO_DATE('1', 'J'), 10) 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.8. gDay Datatype

Without native time-zone support, the gDay datatype can be represented as a DATE column datatype with an INTERVAL offset. This does not maintain the time zone of the original gDay value. Listing D.50 implements a gDay table creation.

Listing D.50. GDayExample Table Creation
CREATE TABLE GDayExample ( 
value  DATE 
);

To insert a row into the table with a gDay datatype, Oracle needs to convert a value from its XML Schema format to an internal Oracle representation. Listing D.51 is a SQL statement that inserts a gDay into the GDayExample table. When the SQL executes, the ?timeZoneSign bind variable is the sixth character in a gDay value, the ?gDayValue bind variable is the fourth and fifth characters in a gDay value, and the ?timeZone bind variable is the last 4 characters in a gDay value.

Listing D.51. GDayExample Example Insert
INSERT INTO gDayExample 
SELECT (CASE WHEN ?timeZoneSign= '-' 
      THEN TO_DATE('1', 'J') + ?gDayValue 
        - INTERVAL ?timeZone HOUR TO MINUTE 
      ELSE TO_DATE('1', 'J') + ?gDayValue 
        + INTERVAL ?timeZone HOUR TO MINUTE 
   END) 
FROM  dual;

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

Listing D.52. GDayExample Values Retrieval via a Select Statement
SELECT TO_CHAR(value, 'IYYY-MM-DD"T"HH24:MI:SS') 
FROM  GDayExample;

D.2.8.1. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing D.53 shows how this can be enforced as a column constraint.

Listing D.53. GDayMaxInclusiveExample Table Creation with a Column Constraint
CREATE TABLE GDayMaxInclusiveExample ( 
value DATE 
CHECK (value <= TO_DATE('1', 'J') + 5 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.8.2. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing D.54 shows how this can be enforced as a column constraint.

Listing D.54. GDayMaxExclusiveExample Table Creation with a Column Constraint
CREATE TABLE GDayMaxExclusiveExample ( 
value DATE 
CHECK (value < TO_DATE('1', 'J') + 5 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.8.3. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing D.55 shows how this can be enforced as a column constraint.

Listing D.55. GDayMinInclusiveExample Table Creation with a Column Constraint
CREATE TABLE GDayMinInclusiveExample ( 
value DATE 
CHECK (value >= TO_DATE('1', 'J') + 5 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.8.4. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing D.56 shows how this can be enforced as a column constraint.

Listing D.56. GDayMinExclusiveExample Table Creation with a Column Constraint
CREATE TABLE GDayMinExclusiveExample ( 
value DATE 
CHECK (value > TO_DATE('1', 'J') + 
    5 - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.9. gMonthDay Datatype

Without native time-zone support, the gMonthDay datatype can be represented as a DATE column datatype with an INTERVAL offset. This does not maintain the time zone of the original gMonthDay value. Listing D.57 implements a gMonthDay table creation.

Listing D.57. GMonthDayExample Table Creation
CREATE TABLE GMonthDayExample ( 
value  DATE 
);

To insert a row into the table with a gMonthDay datatype, Oracle needs to convert a value from its XML Schema format to an internal Oracle representation. Listing D.58 is a SQL statement that inserts a gMonthDay into the GMonthDayExample table. When the SQL executes, the ?timeZoneSign bind variable is the eighth character in a GMonthDay value, the ?GMonthDayValue bind variable is the first 7 characters in a GMonthDay value, and the ?timeZone bind variable is the last 4 characters in a GMonthDay value.

Listing D.58. GMonthDayExample Example Insert
INSERT INTO GMonthDayExample 
SELECT (CASE WHEN ?timeZoneSign = '-' 
      THEN ADD_MONTHS(TO_DATE('1', 'J'), 
         SUBSTR(?GMonthDayValue, 3, 2) - 1) + 
         SUBSTR(?GMonthDayValue, 6, 2) -
        INTERVAL ?timeZone HOUR TO MINUTE 
      ELSE ADD_MONTHS(TO_DATE('1', 'J'), 
         SUBSTR(?GMonthDayValue, 3, 2) - 1) + 
         SUBSTR(?GMonthDayValue, 6, 2) + 
        INTERVAL ?timeZone HOUR TO MINUTE 
   END) 
FROM  dual;

Listing D.59 is a SQL statement that retrieves of the values from the GMonthDayExample table.

Listing D.59. GMonthDayExample Values Retrieval via a Select Statement
SELECT TO_CHAR(value, 'IYYY-MM-DD"T"HH24:MI:SS') 
FROM  GMonthDayExample;

D.2.9.1. maxInclusive Constraining Facet

There is no built-in database support for the maxInclusive constraining facet, but Listing D.60 shows how this can be enforced as a column constraint.

Listing D.60. GMonthDayMaxInclusiveExample Table Creation with a Column Constraint
CREATE TABLE GMonthDayMaxInclusiveExample ( 
value DATE 
CHECK (value <= TO_DATE('1', 'J') + 5 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.9.2. maxExclusive Constraining Facet

There is no built-in database support for the maxExclusive constraining facet, but Listing D.61 shows how this can be enforced as a column constraint.

Listing D.61. GMonthDayMaxExclusiveExample Table Creation with a Column Constraint
CREATE TABLE GMonthDayMaxExclusiveExample ( 
value DATE 
CHECK (value < ADD_MONTHS(TO_DATE('1', 'J'), 5) + 5 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.9.3. minInclusive Constraining Facet

There is no built-in database support for the minInclusive constraining facet, but Listing D.62 shows how this can be enforced as a column constraint.

Listing D.62. GMonthDayMinInclusiveExample Table Creation with a Column Constraint
CREATE TABLE GMonthDayMinInclusiveExample ( 
value DATE 
CHECK (value >= ADD_MONTHS(TO_DATE('1', 'J'), 5) + 5 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

D.2.9.4. minExclusive Constraining Facet

There is no built-in database support for the minExclusive constraining facet, but Listing D.63 shows how this can be enforced as a column constraint.

Listing D.63. GMonthDayMinExclusiveExample Table Creation with a Column Constraint
CREATE TABLE GMonthDayMinExclusiveExample ( 
value DATE 
CHECK (value > ADD_MONTHS(TO_DATE('1', 'J'), 5) + 5 
    - INTERVAL '05:00' HOUR TO MINUTE) 
);

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.223.196.211