Lab 5 of 7: Data Validation Part II

In the lab for Week 5, you will be setting up the validation routines in the SEQ_PROC Oracle Procedure. The data validation needed for this procedure has already been identified in the project specifications in Week 1 (see Group Project tab for Week 1 if you need a refresher). The discussion in the group project lecture for this week gave you some examples of what the code might look like for some of the validation. Your job in this lab is to plug in the validation routines to the procedure and make sure that they work.

Once you have your validation plugged into the procedure code and have complied the procedure (refer back to Lab 2) with no compilation errors then you will need to verify that the validation works. Two files have been provided in a ZIP file in Doc Sharing (Lab5testFile.ZIP) to do your testing with for this lab. The file contains two files, one with order data and one with order line data. The first set of records in each file does not have any errors and the second contains data that your validation routines should pick up. The output from your testing will be part of the deliverable for this lab.

You will be generating a spooled output file that will verify that your validation routines for this lab are working. To generate this output file, you will need to generate a script file similar to the one generated for Lab 2. The last step in the lab will give you more information on how to proceed with this. For this lab, you will need to provide the following in a single ZIP file and submit it to the Dropbox for this week’s lab.

1. The file containing your SEQ_PROC procedure code
2. The script file containing your test script
3. The spooled output file containing the results of your test of the code to verify that the validation works

In the specifications for the project, it is stated that a specific PO number cannot exist more than once in the ORDER_ENTRY table. This would make sense because the PO_NUMBER column in the table is the primary key column. Because of this, you would want to make sure that a record containing a duplicate purchase order number would be identified before it was entered into the table. This in itself would prevent a data integrity violation within the database and the subsequent error from Oracle.

For this step of the lab, you will need to add the data validation routine that will identify a duplicate PO number and then insert the required data into the ERROR_AUDIT table in the Oracle database. You can refer to the Group Project lecture area for this week if you need additional reference on how to do this. Remember that you must set up a process to keep track that an error has occurred so that after all validation has been done you will know not to insert that record into the ORDER_ENTRY table.

In the specifications for the project, it is stated that for the ORDER_ENTRY table, a specific customer number must first exist in the CUST_ENTRY table. This would make sense because the CUST_NUMBER column in the ORDER_ENTRY table is the foreign key column relating back to the CUST_ENTRY table. Because of this, you would want to make sure that a record containing an invalid customer number would be identified before it was entered into the table. This in itself would prevent a referential integrity violation within the database and the subsequent error from Oracle.

For this step of the lab, you will need to add the data validation routine that will identify whether or not the customer number on the current record does exist in the CUST_ENTRY table. If it does not, insert the required data into the ERROR_AUDIT table in the Oracle database. You can refer to the Group Project lecture area for this week if you need additional reference on how to do this. Remember that you must set up a process to keep track that an error has occurred so that after all validation has been done, you will know not to insert that record into the ORDER_ENTRY table.

In the specifications for the project, it is stated that for the ORDER_ENTRY table, the Purchase Date must be prior to or equal to the current date. On the input file, the date is presented in the default Oracle format so that there will not be any data conversion needed. If the date field in the record for the Purchase Date does not contain the correct date (based on the current system date), then you will want to insert the required data into the ERROR_AUDIT table in the Oracle database. You can refer to the Group Project lecture area for this week if you need additional reference on how to do this piece of validation. Remember that you must set up a process to keep track that an error has occurred so that after all validation has been done, you will know not to insert that record into the ORDER_ENTRY table.

In the specifications for the project, it is stated that for the ORDER_ENTRY table, both the Partial Ship indicator and Taxable indicators must contain a value of either Y or N and it can be in either upper or lower case. The third and fourth validation routines you will need to add should address this piece of data. Both routines will be identical, only addressing different pieces of data.

If the data field in the record for these pieces of data does not contain the correct code, then you will want to insert the required data into the ERROR_AUDIT table in the Oracle database. You can refer to the Group Project lecture area for this week if you need additional reference on how to do this. Remember that you must set up a process to keep track that an error has occurred so that after all validation has been done, you will know not to insert that record into the ORDER_ENTRY table.

