STEP 1: Logging In and Creating New Tables
To begin this lab, first log into the iLab environment on Citrix and access the Oracle SQL*Plus application that can be found in your SAI430 applications folder. Remember to select the ‘Full access’ button from the security settings window when it appears, because you will need access to both the folder and files that you will have on your Q: mapped drive and the V: drive on your client computer.
Once the SQL*Plus editor opens, log into your database instance using the SAI430_### userid and password you were assigned (remember that you must use the word ORACLE as the host string as well). Next, you need to run the ORDER_TRANS.SQL script that you downloaded before starting this lab. If you have moved this file to the Q:SAI430_### folder on Citrix, then you can enter @Q:SAI430_###ORDER_TRANS.SQL at the command prompt to execute the file; if the file is still on the C: of your computer, then enter @V:ORDER_TRANS.SQL and press the ENTER key (remember that your C: drive is mapped to the V: network drive on the server). Your script should run and create the needed tables.
IMPORTANT: Next you will need to create the three new tables that will be needed for the project and this lab. These tables, the ERROR_AUDIT, the TRANS_VERIFY, and the TRANS_OUTSTANDING are referenced and used in the procedures you will be compiling and must exist before you can execute the procedures. The specification for these tables can be found under the Course Project tab under Course Home.
Now you are ready to start working with your procedures.
STEP 2: Loading the Procedure into Oracle
Download the StoredProc.ZIP file that is in Doc Sharing and extract all of the files into your Q:SAI430_### drive in Citrix. To do this you will probably first need to download the Zip file onto your local hard drive and then access the file via the V: drive in Citrix to extract the files into the Q: drive.
Now you will need to run each of the three procedures in your Oracle session. Remember that a PL/SQL procedure is actually stored in the database. Once the procedure is compiled in the database, you will not be able to see the code again except to look at your script file that contains the code. To compile the procedure into your instance, you will simply need to run the SQL file from the command line within your session. You should be able to accomplish this with the three following statements. Before you run the scripts, be sure to issue the session command SET ECHO ON from the command line in SQL*Plus so that you can see the code and line numbers when it runs. NOTE: The last procedure has an error in it that is intentional. In the next section we will see how to look at the error and how to fix it. The first two procedures should compile and generate a return message of “Procedure created.” The third will generate a message “Warning: Procedure created with compilation errors.”
@Q:SAI430_###TRANS_PROC (NOTE: This one will compile with compilation errors, which will be discussed in the next step)
Since these files have an .SQL extension, it is not mandatory to include the extension with the file name when run in Oracle.
STEP 3: Debugging the Procedure
The errors generated from PL/SQL are not very different than those generated by other similar compilers. In general, the error messages are not very explanatory concerning the actual problem, so it takes some investigation sometimes to really understand what has happened. The following example will help you understand the parts of the error message and how they can be used.
|PL/SQL Error Messages Explained|
The following is an example of what a PL/SQL error message might look like coming back from Oracle.
LINE/COL ERROR_x000D_ -------- -----------------------------------------------------------------_x000D_ 96/7 PLS-00201: identifier 'V_PO_NUMBE2' must be declared_x000D_ 96/7 PL/SQL: Statement ignored
The first two numbers you see in the error message line identify a line in the code associated with the error. In some cases, the error might actually be on the line above the one noted in the error message, but in this case the variable in question was actually in line 96.
The second number denotes the column position going across the page that the questionable code starts in. This is not often very useful, and not nearly as useful as the column number.
The error code can be quite helpful. It is not always the case that the error message associated with the code is as descriptive as the example here. In cases like that, you can usually take the error code (in this case the PLS-00201) and Google it to get a more exact explanation of what the problem is and how to fix it.
Finally we have the actual error message “identifier ‘V_PO_NUMBE2’ must be declared.” In our example here, the error message is fairly exact and gives us a clue that the variable is not declared and needs to be. There are cases, as you will see in the next example, when the error message might not be so clear. Again, in cases in which you might not be sure, then doing a Google search on the error code might help.
When you ran the script to create the TRANS_PROC procedure, you received a return message stating “Warning: Procedure created with compilation errors,” which is Oracle’s way of telling you that the procedure has been created and logged into the data dictionary, but you cannot use it because it has one or more errors in the code. This is helpful to an extent, but it would obviously help to be able to see the errors and know where they were. This can be accomplished by entering the command SHOW ERROR after the procedure code has run. All of the errors in the code or related to the procedure will be listed starting with the upper most error in the code and progressing to the last one. In the TRANS_PROC procedure, there is a single error and it deals with one that happens very often when first writing PL/SQL code. The diagram and explanation below will hopefully give you some insight into how the debugging process is handled, and it will help you get rid of the error in your code so that you can recompile it.
|An example to consider|
In the following example, the error message is not as straightforward as the previous one was. In this case the actual error is in line 99 and the problem is that there is a semicolon missing at the end of the assignment statement. Notice that the line reference points to line 102 where the next actual statement beings. In this case the actual error is on the previous line of code.
98 V_ITEM_NUMBER := SUBSTR(V_INPUT, 12,5);_x000D_ 99 V_TRANS_DATE2 := TO_DATE((SUBSTR(V_INPUT, 17, 8)), 'DDMMYYYY') <--- Error is actually here_x000D_ 100 _x000D_ 101 --insert into trans_verifty table_x000D_ 102 INSERT INTO TRANS_OUTSTANDING <--- Error message is pointing to this line_x000D_ 103 VALUES(V_PO_NUMBER2, V_CUST_NUMBER2, V_ITEM_NUMBER, V_TRANS_DATE2);_x000D_ 104 END LOOP;_x000D_ 105 END;_x000D_ 106 /_x000D_ _x000D_ Warning: Procedure created with compilation errors._x000D_ _x000D_ SQL> show error_x000D_ Errors for PROCEDURE TRANS_PROC:_x000D_ _x000D_ LINE/COL ERROR_x000D_ -------- -----------------------------------------------------------------_x000D_ 102/7 PLS-00103: Encountered the symbol "INSERT" when expecting one of_x000D_ the following:_x000D_ . ( * % & = - + ; < / > at in is mod not rem_x000D_ <an exponent (**)> <> or != or ~= >= <= <> and or like_x000D_ between ||_x000D_ The symbol ";" was substituted for "INSERT" to continue.
STEP 4: Testing the Procedures
Along with the procedure in the stored procedures in the StoredProc.ZIP were three data files. Each of these data files is designed to test one of the three procedures. To test your procedures, you will want to create a test script that you can run in your Oracle session. This test script will generate an output file that you will then be able to turn in as the gradable item for this lab. The following is an example of what will be needed in each test script (you will want to create a separate script for each of the three procedures).
SET ECHO ON_x000D_ SPOOL Q:SAI430_###LAB2_TEST1.TXT_x000D_ _x000D_ --YOUR NAME_x000D_ --COURSE NAME AND NUMBER_x000D_ _x000D_ SELECT * FROM CUST_ENTRY;_x000D_ _x000D_ EXECUTE XML_PROC('SAI430_###_DIR', 'LAB2.XML');_x000D_ _x000D_ SELECT * FROM CUST_ENTRY;_x000D_ _x000D_ SPOOL OFF_x000D_
Notice that the path used for the spool file is pointing to the mapped Q: drive and your directory, while the path to the input file for the procedure is pointing to the Directory Object and not the mapped network drive (remember that the ### must be replaced with your individual number for your directory). In the one case, Oracle can see the mapped drive when it wants to create a spool file or run a script file in the normal manner. However, when using the UTL_FILE package as we are doing in these procedures, Oracle must be able to find the Directory Object that points to the actual file path that has your files for reading or that will be used to write files out to. When executing your procedures, you will always need to point to the Directory Object name within the parameter listing for the procedure call. To test the other two procedures, you will need to use the following information.
Important: If you run your test scripts more than once, you will need to delete the data inserted into the tables by the procedures each time before running the script. Failing to do this will cause the procedure to error with a ORA-00001: unique constraint violated error.
- Query the ORDER_ENTRY and ORDER_LINE tables before executing the procedure.
- Use EXECUTE SEQ_PROC(‘SAI430_###_DIR’, ‘LAB2OE.TXT’, ‘LAB2OL.TXT’);
- Query the ORDER_ENTRY and ORDER_LINE tables again (this time there should be data).
Testing the TRANS_PROC:
- Query the TRANS_VERIFY and TRANS_OUTSTANDING tables before executing the procedure.
- Use EXECUTE TRANS_PROC(‘SAI430_###_DIR’, ‘LAB2V.TXT’, ‘LAB2BO.TXT’);
- Query the TRANS_VERIFY and TRANS_OUTSTANDING tables again (this time there should be data).
When finished you should have three output files, one for each test created because you used the SPOOL command. Make sure that these files have data in them and that the data is correct (is it the same data that was on the input file?). These will be the files that you submit for grading.