23.4. The list Element

The list element declares an ordered series of items of any atomic type. An instance of a list contains all the items within a single element. There are four distinct ways to implement the list element:

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

• Create a table to hold the values associated with a primary table.

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

• Create a single column of type SYS.ANYDATASET to hold the list of values.

Each of these approaches has merits and drawbacks with respect to ease of use, functionality, and ease of implementing the constraining facets. With respect to ease of use and functionality, the first approach 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 approach requires more work from both the application developer and database designer, but allows individual values that are part of the list to be easily inserted, updated, deleted, and selected. The third and fourth approaches require significant effort from both the application developer and the database designer and provide little benefit beyond the first approach. They are not implemented in this section. With respect to the ease of implementing constraining facets, refer to Sections 23.4.3, 23.4.4, 23.4.5, 23.4.6, and 23.4.7.

Listing 23.5 provides an XML schema example to demonstrate the single column and separate table approaches.

Listing 23.5. An XML Schema Document Fragment of a Simple Type Supporting a List of Values (catalog.xsd)

<xsd:simpleType name="assemblyPartNumberListType" 
                id="catalog.assemblyPartNumber.list.sType"> 
    <xsd:annotation> 
        <xsd:documentation xml:lang="en"> 
          The "assemblyPartNumberListType" describes the 
          value for an element that contains a set of part 
          numbers. Given that a part number might look 
          like any of the following: 
              ASM1 
              ASM32897 
              ASM2233344 
          A list of these part numbers might look like: 
              ASM1 ASM32897 ASM2233344 
        </xsd:documentation> 
    </xsd:annotation> 
    <xsd:list id="transaction.assemblyPartNumberList" 
              itemType="assemblyPartNumberType"> 
    </xsd:list> 
</xsd:simpleType> 

23.4.1. Single Column

In this scenario, the assemblyPartNumberListType simple type is comprised of a series of assemblyPartNumberType simple types where the items are represented as a single string. Listing 23.6 shows this as a column constraint. Note that the pattern constraining facet of ‘ASMd{1,8}’ is not enforced because it would require additional user-defined PL/SQL procedures to parse the string into individual asemblyPartNumberTypes and validate each one separately. The example uses a database column datatype of VARCHAR2 with a length of 300. To determine the best database column datatype, refer to Section 22.4.1.

Listing 23.6. AssPartNumberListTypeSC Single Column Table Creation with a Column Constraint

CREATE TABLE AssPartNumberListTypeSC ( 
assPartNumberListTypeSCID VARCHAR2(300) NOT NULL 
  CHECK (INSTR(assPartNumberListTypeSCID, 
               UNISTR('00D')) = 0 AND 
         INSTR(assPartNumberListTypeSCID, 
               UNISTR('00A')) = 0 AND 
         INSTR(assPartNumberListTypeSCID, 
               UNISTR('009')) = 0 AND 
         LENGTH(assPartNumberListTypeSCID) = 
           LENGTH(TRIM(assPartNumberListTypeSCID)) AND 
         INSTR(assPartNumberListTypeSCID, ' ') = 0) 
); 

Listing 23.7 shows how an AssPartNumberListTypeSC element, which specifies the list of ‘ASM1 ASM32897 ASM2233344’, would be inserted into the database.

Listing 23.7. AssPartNumberListTypeSC Example Values Insertion

INSERT INTO AssPartNumberListTypeSC 
VALUES 
('ASM1 ASM32897 ASM2233344'), 

23.4.2. Separate Table

In this scenario, the assemblyPartNumberListType simple type is essentially a multivalued assemblyPartNumberType simple type where each value is stored separately in a table row. In Listing 23.8, a sequence, primary keys, and a foreign key are created. A sequence provides the unique values for the primary key of the AssPartNumberListTypeMT table. The sole column of the AssPartNumberListTypeMT table, assPartNumberListTypeMTID, is a foreign key to the AssPartNumberTypeMT table that also has listOrder as the second part of the primary key. The AssPartNumberTypeMT table holds the assemblyPartNumberType value.

Listing 23.8. AssemblyPartNumberListType 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_AssPartNumberListType INCREMENT BY 1 START 
    WITH 1 MAXVALUE 999999999 MINVALUE 1 NOCYCLE ORDER;  
CREATE TABLE AssPartNumberListTypeMT ( 
assPartNumberListTypeMTID NUMBER(9) NOT NULL, 
PRIMARY KEY (AssPartNumberListTypeMTID) 
); 