In the specifications for the project, it is stated that for the ORDER_ENTRY table, the Hold Date must be after to or equal to the current date. On the input file, the date is presented in the default Oracle format so there will not be any data conversion needed. If the date field in the record for the Purchase Date does not contain the correct date (based on the current system date), then you will want to insert the required data into the ERROR_AUDIT table in the Oracle database. You can refer to the Group Project lecture area for this week if you need additional reference on how to do this piece of validation. Remember that you must set up a process to keep track that an error has occurred so that after all validation has been done, you will know not to insert that record into the ORDER_ENTRY table.

This validation step will complete the validation for the order file. The next three validation steps will address data that is coming into the SEQ_PROC on the order line table. This validation will need to go in the designated area of the second loop in the procedure.

In the specifications for the project, it is stated that for the ORDER_LINE table, a specific PO number must first exist in the ORDER_ENTRY table. This would make sense because the PO_NUMBER column in the ORDER_LINE table is the foreign key column relating back to the ORDER_ENTRY table. Because of this you would want to make sure that a record containing an invalid PO number for the ORDER_LINE table would be identified before it was entered into the table. This in itself would prevent a referential integrity violation within the database and the subsequent error from Oracle.

For this step of the lab, you will need to add the data validation routine that will identify whether or not the PO number on the current record does exist in the ORDER_ENTRY table. This routine will be identical to the one done to verify if the customer number on the order entry record current exists in the CUST_ENTRY table. If the PO number does not exist in the ORDER_ENTRY table, then insert the required data into the ERROR_AUDIT table in the Oracle database. You can refer to the Group Project lecture area for this week if you need additional reference on how to do this. Remember that you must set up a process to keep track that an error has occurred so that after all validation has been done you will know not to insert that record into the ORDER_LINE table.

As stated in the project specifications, the data representing the quantity of an item ordered comes into the system as a code (alpha A to K). The database contains a table named QTY_CONVERSION containing two columns of data. The first column is a code and the second column is a number representing an associated amount. The validation routine for this last piece of data to be validated will need to first verify that the code is BETWEEN “A” AND “K.” By using the BETWEEN comparison operator, you will include the two outer bounds of the comparison. If the code is valid, then you will need to have a select statement that will select the quantity amount into a variable from the table in the database based on a match between the code on the record and the code in the table. This variable will then need to be used at the end of the program to insert the amount value into the database.

If the quantity code field in the record does not contain the correct code, then you will want to insert the required data into the ERROR_AUDIT table in the Oracle database. You can refer to the Group Project lecture area for this week if you need additional reference on how to do this. Remember that you must set up a process to keep track that an error has occurred so that after all validation has been done you will know not to insert that record into the ORDER_LINE table.

This is the last validation or data manipulation routine needed. Now it is time to test.

To test your validation, you will need first to download the Lab5OE.TXT and Lab5OL.TXT files in the archive Lab5TestFiles.zip from Doc Sharing. These files contain the records that should provide adequate testing verification for the changes you have made. You will also want to delete all the records from the ORDER_LINE, ORDER_ENTRY, and CUST_ENTRY tables that are left over from the testing of the procedures in Lab 2 or Lab 4. Be sure to delete the records from the tables in the order just given to avoid any referential integrity issues. To set up your testing and create the spool output file to turn in for grading you will need to follow these steps:

Create the test script file:

1. At the top of the script, add the session command SET ECHO ON and SET LINESIZE 132.
2. Next, add a comment with your name, as in –my name (the two hyphens will indicate a comment).
3. Set the SPOOL command to create a file named Lab5Output.txt.
4. Write a query to select everything from the ORDER_ENTRY table (you should not get any records returned when this executes).
5. Write a query to select everything from the ORDER_LINE table (you should not get any records returned when this executes).
6. Write a query to select everything from the ERROR_AUDIT table (you should not get any records returned when this executes).
7. Execute the SEQ_PROC pointing to the SAI430_###_DIR object and the lab5TestOE.TXT and Lab5TestOL.TXT files (refer back to Lab 2 if you need a refresher on the syntax for this).
8. Write a second series of queries to see everything from the ORDER_ENTRY, ORDER_LINE and ERROR_AUDIT tables again (this time there should be data).
9. Add the SPOOL OFF command to close the spool file.

Be sure to check your output file from the spool command to make sure that you have data in it. By setting ECHO ON, you will see what is happening in the SQL*Plus editor window. The data in the output file should look just like what you see in the editor window. If there is no data in the file, then make sure that you have the SPOOL OFF command as the last thing in the script file. Be sure you are including both the script file and the output file when you turn in your work for this lab.