The FETCH
statement retrieves rows of data from the result set of a multiple-row query. You can fetch rows one at a time, several at a time, or all at once. The data is stored in variables or fields that correspond to the columns selected by the query.
Keyword and Parameter Descriptions
Instructs the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. The SQL engine bulk-binds all collections referenced in the INTO
list.
The name of a declared collection into which column values are bulk fetched. For each query select_item
, there must be a corresponding, type-compatible collection in the list.
An explicit cursor declared within the current scope.
A PL/SQL cursor variable (or parameter) declared within the current scope.
An array (declared in a PL/SQL host environment and passed to PL/SQL as a bind argument) into which column values are bulk fetched. For each query select_item
, there must be a corresponding, type-compatible array in the list.
A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. The data type of the host cursor variable is compatible with the return type of any PL/SQL cursor variable.
This optional clause, allowed only in bulk (not scalar) FETCH
statements, lets you bulk fetch several rows at a time, rather than the entire result set.
A user-defined or %ROWTYPE
record into which rows of values are fetched. For each column value returned by the query associated with the cursor or cursor variable, there must be a corresponding, type-compatible field in the record.
A variable into which a column value is fetched. For each column value returned by the query associated with the cursor or cursor variable, there must be a corresponding, type-compatible variable in the list.
You must use either a cursor FOR
loop or the FETCH
statement to process a multiple-row query.
Any variables in the WHERE
clause of the query are evaluated only when the cursor or cursor variable is opened. To change the result set or the values of variables in the query, you must reopen the cursor or cursor variable with the variables set to their new values.
To reopen a cursor, you must close it first. However, you need not close a cursor variable before reopening it.
You can use different INTO
lists on separate fetches with the same cursor or cursor variable. Each fetch retrieves another row and assigns values to the target variables.
If you FETCH
past the last row in the result set, the values of the target fields or variables are indeterminate and the %NOTFOUND
attribute returns TRUE
.
PL/SQL makes sure the return type of a cursor variable is compatible with the INTO
clause of the FETCH
statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible field or variable in the INTO
clause. Also, the number of fields or variables must equal the number of column values.
When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN
or IN
OUT
mode. However, if the subprogram also opens the cursor variable, you must specify the IN
OUT
mode.
Because a sequence of FETCH
statements always runs out of data to retrieve, no exception is raised when a FETCH
returns no data. To detect this condition, you must use the cursor attribute %FOUND
or %NOTFOUND
.
PL/SQL raises the predefined exception INVALID_CURSOR
if you try to fetch from a closed or never-opened cursor or cursor variable.
Restrictions on BULK COLLECT INTO
The following restrictions apply to the BULK
COLLECT
INTO
clause:
You cannot bulk collect into an associative array that has a string type for the key.
You can use the BULK
COLLECT
INTO
clause only in server-side programs (not in client-side programs). Otherwise, you get the following error:
this feature is not supported in client-side programs
All target variables listed in a BULK
COLLECT
INTO
clause must be collections.
Composite targets (such as objects) cannot be used in the RETURNING
INTO
clause. Otherwise, you get the following error:
error unsupported feature with RETURNING clause
When implicit data type conversions are needed, multiple composite targets cannot be used in the BULK
COLLECT
INTO
clause.
When an implicit data type conversion is needed, a collection of a composite target (such as a collection of objects) cannot be used in the BULK
COLLECT
INTO
clause.