CREATE TABLE AssPartNumberTypeMT ( 
assPartNumberTypeMTID NUMBER(9) NOT NULL, 
listOrder             NUMBER(3) NOT NULL, 
partNumberType        VARCHAR2(11) NOT NULL 
  CHECK (INSTR(partNumberType, UNISTR('00D')) = 0 AND 
         INSTR(partNumberType, UNISTR('00A')) = 0 AND 
         INSTR(partNumberType, UNISTR('009')) = 0 AND 
         LENGTH(partNumberType) = 
           LENGTH(TRIM(partNumberType)) AND 
         INSTR(partNumberType, ' ') = 0 AND 
         LENGTH(partNumberType) BETWEEN 4 AND 11 AND 
         SUBSTR(partNumberType, 1, 3) = 'ASM' AND 
         -- If the value is not a number, then an 
         -- error will be thrown. IS NOT NULL makes 
         -- this valid SQL, but is meaningless. 
         CAST(SUBSTR(partNumberType, 4, 8) AS NUMBER) 
           IS NOT NULL), 
PRIMARY KEY (assPartNumberTypeMTID, listOrder), 
FOREIGN KEY (assPartNumberTypeMTID) 
REFERENCES AssPartNumberListTypeMT 
); 

Listing 23.9 shows how an AssPartNumberListTypeMT element, which specifies the list of ‘ASM1 ASM32897 ASM2233344’, would be inserted into the database.

Listing 23.9. AssPartNumberListTypeMT Example Values Insertion

INSERT INTO AssPartNumberListTypeMT 
VALUES 
(Seq_AssPartNumberListType.NEXTVAL); 

INSERT INTO AssPartNumberTypeMT 
(assPartNumberTypeMTID, listOrder, partNumberType) 
VALUES 
(Seq_AssPartNumberListType.CURRVAL, 1, 'ASM1'), 

INSERT INTO AssPartNumberTypeMT 
(assPartNumberTypeMTID, listOrder, partNumberType) 
VALUES 
(Seq_AssPartNumberListType.CURRVAL, 2, 'ASM32897'),   

INSERT INTO AssPartNumberTypeMT 
(assPartNumberTypeMTID, listOrder, partNumberType) 
VALUES 
(Seq_AssPartNumberListType.CURRVAL, 3, 'ASM2233344'), 

23.4.3. length Constraining Facet

The length constraining facet determines the number of items in the list and not the length of each item in the list. Implementing this facet when either a single column or separate table approach is chosen requires some coding and design trade-offs.

23.4.3.1. Single Column

With the single column approach, the string would need to be examined to determine the number of tokens. Listing 23.10 creates an AssPartNumberListTypeSC table using a check constraint that counts the number of space characters, assuming a length of three has been specified. Note that, to simplify the code, the pattern constraining facet on assemblyPartNumberType is not verified.

Listing 23.10. Mapping an AssemblyPartNumberListTypeSC in a Single Column Implementing the length Constraining Facet to a Database Representation

CREATE TABLE AssPartNumberListTypeSC ( 
assPartNumberListTypeSCID VARCHAR2(300) NOT NULL 
  CHECK (INSTR(assPartNumberListTypeSCID, 
               UNISTR('00D')) = 0 AND 
         INSTR(assPartNumberListTypeSCID, 
               UNISTR('00A')) = 0 AND 
         INSTR(assPartNumberListTypeSCID, 
               UNISTR('009')) = 0 AND 
          LENGTH(assPartNumberListTypeSCID) = 
            LENGTH(TRIM(assPartNumberListTypeSCID)) AND 
          INSTR(assPartNumberListTypeSCID, ' ') = 0 AND 
          --Check to see if enough remaining tokens. 
          --If there are three spaces, then there are too 
          --many tokens. It there are not two, then there 
          --are too few. 
          INSTR(assPartNumberListTypeSCID, ' ', 1, 3) = 0 AND 
          INSTR(assPartNumberListTypeSCID, ' ', 1, 2) <> 0) 
); 

23.4.3.2. Separate Table

With the separate table approach, the number of items cannot be counted until all of the items have been inserted. What is needed is a before-commit trigger, but such a trigger does not exist. In this case, there are three options:

• Create a column in the table that tells the database this is the last entry, and have a trigger run to validate the length at that point.

• Create a validation PL/SQL procedure that the application developer can explicitly call.

• Have the system call the validation procedure periodically and write any issues to an error log.

