A script-enabled browser is required for this page to function properly.

EXEC_SQL.Fetch_Rows

Description

Retrieves a row that satisfies the query at a specified cursor.

Syntax


FUNCTION EXEC_SQL.Fetch_Rows
    ([Connid     IN CONNTYPE],
      Curs_Id    IN CURSTYPE)
RETURN PLS_INTEGER;

Parameters

Parameter Description
Connid Is the handle to the connection you want to use. If you do not specify a connection, EXEC_SQL.Default_Connection retrieves the primary Oracle Developer connection handle from the cache.
Curs_Id Is the cursor handle to the SQL statement from which you want to fetch.

Returns

The number of rows actually fetched.

Usage Notes

Each EXEC_SQL.Fetch_Rows call retrieves one row into a buffer. Use EXEC_SQL.Fetch_Rows repeatedly until 0 is returned. For Oracle databases, this means there is no more data in the result set. For non-Oracle data sources, this does not mean there is no more data in the specified cursor. See EXEC_SQL.More_Results_Sets for more information.

After each EXEC_SQL.Fetch_Rows call, use EXEC_SQL.Column_Value to read each column in the fetched row.

Example


PROCEDURE getData IS
  connection_id EXEC_SQL.CONNTYPE; 
  cursorID EXEC_SQL.CURSTYPE;
  sqlstr VARCHAR2(1000);  
  loc_ename VARCHAR2(30);
  loc_eno NUMBER;			
  loc_hiredate DATE;
  nIgn PLS_INTEGER; 
  nRows PLS_INTEGER := 0;  -- used for counting the actual number of rows returned       

 ...

BEGIN
  connection_id := EXEC_SQL.OPEN_CONNECTION(connect_str);
  cursorID := EXEC_SQL.OPEN_CURSOR(connection_id);
  sqlstr := 'select ename, empno, hiredate from emp ';  
  EXEC_SQL.PARSE(connection_id, cursorID, sqlstr, exec_sql.V7);  
  EXEC_SQL.BIND_VARIABLE(connection_id, cursorID, ':bn', input_empno); 
  EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, 1, loc_ename, 30); 
  EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, 2, loc_eno);	      
  EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, 3, loc_hiredate); 
  nIgn := EXEC_SQL.EXECUTE(connection_id, cursorID); 
  --
  -- call FETCH_ROWS to obtain a row. When a row is returned, obtain the values, 
  -- and increment the count.
  --
  WHILE (EXEC_SQL.FETCH_ROWS(connection_id, cursorID) > 0 ) LOOP	
    nRows := nRows + 1;
    EXEC_SQL.COLUMN_VALUE(connection_id, cursorID, 1, loc_ename;  
    EXEC_SQL.COLUMN_VALUE(connection_id, cursorID, 2, loc_eno);	
    EXEC_SQL.COLUMN_VALUE(connection_id, cursorID, 3, loc_hiredate); 

 ...

  END LOOP;
  --
  -- The loop terminates when FETCH_ROWS returns 0. This could have happen because 
  -- the query was incorrect or because there were no more rows.  To distinguish 
  -- between these cases, we keep track of the number of rows returned.
  --
  IF (nRows <= 0) THEN
    TEXT_IO.PUT_LINE ('Warning: query returned no rows');	
  END IF;

 ...

END;