2.3.6 Executing the Cursor
So you've opened and parsed the cursor. You've bound your variables and defined your columns. Now it's
time to get some work done.
2.3.6.1 The DBMS_SQL.EXECUTE function
The EXECUTE function executes the SQL statement associated with the specified cursor,
FUNCTION DBMS_SQL.EXECUTE (c IN INTEGER) RETURN INTEGER;
where c is the pointer to the cursor. This function returns the number of rows processed by the SQL statement
if that statement is an UPDATE, INSERT, or DELETE. For all other SQL (queries and DDL) and PL/SQL
statements, the value returned by EXECUTE is undefined and should be ignored.
If the SQL statement is a query, you can now call the FETCH_ROWS function to fetch rows that are retrieved
by that query. If you are executing a query, you can also use EXECUTE_AND_FETCH to execute the cursor
and fetch one or more rows with a single program call.
2.3.7 Fetching Rows
You can fetch one or more rows of data from a dynamically constructed query with either the FETCH_ROWS
or EXECUTE_AND_FETCH functions.
NOTE: Prior to PL/SQL8, both of these functions would return either 0 (no rows fetched) or
1 (one row fetched). With PL/SQL8 and array processing, these functions will return 0 (no
rows fetched) or the actual number of rows fetched.
2.3.7.1 The DBMS_SQL.FETCH_ROWS function
The FETCH_ROWS function corresponds to the FETCH statement for regular PL/SQL cursors. It fetches the
next N rows from the cursor (a maximum of one if not using array processing in PL/SQL8). Here's the
specification for the function,
FUNCTION DBMS_SQL.FETCH_ROWS
(c IN INTEGER) RETURN INTEGER;
where c is the pointer to the cursor. The function returns 0 when there are no more rows to fetch. You can
therefore use FETCH_ROWS much as you would FETCH and the %FOUND (or %NOTFOUND) attributes.
The following two sets of statements are equivalent:
•
Use a normal, static cursor:
FETCH emp_cur INTO emp_rec;
IF emp_cur%FOUND