The first two options, which rely on the application to inform the database that the validation can occur, are prone to error. The last option handles this automatically but allows for incorrect data in the database and then creates the need for manual inspection and correction. However, the first and second options do not preclude the addition of the third option. All three options are explored further in the following sections. Note that, to simplify the code, the pattern constraining facet on assemblyPartNumberType is not verified in any of these options.

23.4.3.2.1. Create a Last Entry Column

Creating a last entry column involves adding an extra database column solely for use by the validation trigger. This is unfortunate, but functional. It will throw an error so that the last entry will not be inserted, but because previous rows will have already been inserted, the application code will need to perform a rollback. Listings 23.11, 23.12, and 23.13 create the assemblyPartNumberType table and an insert trigger, and test the trigger with some sample data.

Listing 23.11. AssPartNumberTypeMT Example Table Creation with a length Constraining Facet and Last Entry Column

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

CREATE TABLE AssPartNumberTypeMT ( 
assPartNumberTypeMTID NUMBER(9) NOT NULL, 
listOrder             NUMBER(3) NOT NULL, 
partNumberType        VARCHAR2(11) NOT NULL 
  CHECK (INSTR(partNumberType, UNISTR('00D')) = 0 AND 
         INSTR(partNumberType, UNISTR('00A')) = 0 AND 
         INSTR(partNumberType, UNISTR('009')) = 0 AND 
         LENGTH(partNumberType) = 
           LENGTH(TRIM(partNumberType)) AND 
         INSTR(partNumberType, ' ') = 0 AND 
         LENGTH(partNumberType) BETWEEN 4 AND 11 AND 
         SUBSTR(partNumberType, 1, 3) = 'ASM' AND 
         -- If the value is not a number, then an 
         -- error will be thrown. IS NOT NULL makes 
         -- this valid SQL, but is meaningless.   
         CAST(SUBSTR(partNumberType, 4, 8) AS NUMBER) 
           IS NOT NULL), 
isLastPartNumberOfOrder NUMBER(1) NOT NULL, 
PRIMARY KEY (assPartNumberTypeMTID, listOrder), 
FOREIGN KEY (assPartNumberTypeMTID ) 
REFERENCES AssPartNumberListTypeMT 
); 

Listing 23.12 shows a SQL statement that creates a before-insert trigger to ensure that the length of the list is three.

Listing 23.12. Before-Insert Trigger on AssPartNumberTypeMT Implementing the length Constraining Facet

CREATE OR REPLACE TRIGGER InsertAssPartNumberTypeMT 
BEFORE INSERT 
ON AssPartNumberTypeMT 
FOR EACH ROW 
WHEN (new.isLastPartNumberOfOrder = 1) 
DECLARE 
I PLS_INTEGER; 
BEGIN 
  I := 0; 
  SELECT COUNT(1) 
  INTO  I 
  FROM  AssPartNumberTypeMT 
  WHERE assPartNumberTypeMTID = :new.assPartNumberTypeMTID; 

  -- Check to see if enough rows. Since this a before trigger, 
  -- the last row has not yet been inserted. 
  IF I <> 2 THEN 
    RAISE_APPLICATION_ERROR(-20000, 'Not length of 3.'), 
  END IF; 
END; 

Listing 23.13 shows a few SQL statements that insert some sample valid data to demonstrate use of the tables.

Listing 23.13. AssPartNumberListTypeMT Example Values Insertion with a Last Entry Column

INSERT INTO AssPartNumberListTypeMT 
VALUES 
(Seq_AssPartNumberListType.NEXTVAL);  

INSERT INTO AssPartNumberTypeMT 
(assPartNumberTypeMTID, listOrder, partNumberType, isLastPartNumberOfOrder) 
VALUES 
(Seq_AssPartNumberListType.CURRVAL, 1, 'ASM1', 0); 

INSERT INTO AssPartNumberTypeMT 
(assPartNumberTypeMTID, listOrder, partNumberType, isLastPartNumberOfOrder) 
VALUES 
(Seq_AssPartNumberListType.CURRVAL, 2, 'ASM32897', 0); 

INSERT INTO AssPartNumberTypeMT 
(assPartNumberTypeMTID, listOrder, partNumberType, isLastPartNumberOfOrder) 
VALUES 
(Seq_AssPartNumberListType.CURRVAL, 3, 'ASM2233344', 1); 

23.4.3.2.2. Create a Validation Procedure

Creating a validation procedure involves developing a PL/SQL procedure that the application needs to call manually before a commit to the database. The application will need to perform a rollback in case the validation fails. Listings 23.8 and 23.9 are still applicable; Listing 23.14 shows the validation procedure for assemblyPartNumberTypes.

