Enabling with PL/SQL
The next examples will demonstrate the same process completely within PL/SQL. Go ahead and execute the code in Listing 18.8.

INPUTListing 18.8. Using DBMS_OUTPUT with PUT, NEW_LINE, and PUT_LINE with the PL/SQL ENABLE procedure.

 1: DECLARE
 2: --Counter for the For Loop
 3:      v_Counter NUMBER;
 4: BEGIN
 5:      DBMS_OUTPUT.ENABLE;
 6:      FOR v_Counter IN 1..5 LOOP
 7: -- This will cause two of each number to appear on same line
 8: -- as PUT_LINE will flush PUT with it
 9:           DBMS_OUTPUT.PUT(v_Counter);
10:           DBMS_OUTPUT.PUT_LINE(v_Counter);
11:      END LOOP;
12: --Demonstrate PUT with NEW_LINE
13:      DBMS_OUTPUT.PUT_LINE(`We will now test with a newline character');
14:      FOR v_Counter IN 1..5 LOOP
15:           DBMS_OUTPUT.PUT(v_Counter);
16:           DBMS_OUTPUT.NEW_LINE;
17:      END LOOP;
18: END;

ANALYSIS Your output will still be identical to the output generated from Listing 18.7. By
now, you can see how great the DBMS_OUTPUT package can be as a debugging tool. You can finally see alternative ways to gain access to the data by using GET_LINE or GET_LINES.

GET_LINE and the DBMS_OUTPUT Package

The GET_LINE procedure allows you to retrieve only one line from the buffer up to a maximum of 255 characters. A line is defined as all characters up to the newline character. The format for GET_LINE is

PROCEDURE GET_LINE(single_line OUT VARCHAR2, status OUT INTEGER);

If GET_LINE is successful reading a single line, a value of 1 is returned for a status; otherwise, a value of 0 is returned if false. You can test GET_LINE by executing the code in Listing 18.9.

INPUTListing 18.9. An example using GET_LINE.

 1: DECLARE
 2:      v_Counter NUMBER;
 3:      v_HoldBuffer VARCHAR2(255);
 4:      v_HoldStatus INTEGER;

 5: BEGIN
 6:      DBMS_OUTPUT.ENABLE;
 7:      FOR v_Counter IN 1..5 LOOP
 8:           DBMS_OUTPUT.PUT(v_Counter);
 9:           DBMS_OUTPUT.PUT_LINE(v_Counter);
10:      END LOOP;
11:      DBMS_OUTPUT.PUT_LINE(`We will now test with a newline character');
12:      FOR v_Counter IN 1..5 LOOP
13:           DBMS_OUTPUT.PUT(v_Counter);
14:           DBMS_OUTPUT.NEW_LINE;
15:      END LOOP;
16:      DBMS_OUTPUT.GET_LINE(v_HoldBuffer,v_HoldStatus);
17:      DBMS_OUTPUT.PUT_LINE(v_HoldBuffer);
18: END;

Your output will appear as only

OUTPUT

11

ANALYSIS Why did only the first line appear instead of all the output lines from Listing 18.7 or Listing 18.8? Earlier, I stated that all output would be sent to the screen as long as GET_LINE or GET_LINES had not been used. Now because you are using GET_LINE in line 16, all output is suppressed from the screen. GET_LINE went to the buffer and extracted the first line with a value of 11, which was the result of the first FOR loop. You could only output the line with a subsequent PUT_LINE statement in line 17 displaying the data in the variable v_HoldBuffer, which was returned from the GET_LINE call.

Using GET_LINES

Although GET_LINE retrieves only one line, you can use GET_LINES to retrieve more than one line. You can read more than one line because GET_LINES reads the buffer into a PL/SQL table, which will be defined in the DECLARE section as a type CHARARR.

The Syntax for the GET_LINES Procedure
The syntax for GET_LINES is

PROCEDURE GET_LINES(line(s) OUT CHARARR,
     no_lines_retrieve IN OUT  INTEGER);

The Syntax for the CHARARR Datatype
The format for the datatype of CHARARR is

TYPE CHARARR IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;

When the call is made, the number of rows to retrieve is stored in the array, starting with row zero. If you ask for more lines than there actually are, only the number of available lines are read into the table. Again, GET_LINES, like GET_LINE, defines a line as all characters up to the newline character. You can now practice GET_LINES by executing the code in Listing 18.10.

INPUTListing 18.10. Using GET_LINES in place of GET_LINE.

 1: DECLARE
 2:      v_Counter NUMBER;
 3:      v_HoldBuffer DBMS_OUTPUT.CHARARR;
 4:      v_HoldLines INTEGER := 11;
 5: BEGIN
 6:      DBMS_OUTPUT.ENABLE;
 7:      FOR v_Counter IN 1..5 LOOP
 8:           DBMS_OUTPUT.PUT(v_Counter);
 9:           DBMS_OUTPUT.PUT_LINE(v_Counter);
10:      END LOOP;
11:      DBMS_OUTPUT.PUT_LINE(`We will now test with a newline character');
12:      FOR v_Counter IN 1..5 LOOP
13:           DBMS_OUTPUT.PUT(v_Counter);
14:           DBMS_OUTPUT.NEW_LINE;
15:      END LOOP;
16:      DBMS_OUTPUT.GET_LINES(v_HoldBuffer,v_HoldLines);
17:      FOR v_Counter IN 1..v_HoldLines LOOP
18:          DBMS_OUTPUT.PUT_LINE(v_HoldBuffer(v_Counter));
19:      END LOOP;
20: END;

ANALYSIS Your output will look identical to the output generated from Listing 18.7 and Listing 18.8. One of the interesting things you should note is how to display or access the information from GET_LINES. If you remember, the datatype CHARARR is really an array table. To access it, you simply access the variable assigned to the table and specify the location in the array. In this case, this was v_Counter. You could simply think of this as v_HoldBuffer(1) ... v_HoldBuffer(11).

One useful feature of GET_LINES is that you can use it as another means to enter data into a table. However, the source for the buffer called by PUT or PUT_LINES could easily copy from one table to another using a cursor instead, and only use PUT_LINE to display the information as it is copied from one table to the next. As with any procedural language, there are many ways to accomplish the same goal!

Exceptions Raised from the DBMS_OUTPUT Package

There are two exceptions that you have to worry about when using the DBMS_OUTPUT package. These are described in Table 18.5, along with the actions required to fix the problem.

Table 18.5. Exceptions raised by DBMS_OUTPUT.

Now that you know the exceptions, you can trap errors as they occur.

Summary

Today you discovered two methods of output from Oracle. The UTL_FILE package enables you to read and write files for input and output. You can then export the data in any format desired or import from practically any application's output. The DBMS_OUTPUT package, which you used before without much in-depth discussion, enables you to output information to the screen to use primarily as a debugging tool with your PL/SQL code. If you use any version of GET, all output to the screen is suppressed, unless you use PUT_LINE with the variable assigned to the values returned from GET_LINE or GET_LINES. In most cases, you will rarely use GET_LINE or GET_LINES. Instead, PUT_LINE should be sufficient for most of your debugging needs.

Q&A

Q When working with UTL_FILE, can you access a file for reading and writing simultaneously? A No. You can choose only one method of operations on a file. If you attempt to write to a file that is read-only, an exception will be raised. Q What is one major purpose of the UTL_FILE package? A It provides a slick way to import and export data from one application to another, even allowing you to do the proper character conversions. Q What is one problem with not using FCLOSE? A If you are writing a small amount of data to the buffer, if the procedure ends before the buffer fills, the data will not be written. This will lead to data missing from files as well as probable file corruption. You need to flush the buffer either with FFLUSH or FCLOSE, which is the proper way to flush the buffer, and then close the file.