PL/SQL DBMS_SQL.FETCH_ROWS

The DBMS_SQL.FETCH_ROWS function is a part of Oracle’s DBMS_SQL package, which provides an interface for dynamic SQL operations in PL/SQL. Dynamic SQL allows you to construct SQL statements dynamically at runtime, rather than having them fully specified at compile time. This flexibility is useful in applications where the exact structure of a query cannot be determined until the program is running.

Syntax

The syntax of the DBMS_SQL.FETCH_ROWS function is as follows:

DBMS_SQL.FETCH_ROWS(cursor_id IN NUMBER) RETURN NUMBER;

cursor_id: This is the identifier of the cursor that is used to execute a SQL statement. The cursor must be opened and parsed before you can fetch rows from it.
RETURN NUMBER: This function returns an integer value that indicates the number of rows fetched. If the function returns 0, it means there are no more rows to fetch.

Usage

To use DBMS_SQL.FETCH_ROWS, you typically follow these steps:

Open a Cursor: Allocate a cursor using DBMS_SQL.OPEN_CURSOR.
Parse the SQL Statement: Prepare a SQL statement for execution using DBMS_SQL.PARSE.
Bind Variables: Optionally, bind any variables to the SQL statement using DBMS_SQL.BIND_VARIABLE.
Execute the Statement: Execute the prepared statement using DBMS_SQL.EXECUTE.
Define Columns: For select statements, define columns in the result set using DBMS_SQL.DEFINE_COLUMN.
Fetch Rows: Use DBMS_SQL.FETCH_ROWS to fetch the result set rows. This function is usually called in a loop until it returns 0, indicating that there are no more rows to fetch.
Close the Cursor: After fetching all rows, close the cursor using DBMS_SQL.CLOSE_CURSOR.

Example

Here’s a simple example of how to use DBMS_SQL.FETCH_ROWS:

DECLARE
  cursor_id NUMBER;
  rows_fetched NUMBER;
BEGIN
  cursor_id := DBMS_SQL.OPEN_CURSOR;
  
  DBMS_SQL.PARSE(cursor_id, 'SELECT employee_id, last_name FROM employees WHERE department_id = :1', DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(cursor_id, ':1', 10); -- Assuming department_id 10
  
  DBMS_SQL.DEFINE_COLUMN(cursor_id, 1, employee_id NUMBER);
  DBMS_SQL.DEFINE_COLUMN(cursor_id, 2, last_name VARCHAR2(50));
  
  IF DBMS_SQL.EXECUTE(cursor_id) > 0 THEN
    LOOP
      rows_fetched := DBMS_SQL.FETCH_ROWS(cursor_id);
      EXIT WHEN rows_fetched = 0;
      
      -- Process fetched rows here
      -- For example, fetch column values using DBMS_SQL.COLUMN_VALUE procedure
    END LOOP;
  END IF;
  
  DBMS_SQL.CLOSE_CURSOR(cursor_id);
END;

This example demonstrates fetching rows from a simple select statement using dynamic SQL. Note that actual implementations might need to retrieve and handle the column values within the loop, which can be done using the DBMS_SQL.COLUMN_VALUE procedure.