Generating Dynamic SQL 477
16
Of those three steps, only the first step represents a difference from what you would do
to process a static
SELECT
statement by using PL/SQL.
The Syntax for the
OPEN...FOR
Statement
OPEN cursor
FOR string
[USING bind[, bind]...];
In this syntax the parameters are as follows:
•
cursor
is the cursor that you want to open. This is actually a pointer to a cursor,
and must be a
REF CURSOR
variable.
•
string
is a variable or literal that contains the SQL statement you want to execute.
•
bind
is a bind variable. You use these to pass parameters to the dynamic SQL state-
ment. The parameter markers are numbered, and must be
:1
,
:2
, and so on. The
first bind variable becomes the value

4:
5: -- Declare a record type for the output
6: TYPE dyn_record IS RECORD (
7: yourrow yourtable.yourrow%TYPE,
8: yourdesc yourtable.yourdesc%TYPE
9: );
10:
11: -- Note, could also use:
12: -- dyn_rec yourtable%ROWTYPE;
13: --
14: dyn_rec dyn_record;
15:
,
S
YNTAX
,
I
NPUT
continues
22 7982 ch16 11/30/99 1:06 PM Page 477
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
16: dynamic_select_stmt VARCHAR2(100);
17: BEGIN
18: -- Generate the dynamic SELECT statement.
19: dynamic_select_stmt :=
20: ‘SELECT yourrow, yourdesc FROM yourtable’;
21: dynamic_select_stmt := dynamic_select_stmt ||
22: ‘ ORDER BY yourrow DESC’;
23:
24: -- Open a cursor on the dynamic statement.

statement is declared in lines 2–3.
Lines 18–20 build on the initial
SELECT
statement, and the next two lines (21–22)
add an
ORDER BY
clause. The cursor is opened in line 25, through the use of the
OPEN...FOR
statement. Note that
OPEN FOR
references the
VARCHAR2
variable containing
the
SELECT
statement. From here on out, it’s just normal everyday PL/SQL cursor pro-
cessing. The
FETCH
in lines 31–32 fetches the data from the cursor into a record. You
could also fetch the data into a list of variables. The
DBMS_OUTPUT
call in line 36–37
allow SQL*Plus to display the data, and the
CLOSE
statement in line 41 closes the cursor
after all the data has been processed.
478 Day 16
L
ISTING
16.8 continued

15: DBMS_OUTPUT.PUT_LINE(YourRow || ‘ ‘ || YourDesc);
16: END;
17: /
You can see that this code is a lot simpler to understand than the Listing 16.4
version. The
block_to_execute
variable, declared in lines 2–7, contains the
PL/SQL block to be executed. The
EXECUTE IMMEDIATE
statement in lines 12–13 is used
to execute the block. The crucial thing to note here is that the bind variables listed in the
USING
clause both have the keyword
OUT
in front of them. This allows them to receive
values back from the PL/SQL block. So the PL/SQL block issues a
SELECT...INTO
state-
ment that places values into these variables, and because they are
OUT
bind variables,
those values are returned to you.
I
NPUT
A
NALYSIS
This code fails if there are two rows in
YOURTABLE
with a value of
2

Q&A
QNow that native dynamic SQL has arrived, is there any reason I would ever
use
DBMS_SQL
?
A There probably are some reasons, but there sure aren’t many. One thing that
DBMS_SQL
can handle that native dynamic SQL can’t, at least not easily, is the situa-
tion where you know absolutely nothing about the tables and columns that you will
be querying.
DBMS_SQL
allows you to issue a query, and then dynamically discover
how many columns the query returns, as well as what the datatypes are. That’s a
fairly advanced use, but if you need to be able to do it, then you need to use
DBMS_SQL
.
Q What three types of statements can be executed dynamically?
A Using dynamic SQL, you can execute non-query DDL and DML statements, SQL
queries, and anonymous PL/SQL blocks.
Q Should dynamically executed queries be written with trailing semicolons?
A No! This is a very common mistake to make. Do not include a trailing semicolon
with any dynamic SQL statement. The reason is that strictly speaking, the semi-
colon is not part of the SQL syntax. You need it when you write a static SQL state-
ment, because Oracle needs to know where the SQL statement ends. However,
when you are writing dynamic SQL, you are only working with one statement at a
time, so a terminator is not needed.
480 Day 16
22 7982 ch16 11/30/99 1:06 PM Page 480
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Generating Dynamic SQL 481

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
D
AY
17
W
EEK
3
Writing to Files and the
Display
by Timothy Atwood and Jonathan Gennick
The PL/SQL language itself does not have any mechanism for performing
either file or screen output. However, Oracle supplies a number of built-in
packages that allow you to perform I/O, and that can be called from PL/SQL.
Today’s lesson talks about the following:
• The
DBMS_OUTPUT
package
• The
UTL_FILE
package
• The
TEXT_IO
package
You’ve already seen
DBMS_OUTPUT
used throughout this book as a way to dis-
play output on the screen, using SQL*Plus. It has some other capabilities, too,
which you’ll learn about today. The
UTL_FILE
and

