Oracle UTL_FILE package practice examples

To practice the exercise you need the schema. You can found the schema from the post http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html and before practice these exercises you must first run those sqls so that you can get tables and data to practice. After you know the answer of each step please post your PL/SQL inside comment section so that other can check and understand it and you will be more clear about it.


Scenario/Summary
This week we are going to make a change to the processing in our schema by altering one of the procedures contained in the package previously created. At this point we have dealt with procedures that required use to pass parameter values each time the procedure was executed. This type of processing greatly limits the ability to process large amounts of data efficiently. The lab this week will introduce you to a couple of the procedures contained in the UTL_FILE package that is part of Oracle's group of built-in packages. By using the GET_LINE and PUT_LINE procedures contained in UTL_FILE you will be able to process multiple records from a file and create results report on the events performed by your movie rental procedure with one call to execute the procedure.
For the lab you will need to create a script file containing the PL/SQL code that will address the lab steps below. Run the script file in your SQL*Plus session using the SET ECHO ON session command at the beginning to capture both the PL/SQL block code and output from Oracle after the block of code has executed. You will only need to recompile both the package specifications and the package body since you will be making a change to the movie_rental_sp procedure in the specifications. You will be running tests to verify that the changes to your procedure are working as they should. Spool your output and name your files with your last name plus lab 5 and give the file a text (txt) extension. For example, if you last name was Johnson then the file would be named johnson_lab5.txt. Submit both the spooled output files AND the script file for grading of the lab.
IMPORTANT: Before beginning the lab you will want to refresh the tables in your schema by running the movierental.sql script.

L A B S T E P

Step 1: Setting up your environment

Before you can effectively use the procedures in UTL_FILE to work with external files for this lab you will need to set up your environment. The first step of this process will involve creating a directory folder under the R drive which is the mapped drive for DBM405. The second part of the process will involve creating the Directory Object in Oracle to point to this folder. To accomplish this task please follow the steps listed here.

1. Create the directory folder (You can refer to the SQL*Plus Tutorial in week 1 for visuals of this process)

• 
  
  
  First, log into iLab and open up your Oracle folder. Next open Windows Explorer and expand the R drive tree.
  
  
  
  
• 
  
  
  Under the R create your own folder by selecting File/New/Folder from the main menu bar. Name your folder using a unique name such as DBM405_lastname_lab6 or some other unique name. Make sure that there are no other folders under the R drive with the same name as yours. You are the only person who has access to your folder, but the name must be unique.
  
  
  
  

2. Create the Directory Object

• 
  
  
  In Windows Explorer (the one in the Oracle folder in iLab) you want to take note of the path name for the R: drive. It will be DBM405\ followed by the session you are in. For example if you are in Spring A the path would be DBM405\SPRINGA, or if Spring B it would be DBM405\SPRINGB. This is important because this will be used to define the path for the Directory Object. Remember that the R: drive is really mapped to the Oracle server F: drive.
  
  
  
  
• 
  
  
  Start up a session in SQL*Plus and log into your user schema.
  
  
  
  
• 
  
  
  Use the following SQL command to create the Directory Object that you will use for this lab.
  
  
  
  

Create or Replace Directory DBM405_YOURNAME_DIR As 'F:\DBM405\session\YourFolderName';

IMPORTANT: Replace the word YOURNAME with your actual last name, the word session with the session name found in the path for the R: drive (see first bullet above), and the word YourFolderName with the name of the folder you created in part 1 above. For example, if your last name were SMITH and your were taking this course is Spring session A and the folder you create was named DBM405_SMITH_lab6 the statement would be:

Create or Replace Directory DBM405_SMITH_DIR As 'F:\DBM405\SPRINGA\DBM405_SMITH_LAB6';

3. Setting up the data file

• 
  
  
  Download the file "movierentaldata.txt" from Doc Sharing and place this file in the directory folder you created above in part 1.
  
  
  
  

You have now set up Oracle to work with the procedures in UTL_FILE that we will use for this lab.

Step 2: Changing the Package Specifications

We are now ready to start updating the package that we modified in Lab 5 so that it will allow us to read a file and make changes to the database. To do this we will need to make changes to the parameter list of the MOVIE_RENT_SP procedure to reflect a new process. Since we will be changing the parameter list we will need to make changes to the Package Specifications as well as to the Package Body.

For the change to the Package Specifications you will need to define a parameter for the Directory Object, one for the input file, and one for an output file that will be used for a verifications report. Each of these parameters can be defined as VARCHAR2 data types.

Step 3: The DECLARE (IS) section of the procedure

Now we can move on to the Package Body and the MOVIE_RENT_SP procedure itself. The first thing you want to do is make sure that the parameter list in the procedure matches the parameter list that you just changed in the Specifications in Step 2. The two parameter lists must match exactly or you will get errors when trying to compile the Package Body.

When you read in the file you will need several variables to handle both the record and the various pieces of data in the record. When the record is read in you will need to substring each piece of data from the record into a variable which then can be used like your were using the parameters before. To keep form having to make numerous changes in the code you might want to consider using the actual parameter names from your parameter list in the code from lab 5. For example, if the parameter name for the movie id was P_MOVIE_ID then you would create a variable named P_MOVIE_ID. Each of the variables for movie id, member id, and payment method can be cast to the data type in the MM_RENTAL table. For example:

p_movie_id mm_rental.movie_id%TYPE;

You will also need a variable to hold the record data that will be read in and a variable to hold record data that will be written out. Check the length of the data in the movierentaldata.txt file to determine the length for the input data variable. For the output data variable you can use your own judgement, but using VARCHAR2(80) would probably be more than adequate.

