Oracle PL/SQL Programming: A Developer's Workbook

Intermediate

Q:
21-15.	The steps are: Open the cursor: OPEN_CURSOR Parse the SQL statement string: PARSE Define the type of each of the columns: DEFINE_COLUMN DEFINE_COLUMN_LONG DEFINE_ARRAY Bind any variables into placeholders: BIND_VARIABLE BIND_ARRAY Execute the cursor: EXECUTE EXECUTE_AND_FETCH Fetch a row: FETCH_ROWS 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 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 ...