buffer in memory.
PL/SQL Program Unit
PUT_LINE
Memory Buffer
GET_LINE
SQL*Plus
DBMS_OUTPUT
Package
From PL/SQL, you make
calls to
PUT_LINE
in order
to place text in the buffer
SQL*Plus reads
text from the buffer
by making calls to
GET_LINE
The usefulness of
DBMS_OUTPUT
becomes apparent when you realize that the procedure
that reads data from the buffer does not have to be the same procedure that wrote it there
in the first place. Any procedure can read the data. When you issue the command
SET
SERVEROUTPUT ON
in SQL*Plus, you are really telling SQL*Plus to check the buffer for
data after each statement executes, fetch any data that’s found, and display it for you to
see. In its most generic sense,
DBMS_OUTPUT
can be used to communicate data between
any two PL/SQL procedures.

DBMS_OUTPUT.ENABLE
yourself would be if SQL*Plus isn’t the destination for your messages.
The Syntax for the
DBMS_OUTPUT.ENABLE
Procedure
DBMS_OUTPUT.ENABLE (buffer_size IN INTEGER DEFAULT 20000);
The
buffer_size
parameter controls the size of the buffer, and can be any value between
2,000 and 1,000,000. The default is 20,000.
The following PL/SQL block shows a call to enable the
DBMS_OUTPUT
package:
BEGIN
DBMS_OUTPUT.ENABLE (1000000);
END;
If you are using
DBMS_OUTPUT
to send a lot of data to SQL*Plus, keep in mind that
SQL*Plus can’t begin reading until all the data is sent. Therefore, your buffer must be
large enough to contain all the output. Also bear in mind that SQL*Plus release 8.0 and
above allows you to specify the buffer size as an argument to the
SET SERVEROUTPUT ON
command. For example, the following command also enables
DBMS_OUTPUT
, and with a
buffer size of 1,000,000 bytes:
SET SERVEROUTPUT ON SIZE 1000000
After you enable the package, you can use it to write data to the buffer, and to read it
back again.

S
YNTAX
S
YNTAX
23 7982 ch17 11/30/99 1:05 PM Page 485
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Writing Data to the Buffer
Youwrite data to the
DBMS_OUTPUT
buffer by using a combination of the
PUT_LINE
,
PUT
,
and
NEW_LINE
procedures.
PUT_LINE
writes a line of text, followed by a newline charac-
ter.
PUT
writes text, but doesn’t follow that text with a newline. The
NEW_LINE
procedure
writes one newline character.
The Syntax for the
DBMS_OUTPUT.PUT_LINE
,
DBMS_OUTPUT.PUT
, and

ture the output.
L
ISTING
17.1
Using
DBMS_OUTPUT
to Place Data in the Buffer
1: SET SERVEROUTPUT ON
2:
3: BEGIN
4: --We only need a small buffer for this example.
5: DBMS_OUTPUT.ENABLE (2000);
6:
7: DBMS_OUTPUT.PUT_LINE(‘Three names will be written.’);
8: DBMS_OUTPUT.PUT(‘Jenny’);
9: DBMS_OUTPUT.NEW_LINE;
10: DBMS_OUTPUT.PUT(‘Shirley’);
11: DBMS_OUTPUT.NEW_LINE;
12: DBMS_OUTPUT.PUT(‘Tina’);
486 Day 17
,
S
YNTAX
,
I
NPUT
23 7982 ch17 11/30/99 1:05 PM Page 486
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Writing to Files and the Display 487
17

GET_LINES
,allow you to read from the buffer.
GET_LINE
allows you to read one line at a time, and
GET_LINES
allows you to read several lines into
an array.
The Syntax for
GET_LINE
and
GET_LINES
DBMS_OUTPUT.GET_LINE (
line OUT VARCHAR2,
status OUT INTEGER);
DBMS_OUTPUT.GET_LINES (
lines OUT CHARARR,
numlines IN OUT INTEGER);
In this syntax the parameters are as follows:
•
line
is the line retrieved by
GET_LINE
.
•
status
indicates whether a line was retrieved. A status of
1
means that the line
parameter contains a line retrieved from the buffer. A status of
0

