Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL Programming: A Developer's Workbook

Intermediate

Q:

21-15.

The steps are:

  1. Open the cursor:

    OPEN_CURSOR
  2. Parse the SQL statement string:

    PARSE
  3. Define the type of each of the columns:

    DEFINE_COLUMN
    DEFINE_COLUMN_LONG
    DEFINE_ARRAY
  4. Bind any variables into placeholders:

    BIND_VARIABLE
    BIND_ARRAY
  5. Execute the cursor:

    EXECUTE
    EXECUTE_AND_FETCH
  6. Fetch a row:

    FETCH_ROWS
  7. Extract the value from a column in the fetched row (after fetch):

    COLUMN_VALUE
    COLUMN_VALUE_LONG
    COLUMN_VALUE_ROWID
    COLUMN_VALUE_RAW
    COLUMN_VALUE_CHAR
  8. Close the cursor:

    CLOSE_CURSOR

Q:

21-16.

When you fetch past the last row with DBMS_SQL.FETCH_ROWS, the PL/SQL runtime engine raises the error:

ORA-01002: fetch out of sequence

To demonstrate this behavior, run the queens.sql script (found on the book’s web page). It creates a table named corporate_welfare_queens and then runs an infinite loop to fetch forever against that table (there might be that many corporations on the dole, but there are only three rows in the table). The result is this output:

McDonald's
General Electric
Boeing
Boeing
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence

Q:

21-17.

The DBMS_SQL.EXECUTE_AND_FETCH function is comparable to SELECT INTO. It executes the query and then tries to fetch the first row.

Q:

21-18.

It might raise the TOO_MANY_ROWS exception (just as SELECT INTO does), or it might ignore that problem. The behavior of EXECUTE_AND_FETCH in a “TOO MANY ROWS” situation is something you can control. The header for this function is:

FUNCTION DBMS_SQL.EXECUTE_AND_FETCH ( c IN INTEGER ,exact IN BOOLEAN ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page