Listing 23.14. Validation Procedure Implementing the length Constraining Facet

CREATE OR REPLACE FUNCTION ValidateAssPartNumberTypeMTLen 
  (assPartNumberID IN 
     AssPartNumberTypeMT.assPartNumberTypeMTID%TYPE) 
RETURN BOOLEAN IS 
  I PLS_INTEGER; 
BEGIN 
  I := 0; 
  SELECT COUNT(1) 
  INTO  I 
  FROM  AssPartNumberTypeMT 
  WHERE assPartNumberTypeMTID = assPartNumberID; 

  IF I <> 3 THEN 
    RETURN FALSE; 
  END IF; 

  RETURN TRUE; 
END; 

23.4.3.2.3. Create a System-Called Validation Procedure

Creating a system-called validation procedure involves developing a PL/SQL procedure that is called automatically by the system at specified intervals. The results need to be reviewed by the database administrator and application administrator, and then manually corrected. Listings 23.8 and 23.9 are still applicable; Listing 23.15 shows the validation procedure and Listing 23.16 creates a cron job to run it at midnight to validate assemblyPartNumberTypes.

Listing 23.15. Validation Procedure Implementing the length Constraining Facet for the AssPartNumberTypeMT Table

CREATE OR REPLACE PROCEDURE ValidateAllAssPartNumberLen IS 
  CURSOR AllInvalidIDS IS 
    SELECT assPartNumberTypeMTID 
    FROM   AssPartNumberTypeMT 
    HAVING COUNT(assPartNumberTypeMTID) <> 3 
    GROUP BY assPartNumberTypeMTID; 
BEGIN 
  FOR ID IN AllInvalidIDS LOOP 
    --This line needs to be replaced with a logfile 
    --using the UTL_FILE package, with a queue entry, 
    --using DBMS_AQ package, etc. 
    DBMS_OUTPUT.PUT_LINE(ID.assPartNumberTypeMTID); 
  END LOOP; 
END; 

To run this procedure manually, leaving in the call to DBMS_OUTPUT.PUT_LINE, run the following code snippet:

SET serveroutput ON; 
BEGIN 
  DBMS_OUTPUT.ENABLE; 
  ValidateAllAssPartNumberLen; 
END; 

Listing 23.16 is an anonymous PL/SQL block that creates a cron job using the DBMS_JOB package in Oracle. This one first runs at midnight and then every midnight thereafter.

Listing 23.16. Creating a Cron Job to Run the Validation Procedure Nightly

DECLARE 
  jobNo PLS_NUMBER; 
BEGIN 
  DBMS_JOB.SUBMIT(:jobNo,' ValidateAllAssPartNumberLen;' 
                  TRUNC(SYSDATE), 'TRUNC(SYSDATE) + 1'), 
  COMMIT; 
END; 

23.4.4. minLength Constraining Facet

The minLength constraining facet determines the minimum number of items in the list and not the minimum length of each item in the list. Implementing this facet when either a single column or separate table approach is chosen requires some coding and design trade-offs. The same discussion concerning the length constraining facet in 23.4.3 applies to the minLength constraining facet.

23.4.5. maxLength Constraining Facet

The maxLength constraining facet determines the maximum number of items in the list and not the maximum length of each item in the list. Implementing these when either a single column or separate table approach is chosen requires some coding and design trade-offs.

23.4.5.1. Single Column

With the single column approach, the string would need to be examined to determine the number of tokens. Listing 23.17 creates an AssPartNumberListTypeSCML table using a check constraint that counts the number of space characters, assuming a maximum length of three has been specified. Note that, to simplify the code, the pattern constraining facet on assemblyPartNumberType is not verified.

Listing 23.17. A Mapping of an AssemblyPartNumberListTypeSCML in a Single Column Implementing the maxLength Constraining Facet to a Database Representation

CREATE TABLE AssPartNumberListTypeSCML ( 
assPartNumberListTypeSCMLID VARCHAR2(300) NOT NULL 
  CHECK (INSTR(assPartNumberListTypeSCMLID, 
               UNISTR('00D')) = 0 AND 
         INSTR(assPartNumberListTypeSCMLID, 
               UNISTR('00A')) = 0 AND 
         INSTR(assPartNumberListTypeSCMLID, 
               UNISTR('009')) = 0 AND 
         LENGTH(assPartNumberListTypeSCMLID) = 
           LENGTH(TRIM(assPartNumberListTypeSCMLID)) AND 
         INSTR(assPartNumberListTypeSCMLID, ' ') = 0 AND 
         --Check to see if enough remaining tokens. 
         --If there are three spaces, then there are too 
         --many tokens. It there are not two, then there 
         --are too few. 
         INSTR(assPartNumberListTypeSCMLID, ' ', 1, 3) = 0) 
); 

