EXEC_SQL.FETCH_ROWS
built-in functionThis function retrieves a row that satisfies the query at a specified cursor.
FUNCTION EXEC_SQL.FETCH_ROWS
([connid IN CONNTYPE],
curs_id IN CURSTYPE)
RETURN PLS_INTEGER;
Parameter |
Description |
|
Is the handle to the connection you want to use. If you
do not specify a connection, |
|
Is the cursor handle to the SQL statement from which you want to fetch. |
The number of rows actually fetched.
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.
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;
About the EXEC_SQL
built-in package
Copyright © 1984, 2005, Oracle. All rights reserved.