THEN
process data
END IF;
•
Use DBMS_SQL to fetch rows:
IF DBMS_SQL.FETCH_ROWS (c) > 0
THEN
process data
END IF;
[Appendix A] What's on the Companion Disk?
2.3.6 Executing the Cursor 66
So that happens when you fetch past the end of the cursor's result set? With static cursors, you can fetch all
you want and never raise an error. In the following block, for example, I fetch 1000 times from a table with 14
rows. (C'mon, you knew that, right? The emp table has 14 rows.)
DECLARE
CURSOR empcur IS SELECT * FROM emp;
emprec empcur%ROWTYPE;
BEGIN
OPEN empcur;
FOR rowind IN 1 1000
LOOP
FETCH empcur INTO emprec;
END LOOP;
END;
/
No problem −− and no exceptions! After the fourteenth fetch, the FETCH statement simply does nothing
(and the record continues to hold the fourteenth row's information).
However, the "fetch past last record" behavior with dynamic SQL is different. The FETCH_ROWS function
will raise the ORA−01002 exception: fetch out of sequence, if you fetch again after a call to FETCH_ROWS
has returned 0. The following anonymous block raises the ORA−01002 error, because there are only three

employees in department 10:
DECLARE
cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
fdbk PLS_INTEGER;
BEGIN
DBMS_SQL.PARSE
(cur, 'SELECT * FROM emp WHERE deptno = 10', DBMS_SQL.NATIVE);
fdbk := DBMS_SQL.EXECUTE_CURSOR (cur);
FOR Nfetch IN 1 5
LOOP
/* On fetch #5 this will raise ORA−01002 */
fdbk := DBMS_SQL.FETCH_ROWS (cur);
END LOOP;
DBMS_SQL.CLOSE_CURSOR (cur);
END;
/
The following procedure shows how you can employ the FETCH_ROWS logic inside of a loop to fetch each
of the rows from a cursor and place column values in an Oracle Forms block:
/* Filename on companion disk: fillblck.fp */
PROCEDURE fill_block (where_clause_in IN VARCHAR2)
/*
|| Query data from table using a dynamic where clause and then
|| pass those values to an Oracle Forms block.
*/
IS
/*
|| Declare cursor handle and parse the query, all in a single
|| statement using open_and_parse (see PARSE description).
*/
c INTEGER := DBMS_SQL.OPEN_CURSOR;

emprec employee%ROWTYPE;
/* return value from EXECUTE; ignore in case of query */
fdbk INTEGER;
BEGIN
/* Parse the query with a dynamic WHERE clause */
[Appendix A] What's on the Companion Disk?
2.3.6 Executing the Cursor 67
DBMS_SQL.PARSE (c,
'SELECT employee_id, last_name ' ||
' FROM employee WHERE ' || where_clause_in,
DBMS_SQL.NATIVE);
/* Define the columns in the cursor for this query */
DBMS_SQL.DEFINE_COLUMN (c, 1, emprec.empno);
DBMS_SQL.DEFINE_COLUMN (c, 2, emprec.ename, 30);
/* Now I can execute the query */
fdbk:= DBMS_SQL.EXECUTE (c);
LOOP
/* Try to fetch next row. If done, then exit the loop. */
EXIT WHEN DBMS_SQL.FETCH_ROWS (c) = 0;
/*
|| Retrieve data via calls to COLUMN_VALUE and place those
|| values in a new record in the block.
*/
DBMS_SQL.COLUMN_VALUE (c, 1, emprec.empno);
DBMS_SQL.COLUMN_VALUE (c, 2, emprec.ename);
CREATE_RECORD;
:employee.employee_id := emprec.empno;
:employee.employee_nm := emprec.ename;
END LOOP;
/* Clean up the cursor */

DBMS_SQL.CLOSE_CURSOR (c);
END;
In this example, you can fetch only one row at a time, because you defined each of your columns in the cursor
to hold a single value. If, on the other hand, you defined a column as an array, then the fetch could receive
multiple rows in a single call. This approach is explored in more detail in the section Section 2.5.6, "Array
Processing with DBMS_SQL"."
2.3.7.2 The DBMS_SQL.EXECUTE_AND_FETCH function
The EXECUTE_AND_FETCH function executes the SELECT statement associated with the specified cursor
and immediately fetches the rows associated with the query. Here's the specification for the function.
FUNCTION DBMS_SQL.EXECUTE_AND_FETCH
(c IN INTEGER
,exact IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;
Parameters are summarized in the following table.
Parameter Description
c The handle or pointer to the cursor originally returned by a call to OPEN_CURSOR.
exact Set to TRUE if you want the function to raise an exception when it fetches more than one row.
Even if EXECUTE_AND_FETCH does raise an exception (TOO_MANY_ROWS), the rows will still be
fetched and available. The value returned by the function will, however, be NULL.
This function is designed to make it easy to execute and fetch a single row from a query. It is very similar to
the implicit SELECT cursor in native PL/SQL, which either returns a single row, multiple rows (for PL/SQL8
array access only), or raises the TOO_MANY_ROWS exception (ORA−01422).
See the sidebar entitled the sidebar "Oracle: The Show Me Technology"," for a script you can use to examine
the behavior of this built−in function.
[Appendix A] What's on the Companion Disk?
2.3.7 Fetching Rows 68
2.3.8 Retrieving Values
Oracle: The Show Me Technology
If there is one thing I have learned over the years, it is that if I am not absolutely sure about the way a
particular feature works, I have to try it out and see what happens. I have two primary references for writing

about PL/SQL technology: the Oracle documentation and the Oracle software. The former is helpful for
guiding my "first draft." The latter is helpful for making sure that my "final draft" is accurate and honest. See,
unfortunately, just because the documentation says that something works a certain way, that doesn't
necessarily mean it really does operate as advertised.
I built the following script (designed for use in SQL*Plus) to make it easier for Oracle to "show me" exactly
the kind of variations in behavior of DBMS_SQL.EXECUTE_AND_FETCH I might encounter. You use the
first argument to indicate whether or not you want rows to be found. The second argument dictates the
error−handling behavior. I thought I would share it with you to give you a sense of how these kinds of scripts
can make it very easy to test functionality and behavior.
/* Filename on companion disk: execftch.sql */
DECLARE
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
fdbk INTEGER;
BEGIN
DBMS_SQL.PARSE
(cur, 'SELECT empno FROM emp where 1=&1', DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN (cur, 1, 1);
fdbk := DBMS_SQL.EXECUTE_AND_FETCH (cur, &2);
DBMS_OUTPUT.PUT_LINE (fdbk);
DBMS_SQL.CLOSE_CURSOR (cur);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('error code ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE
('returned by e_and_f ' || NVL (TO_CHAR (fdbk), '[NULL]'));
END;
/
Here are the results of executing this script in SQL*Plus:
SQL> @execftch 1 false

1
SQL> @execftch 2 false
0
SQL> @execftch 1 true
error code −1422
returned by e_and_f [NULL]
SQL> @execftch 2 true
error code 100
returned by e_and_f [NULL]ORA−01403: no data found
If you construct a dynamic SELECT or PL/SQL block, you can retrieve values from the cursor after
execution. Use the COLUMN_VALUE procedure to obtain the values of individual columns in the fetched
row of a SELECT. Use the COLUMN_VALUE_LONG procedure to obtain the values of a LONG column in
the fetched row of a SELECT. Use the VARIABLE_VALUE procedure to extract the values of variables in a
dynamic PL/SQL block.
[Appendix A] What's on the Companion Disk?
2.3.8 Retrieving Values 69
2.3.8.1 The DBMS_SQL.COLUMN_VALUE procedure
The COLUMN_VALUE procedure retrieves a value from the cursor into a local variable. Use this procedure
when the SQL statement is a query and you are fetching rows with EXECUTE_AND_FETCH or
FETCH_ROWS. You can retrieve the value for a single column in a single row or, with PL/SQL8, you can
retrieve the values for a single column across multiple rows fetched. The header for the single−row version of
the procedure is as follows:
PROCEDURE DBMS_SQL.COLUMN_VALUE
(c IN INTEGER,
position IN INTEGER,
value OUT <datatype>,
[, column_error OUT NUMBER]
[, actual_length OUT INTEGER ]);
The COLUMN_VALUE parameters are summarized in the following table.
Parameter Description

c Pointer to the cursor.
position Relative position of the column in the select list.
value The PL/SQL structure that receives the column value. If the <datatype> of this argument
does not match that of the cursor's column, DBMS_SQL will raise the
DBMS_SQL.INCONSISTENT_DATATYPE exception.
<table_parameter> The PL/SQL table (of type <table_type>) holding one or more colum values, depending
on how many rows were previously fetched.
column_error Returns an error code for the specified value (the value might be too large for the
variable, for instance).
actual_length Returns the actual length of the returned value before any truncation takes place (due to
a difference in size between the retrieved value in the cursor and the variable).
<datatype> can be one of the following types:
NUMBER
DATE
MLSLABEL
VARCHAR2 CHARACTER SET ANY_CS
BLOB
CLOB CHARACTER SET ANY_CS
BFILE
The header for the multiple−row version of COLUMN_VALUE is as follows:
PROCEDURE DBMS_SQL.COLUMN_VALUE
(c IN INTEGER,
position IN INTEGER,
<table_parameter> OUT <table_type>);
<table_parameter> OUT <table_type> can be one of the following:
n_tab OUT DBMS_SQL.NUMBER_TABLE
c_tab OUT DBMS_SQL.VARCHAR2_TABLE
d_tab OUT DBMS_SQL.DATE_TABLE
bl_tab OUT DBMS_SQL.BLOB_TABLE
cl_tab OUT DBMS_SQL.CLOB_TABLE

bf_tab OUT DBMS_SQL.BFILE_TABLE
The DBMS_SQL package also offers more specific variants of COLUMN_VALUE for less common
[Appendix A] What's on the Companion Disk?
2.3.8 Retrieving Values 70