being
used to retrieve the three names from the buffer. It also demonstrates that the buffer con-
tents are maintained across PL/SQL blocks.
488 Day 17
This listing should be executed from SQL*Plus.
Note
L
ISTING
17.2
Using
GET_LINE
to Retrieve Data From the Buffer
1: SET SERVEROUTPUT OFF
2:
3: BEGIN
4: --We only need a small buffer for this example.
5: DBMS_OUTPUT.ENABLE (2000);
6:
7: DBMS_OUTPUT.PUT_LINE(‘Three names will be written.’);
8: DBMS_OUTPUT.PUT(‘Jenny’);
9: DBMS_OUTPUT.NEW_LINE;
10: DBMS_OUTPUT.PUT(‘Shirley’);
11: DBMS_OUTPUT.NEW_LINE;
12: DBMS_OUTPUT.PUT(‘Tina’);
13: DBMS_OUTPUT.NEW_LINE;
14: END;
15: /
16:
17: PL/SQL procedure successfully completed.
18:

17
41: END;
42: /
Jenny and Shirley and Tina
PL/SQL procedure successfully completed.
The first PL/SQL block (lines 3–14) writes three names into the buffer, one name
to a line. Because this example is executed from SQL*Plus, the
SERVEROUTPUT
setting is turned off (line 1) to prevent SQL*Plus from reading and displaying the names.
Instead, the names remain in the buffer, where they can be accessed by the second block.
The second PL/SQL block (lines 21–41) reads the first line and throws it away (line
28–29). Then it reads each of the three names (lines 32–34). Finally, it concatenates
those three names together in one line, and writes that line back out to the buffer (lines
36–40). Because the
SERVEROUTPUT
setting has been turned on for the second block,
SQL*Plus reads the results and displays them onscreen.
A
NALYSIS
The buffer is a first-in, first-out (FIFO) buffer. The first line to be written is
also the first to be read.
Note
If you need to retrieve several lines at once from the buffer, you can use the
GET_LINES
procedure. It accepts a PL/SQL table (the equivalent of an array) as an argument, and
retrieves as many lines as you request into that array. By modifying the code shown in
Listing 17.2 to use
GET_LINES
instead of
GET_LINE

14: END;
15: /
16:
17: PL/SQL procedure successfully completed.
18:
19:
20: SET SERVEROUTPUT ON
21: DECLARE
22: throw_away VARCHAR2(50);
23: names DBMS_OUTPUT.CHARARR;
24: lines_to_get NUMBER;
25: inx1 NUMBER;
26: combined_names VARCHAR2(80);
27: status NUMBER;
28: BEGIN
29: --The first line in the buffer is a throw away.
30: DBMS_OUTPUT.GET_LINE(throw_away, status);
31:
32: --The next three lines will be the three names.
33: lines_to_get := 3;
34: DBMS_OUTPUT.GET_LINES(names, lines_to_get);
35:
36: --Now that we have the names, write them out
37: --on one line. SQL*Plus will pick this up and
38: --display it.
39: combined_names := ‘’;
40: FOR inx1 IN 1 .. lines_to_get LOOP
41: IF inx1 > 1 THEN
42: combined_names := combined_names || ‘ and ‘;
43: END IF;

parameter contains the value
3
,
telling
GET_LINES
to return all three names. The loop in line 40–43 then uses the value
that
GET_LINES
passes back to iterate through the array the proper number of times. If
GET_LINES
returned more than three lines, or fewer than three lines, the value of
lines_to_get
would be set appropriately, and the loop would concatenate all the lines
together.
Exceptions Raised from the
DBMS_OUTPUT
Package
There are two exceptions you have to worry about when using the
DBMS_OUTPUT
package.
These are described in Table 17.1, along with the actions required to fix the problems.
T
ABLE
17.1
Exceptions Raised by
DBMS_OUTPUT
Exception Code Error Description Corrective Action
ORU-10027
Buffer overflow Increase the buffer size if possible.
Otherwise, find a way to write less data.

