Q What is the difference between the UTL_FILE package and the DBMS_OUTPUT package?

A The UTL_FILE package allows you to work with reading and writing files, whereas DBMS_OUTPUT is concerned with displaying data from variables to the screen for debugging purposes.

Q What happens if I try to store more than 255 characters in a line of the buffer?

A An exception is raised. Refer to Table 18.5 for definitions of the errors.

Q How do you display output with the DBMS_OUTPUT package?

A If using SQL*Plus, simply use SET SERVEROUTPUT ON. If using PL/SQL, then use DBMS_OUTPUT.ENABLE. It is a good idea in any login script to automatically enable the output package.

Workshop

In the following workshop, you will first be quizzed on your understanding of how file input and output works. Then you will practice file input and output using the UTL_FILE and DBMS_OUTPUT packages in the exercises. You can find the answers in Appendix A, "Answers."

Quiz

1. What is the difference between PUT and PUT_LINE with the UTL_FILE package or even the DBMS_OUTPUT package?
2. What is the maximum number of bytes that can be read into a buffer when using UTL_FILE?
3. What is the maximum number of bytes that can be output to a file when using UTL_FILE?
4. When using FCLOSE_ALL, what boolean value will be returned by testing to see if the file is open using IS_OPEN?
5. What suppresses output from PUT or PUT_LINE when using the DBMS_OUTPUT package?
6. What is the maximum number of characters allowed in a buffer line when using DBMS_OUTPUT?
7. What is the maximum number of characters that can possibly be allocated to the buffer when using DBMS_OUTPUT?

Exercises

1. From the code in Listing 18.6, output the data in a fixed block format. The output should be SSNO with a length of 11 bytes formatted as ###-##-####, FirstName at 20 bytes, and LastName at 30 bytes. Remember, VARCHAR2 data does not pad spaces to the right. You could either use a character function to fill the data with spaces or assign the variables to type CHAR before outputting the data. Name the output file PRESFIX.TXT.
2. From Exercise 1, add DBMS_OUTPUT.PUT_LINE to display the lines to the screen as they are output to the file.