The last thing you will need to add is a variable to act as the handle for the input record and one for the output record. Remember that the name for these is not important, but they must be based on the FILE_TYPE data type.

Step 4: Opening the files

Now we move to the body of the code and the BEGIN section. The first thing that needs to happen is to open both the files. Remember that we are going to set up our processing just like we would any IPO logic, i.e., we need to open the files, read a record, process the data, read the next record and repeat this process until there is no more data.

To accomplish this first step of opening the files you will need to initialize the two variables you declare to handle the files. For the input file you will need to use the FOPEN program. Remember that you have to pass this program three variables; the name of the parameter for the directory object, the name of the parameter for the input file, and the mode the file is to be in. For the input file the mode needs to be 'r', and for the output file the mode needs to be 'w'.

An example of what your code might look like can be found in the lecture material for this week.

Step 5: Setting up the LOOP

Since our code in it's current format is designed for only one set of input values (remember in previous labs we were using an EXECUTE command and then passing data to the procedure) and we now want to process multiple records of data we need to make a change to the overall structure of the code. We need to put all of the processing in the body of the procedure after the files are opened into a basic LOOP using the LOOP/END LOOP commands. Also, we will need a new BEGIN section inside this LOOP that all of the existing code will go into. The basic structure of body of your code should look like the following when you complete this step.

BEGIN
open files
LOOP
BEGIN
file processing
BEGIN
existing process to increment rental_id
EXCEPTION
END
EXCEPTION
END
END LOOP
END

Step 6: Setting up the file read and process

Now we need to get out data record so that we can process the data brought into the program. To do this we need to read a record using the GET_LINE program in UTL_FILE. This involves using the UTL_FILE.GET_LINE process while passing the file handle variable and input record variable to the GET_LINE program.

Once the record is in the input variable we then need to parse out the individual pieces of data (movie id, customer id, and payment method) from the record using the SUBSTR function. Remember that the parameters for the SUBSTR function are the record variable name, the value for the first byte of data we want to pull, and the length of the data. For example, to get the movie id from our record the code might look like the following.

p_movie_id := SUBSTR(v_rental_record, 1, 2);

This would pull two bytes of data from the data record and put them in the variable p_movie_id. Now we can use the variable in our code that follows to see if that movie id exists in the mm_rental table. You will need to repeat the process above for the member id and the payment method. Do all three together between the line of code that reads the file and the first SELECT statement.

Step 7: Setting up the output process

If you have used the same names for your three data variables that you used for the original parameter names in the previous lab then you should not have to make any changes at all to any of the SELECT statements or the INSERT statement in the main body of code. Now we need to set up the processing that will create the output data that will go into our output validation record. To do this you will be using the PUT_LINE program within UTL_FILE.

There are going to be six different places you will want to write out a record. The first will be after the insert statement when a new rental record is added to the mm_rental table. To create this output line you can concatenate variables and character strings together to create the data record. For example, to create an output line that would read "Rental record 13 for member 10 has been added" after the new record has been inserted you would use code similar to the following (keep in mind that your variable names might be different).

v_report_record := 'Rental record '||v_rental_id||' for member '||p_member_id||
' has been added';
UTL_FILE.PUT_LINE(v_report_filehandle, v_report_record);

This same type of process and format will need to be repeated for each exception handler that in the previous lab used the DBMS_OUTPUT.PUT_LINE package and procedure. In these cases your output line is already formatted and set, you just need to replace the DBMS_OUTPUT.PUT_LINE with the initialization of your output record and then add the UTL_FILE.PUT_LINE code.

Step 8: Getting out of the LOOP and closing the output file

There is one final step that has to be taken care of before you can start testing your code. Since we are reading a record file within a LOOP we need to be able to EXIT out of the loop after the last record has been processed. To do this you will need to add a NO_DATA_FOUND exception to your main exception handling section. This exception handle must be added before the WHEN OTHERS exception handle (remember, that on has to be last). This exception handle will have three pieces to process.

The first will be some form of message stating that all the records have been processed and this is the end of the report, i.e., ALL RECORDS PROCESSED - END OF REPORT. The second piece will be a line that will close the output file using UTL_FILE.FCLOSE. The only parameter you will need to pass is the name of the output file variable. This must be done or your output file will not have any data in it. The third thing will be the word EXIT which will tell the program to exit out of the loop.

You are now ready to compile your package specifications (must be done first) and then the package body. Debug any errors you might have and then run your test.

Step 9: Testing your new procedure

For this lab you only need to test the MOVIE_RENT_SP procedure of the package. To do this, enter an EXECUTE command for the package.procedure and pass the name of your Directory Object, input file name, and output file name to the procedure. IMPORTANT: The Directory Object name MUST BE IN UPPER CASE! This is the way it is stored in the data dictionary in Oracle and if it is not in upper case an error will be generated. Your call to the new procedure should look similar to the following.

execute MM_RENTALS_PKG.movie_rent_sp('DBM405_SMITH_DIR','movierentaldata.txt','rentalreport.txt');

The output in your output file should look similar to the following (the wording may be different but the processes recorded should be the same).

There is no movie with id 13. Cannot proceed with rental.
There is no member with id 20. Cannot proceed with rental.
There is no payment method with id 7. Cannot proceed with rental.
Rental record 13 for member 10 has been added.
Movie id 5 is not available at this time. Cannot proceed with rental.

This concludes the Lab for Week 6.