Ilab 4 | Information Systems homework help
In the lab for Week 4, you will be setting up the validation routines in the XML_PROC Oracle Procedure. The data validation needed for this procedure has already been identified in the project specifications in Course Home (Week 0). 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 compiled the procedure (refer to Lab 2) with no compilation errors, then you will need to verify that the validation works. A file has been provided in Doc Sharing (Lab4testFile.ZIP) to do your testing with for this lab. The ZIP file contains a test file named Lab4Test.XML that contains two XML records. The first 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.
- The file containing your XML_PROC procedure code
- The script file containing your test script
- 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 customer number cannot exist more than once in the CUST_ENTRY table. This makes sense because the CUST_NUMBER column in the table is the primary key column. Because of this, you want to make sure that a record containing a duplicate customer number is identified before it is entered into the table. This in itself will 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 customer 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 CUST_ENTRY table.
In the specifications for the project, it is stated that a zip code cannot contain less than four numbers. Although it would actually be possible to set up the processing to validate the zip code against a national list of zip codes, that processing is way beyond the scope of this course, so we will just state that the zip code number must be at least four numbers in length.
To verify this, you can use the LENGTH function provided by Oracle to check the length of the data contained in the variable for the zip code for each record that is being read into the procedure. If the zip code is less than four bytes, then you will need to set the error flag and insert the appropriate data into the ERROR_AUDIT table. You can refer to the Group Project lecture area for this week if you need additional reference on how to do this.
To test your validation, you will need first to download the Lab4test.XML file from Doc Sharing. This file contains two 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 1. 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:
- At the top of the script, add the session command SET ECHO ON and SET LINESIZE 132.
- Next, add a comment with your name, as in –my name (the two hyphens will indicate a comment).
- Set the SPOOL command to create a file on the Q: drive named Lab4output.txt.
- Write a query to select everything from the CUST_ENTRY table (you should not get any records returned when this executes).
- Write a query to select everything from the ERROR_AUDIT table (you should not get any records returned when this executes).
- Execute the XML_PROC pointing to the SAI430_###_DIR object and the lab4test.XML file (refer back to Lab 2 if you need a refresher on the syntax for this).
- Write the query to see everything from both the CUST_ENTRY and ERROR_AUDIT tables again (this time there should be data).
- 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 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.