Listing 18.6. continued

21:           UTL_FILE.PUT_LINE(v_MYFileHandle,'"' || v_SSNO || `","'
22:                || v_FirstName || `","' || v_LastName || `"`);
23:      END LOOP;
24: -- Close Cursor
25:      CLOSE c_PRES;
26: -- Close CSV file
27:      UTL_FILE.FCLOSE(v_MYFileHandle);
28: END;
29: /

After you have executed the code in Listing 18.6, your output should be identical to the following:

OUTPUT
"001010111","George","Washington"
"005124745","Thomas","Jefferson"
"223445726","Richard","Nixon"
"378112475","Jimmy","Carter"
"541129744","Abraham","Lincoln"
"587145961","John","Kennedy"
"665474112","Theodore","Roosevelt"
"725139511","George","Bush"
"998211247","Ronald","Reagan"

As you learned on Day 10, "Manipulating Data with Cursors, DELETE, and UPDATE," you use an explicit cursor to read in the entire table, one row at a time. After the loop executes, you can then use the UTL_FILE package to output using PUT_LINE the data from the table in CSV format.

TEXT_IO

The TEXT_IO package is available only by purchasing Oracle Developer/2000. This package is not as sophisticated as UTL_FILE because it lacks the named exceptions provided by the UTL_FILE package, and it is missing such modules as FFLUSH and FCLOSE_ALL. It is my recommendation that you stick with the UTL_FILE package when working with file input and output.

The DBMS_OUTPUT Package

Several of the previous chapters used the DBMS_OUTPUT package already to demonstrate PL/SQL when there was no apparent output. I use this package extensively as a debugging tool in my PL/SQL, similar to using printf statements in C to see the value of variables as the code executes. This package enables you to store information into a buffer and then to retrieve this information. The buffer can contain lines of 255 characters up to the size of the buffer. As you saw in earlier chapters, using PUT or PUT_LINE will send the output to screen if GET_LINE or GET_LINES is not used.

Enabling the DBMS_OUTPUT Package

There are two methods to enable the DBMS_OUTPUT package. The first method utilizes SQL*Plus, which you saw in earlier chapters.

The code line used to enable is

SET SERVEROUTPUT ON

You can increase the buffer size in SQL*Plus by adding the following:

SET SERVEROUTPUT ON SIZE 1000000

The maximum size of the buffer is 1,000,000 bytes. The default size is only 20,000 bytes, so it is usually a good idea to increase it to the maximum of 1,000,000 bytes.

You can also enable output through PL/SQL by using the ENABLE statement, as in the following procedure:

DBMS_OUTPUT.ENABLE;

If you want to control the buffer size, the command would now be

DBMS_OUTPUT.ENABLE (1000000);

Disabling the DBMS_OUTPUT Package

After you are finished with the DBMS_OUTPUT package, you can disable it through SQL*Plus or PL/SQL by entering the following code line:

SET SERVEROUTPUT OFF

When using PL/SQL with the DBMS_OUTPUT package instead of SQL, you would use

DBMS_OUTPUT.DISABLE;

All output will now be disabled completely after executing the preceding statements.

Using PUT with the DBMS_OUTPUT Package

The PUT procedure works similarly to the UTL_FILE package. This procedure enables you to place up to a maximum of 255 characters into the buffer. The datatypes can be NUMBER, DATE, and VARCHAR2. This does not add a newline character, so it will not output to screen until you call the procedure NEW_LINE or use PUT_LINE, as long as you do not exceed 255 characters. The syntax for PUT is

PROCEDURE PUT(data_to_display);

The NEW_LINE Procedure

Similar to the ULT_FILE package, NEW_LINE generates the operating specific newline character. Generally, NEW_LINE is used in conjunction with PUT. The syntax for NEW_LINE is

PROCEDURE NEW_LINE;

Working with PUT_LINE

PUT_LINE works the same as PUT, except that it adds the newline character at the end so output is printed immediately.

The Syntax for the PUT_LINE Procedure

PROCEDURE PUT_LINE(data_to_display);

The data_to_display can be of type VARCHAR2, DATE, and NUMBER.

Testing PUT, NEW_LINE, and PUT_LINE

In this section, you will test three of the procedures by enabling from within SQL*Plus and also from within PL/SQL.

Enabling with SQL*Plus
To enable the DBMS_OUTPUT package, at the prompt type in

SET SERVEROUTPUT ON

After you press Enter, the output package will be enabled. Now enter and execute the code in Listing 18.7.

Listing 18.7. Using DBMS_OUTPUT with PUT, NEW_LINE, and PUT_LINE.

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

Your output should appear as

OUTPUT
11
22
33
44
55
We will now test with a newline character
1
2
3
4
5

ANALYSIS Because NEW_LINE is not called after the PUT statement in line 8 in the first FOR loop (beginning in line 5), no output occurred until PUT_LINE was called, because PUT_LINE includes the NEW_LINE character. This is why you doubled the numbers on the output for the first FOR loop. The second loop, starting in line 13, is identical to the first loop, except it adds NEW_LINE in line 15 after PUT in line 14. You can now display one set of numbers for the counter in the FOR loop. It still would be easier to use PUT_LINE instead of PUT and NEW_LINE. You can now disable the output by typing

INPUT
SET SERVEROUTPUT OFF

If you want, go ahead and re-execute the code in Listing 18.7, and you will notice that output is no longer shown on the screen.