23.4.5.2. Separate Table

With the separate table approach, unlike the length and minLength constraining facets, counting the number of maximum items can be validated as each element is inserted. Listings 23.18 and 23.19 are the SQL statements that create the assemblyPartNumberTypeMTML table and an insert trigger to implement the maxLength constraining facet.

Listing 23.18. AssemblyPartNumberListTypeMTML Example Tables Creation with a maxLength Constraining Facet

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

CREATE TABLE AssPartNumberTypeMTML ( 
assPartNumberTypeMTMLID NUMBER(9) NOT NULL, 
listOrder               NUMBER(3) NOT NULL, 
partNumberType          VARCHAR2(11) NOT NULL 
 CHECK (INSTR(partNumberType, UNISTR('00D')) = 0 AND 
        INSTR(partNumberType, UNISTR('00A')) = 0 AND 
        INSTR(partNumberType, UNISTR('009')) = 0 AND 
        LENGTH(partNumberType) = 
          LENGTH(TRIM(partNumberType)) AND 
        INSTR(partNumberType, ' ') = 0 AND 
        LENGTH(partNumberType) BETWEEN 4 AND 11 AND 
        SUBSTR(partNumberType, 1, 3) = 'ASM' AND 
        -- If the value is not a number, then an 
        -- error will be thrown. IS NOT NULL makes 
        -- this valid SQL, but is meaningless. 
        CAST(SUBSTR(partNumberType, 4, 8) AS NUMBER) 
          IS NOT NULL), 
PRIMARY KEY (assPartNumberTypeMTMLID, listOrder), 
FOREIGN KEY (assPartNumberTypeMTMLID) 
REFERENCES AssPartNumberListTypeMT 
); 

Listing 23.19 shows a SQL statement that creates a before-insert trigger to ensure that the length of the maximum number of items is three.

Listing 23.19. Before-Insert Trigger on AssPartNumberTypeMTML Implementing the maxLength Constraining Facet

CREATE OR REPLACE TRIGGER InsertAssPartNumberTypeMTML 
BEFORE INSERT 
ON AssPartNumberTypeMTML 
FOR EACH ROW 
DECLARE 
I PLS_INTEGER;  
BEGIN 
  I := 0; 
  SELECT COUNT(1) 
  INTO  I 
  FROM  AssPartNumberTypeMTML 
  WHERE assPartNumberTypeMTMLID = :new.assPartNumberTypeMTMLID; 
  -- Check to see if enough rows. Since this a before trigger, 
  -- the last row has not yet been inserted. 
  IF I > 2 THEN 
    RAISE_APPLICATION_ERROR(-20000, 'Not length of 3.'), 
  END IF; 
END; 

23.4.6. pattern Constraining Facet

The pattern constraining facet determines the pattern for the entire list and not the pattern of each item in the list. Implementing this facet when a single column approach has been chosen is simply a string pattern match. Refer to Section 22.1.1 for design decisions and implementation details and to Listing 23.4, which details the implementation of the constraint in the assemblyPartNumberType example.

Implementing a pattern constraining facet when a separate table approach has been chosen is more difficult. This issue is similar to the length constraining facet in that all the values need to be inserted before the pattern matching can be checked. Instead of summing up the number of values, they need to be concatenated with one space between adjacent values. There is one additional wrinkle: Updating a value can invalidate the pattern. Refer to Section 22.1.1 for design decisions and implementation details and to Listing 23.4, which details the implementation of the constraint in the assemblyPartNumberType example.

23.4.7. enumeration Constraining Facet

The enumeration constraining facet determines the value for the entire list and not the enumerations of each item in the list. Refer to Section 22.2.2 for design decisions and implementation details.

Implementing an enumeration constraining facet when a separate table approach has been chosen is more difficult. This is similar to the length constraining facet in that all the values need to be inserted before the enumeration can be checked. Instead of summing up the number of values, they need to be concatenated with a space between the values. There is one additional wrinkle: Updating a value can invalidate the enumeration. Refer to the Section 22.2.2 for design decisions and implementation details.