EXEC_SQL.LAST_ROW_COUNT
built-in functionThis function returns the cumulative number of rows fetched.
FUNCTION EXEC_SQL.LAST_ROW_COUNT
[connid IN CONNTYPE]
RETURN PLS_INTEGER;
Parameter |
Description |
|
Is the handle to the connection you want to use. If you
do not specify a connection, |
EXEC_SQL.FETCH_ROWS
or EXEC_SQL.EXECUTE_AND_FETCH
.
The function returns a zero when used after an EXEC_SQL.Execute call
.
PROCEDURE eslastrowcount is
connection_id EXEC_SQL.CONNTYPE;
cursorID EXEC_SQL.CURSTYPE;
sqlstr VARCHAR2(1000);
nIgn PLS_INTEGER;
nRows PLS_INTEGER := 0 ;
mynum NUMBER;
BEGIN
connection_id := EXEC_SQL.OPEN_CONNECTION('connection_str');
cursorID := EXEC_SQL.OPEN_CURSOR(connection_id);
--
-- in this query, we order the results explicitly
--
sqlstr := 'select empno from emp order by empno';
EXEC_SQL.PARSE(connection_id, cursorID, sqlstr, exec_sql.V7);
EXEC_SQL.DEFINE_COLUMN(connection_id, cursorID, 1, mynum);
nIgn := EXEC_SQL.EXECUTE(connection_id, cursorID);
LOOP
nIgn := EXEC_SQL.FETCH_ROWS(connection_id, cursorID);
--
-- do whatever processing is desired
--
IF (nIgn > 0) THEN
EXEC_SQL.COLUMN_VALUE(connection_id, cursorID, 1, mynum);
...
END IF;
nRows := EXEC_SQL.LAST_ROW_COUNT(connection_id);
--
-- In this example, we are only interested in the first 10 rows, and exit after
-- fetching them
--
IF (nRows > 10) THEN
EXIT;
END IF;
END LOOP;
EXEC_SQL.CLOSE_CURSOR(connection_id, cursorID);
EXEC_SQL.CLOSE_CONNECTION(connection_id);
END;
About the EXEC_SQL
built-in package
Built-in packages examples
Copyright © 1984, 2005, Oracle. All rights reserved.