GRANT EXECUTE ON utl_file TO username;
I
NPUT
23 7982 ch17 11/30/99 1:05 PM Page 491
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The matter of the
UTL_FILE_DIR
parameter is a bit more difficult to explain. You can’t
just read and write files in any directory on the server. When you make calls to
UTL_FILE_DIR
,Oracle is really reading and writing the files for you. On most systems,
the Oracle software runs in privileged mode, giving it access to all the files. Needless to
say, that presents a security risk. To mitigate that risk, before
UTL_FILE
can be used, your
database administrator must set the
UTL_FILE_DIR
parameter to point to a specific list of
directories. All file I/O done by
UTL_FILE
must be done in one of those directories. The
examples in this book assume the following setting:
UTL_FILE_DIR = c:\a
If you’re experimenting with
UTL_FILE
on a workstation, you need to add this line to the
database parameter file. You also need to stop and restart the database afterward, in order
for the new setting to take effect.
When
UTL_FILE_DIR

5. When you’re done, call
UTL_FILE.FCLOSE
to close the file.
The next section talks briefly about the various
UTL_FILE
procedures and functions, and
shows the syntax information for each. Following that is a section with an example
showing
UTL_FILE
being used to write data to a file.
492 Day 17
23 7982 ch17 11/30/99 1:05 PM Page 492
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Writing to Files and the Display 493
17
Using
UTL_FILE
Procedures and Functions
The
UTL_FILE
package implements the following procedures and functions:
Procedure
or Function Description
FCLOSE
Closes a file.
FCLOSE_ALL
Closes all the files.
FFLUSH
Flushes any buffered data to be written out to disk
immediately.

The
FCLOSE
Procedure
The
FCLOSE
procedure closes a file. If the buffer for the file being closed is not empty, it
is flushed to disk before the file is closed.
The Syntax for the
FCLOSE
Procedure
PROCEDURE FCLOSE(file IN OUT file_type);
The
file
parameter is the file handle returned from
FOPEN
when the file was originally
opened. Table 17.2 shows a list of possible exceptions raised by
FCLOSE
.
S
YNTAX
23 7982 ch17 11/30/99 1:05 PM Page 493
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
T
ABLE
17.2
Exceptions Raised by
FCLOSE
and
FCLOSE_ALL

IS_OPEN
still indicate that they are open, even though in reality they are
not.
The
FOPEN
Procedure
FOPEN
opens a file for reading, or for writing.
FOPEN
is a function, and it returns a file
handle pointing to the file that was opened. There are two versions of
FOPEN
. One allows
you to specify a maximum line size, and the other does not.
The Syntax for the
FOPEN
Procedure
FUNCTION FOPEN(location IN VARCHAR2,
filename IN VARCHAR2,
openmode IN VARCHAR2)
RETURN FILE_TYPE;
FUNCTION FOPEN(location IN VARCHAR2,
filename IN VARCHAR2,
openmode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN FILE_TYPE;
494 Day 17
S
YNTAX
,

A
to append to an existing file.
•
max_linesize
allows you to specify the maximum line size. The allowed range is
1 through 32,767. If you omit this parameter, then the default, 1023, is used.
,
The ability to specify a line size in the
FOPEN
call is a new feature in Oracle8i.
With releases of Oracle prior to 8.1.5, you are limited to a line size of 1023
bytes or less.
If you open a file for write, and a file with the same name exists already,
that file is overwritten. If you append to a file that does not exist, a new file
is created.
Note
After the file has been successfully opened, the
FOPEN
function returns a file handle. You
must use that handle for all further operations on the file.
FOPEN
can raise several excep-
tions, which are listed in Table 17.3.
T
ABLE
17.3
Exceptions Raised by
FOPEN
Exception Raised Description of Error
UTL_FILE.INVALID_PATH

that problem is. The internal error is something you should never get, and indicates that
Oracle is not functioning properly.
The
GET_LINE
Function
When performing file input, in order to read data from the file into the buffer, you use
the
GET_LINE
function.
The Syntax for the
GET_LINE
Function
PROCEDURE GET_LINE(file IN FILE_TYPE,
buffer OUT VARCHAR2);
The parameters are as follows:
•
file
is the file handle returned from the FOPEN function when the file was origi-
nally opened.
•
buffer
is where the data is placed after it is read from the file. This must be of
type
VARCHAR2
. Possible errors that could arise are shown in Table 17.4.
T
ABLE
17.4
Exceptions Raised by
GET_LINE

open a file to be sure that it’s not already open. You can also test to make sure a file is
open before you attempt to close it.
496 Day 17
,
S
YNTAX
,
23 7982 ch17 11/30/99 1:05 PM Page 496
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.