EXEC_SQL.MORE_RESULT_SETS
built-in functionThis function applies to non-Oracle connections only. It determines if there is another result set to retrieve for a specified cursor.
FUNCTION EXEC_SQL.MORE_RESULT_SETS
([connid IN CONNTYPE],
curs_id IN CURSTYPE)
RETURN BOOLEAN;
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. |
TRUE
or FALSE
FALSE
.
If a non-Oracle stored procedure has another result set to retrieve, the function
initializes the result set and returns TRUE. Use EXEC_SQL.DESCRIBE_COLUMN
to obtain information about the new result set and EXEC_SQL.FETCH_ROWS
to retrieve the data, if required.
PROCEDURE esmoreresultsets(sqlstr VARCHAR2) IS
conidODBC EXEC_SQL.CONNTYPE;
nRes PLS_INTEGER;
nRows PLS_INTEGER := 0 ;
curID EXEC_SQL.CURSTYPE;
BEGIN
--
-- an ODBC connection string; usually has the form 'username/password@ODBD:dbname'
--
conidODBC := EXEC_SQL.OPEN_CONNECTION('connection_str_ODBC');
curID := EXEC_SQL.OPEN_CURSOR(conidODBC);
EXEC_SQL.PARSE(conidODBC, curID, sqlstr, exec_sql.v7);
nRes := EXEC_SQL.EXECUTE(conidODBC, curID);
--
-- obtain results from first query in sqlstr
WHILE (EXEC_SQL.FETCH_ROWS(conidODBC, curID) > 0) LOOP
nRows := nRows + 1;
...
END LOOP;
--
-- for some non-Oracle databases, sqlstr may contain a batch of queries;
-- MORE_RESULT_SETS checks for additional result sets
--
IF (EXEC_SQL.MORE_RESULT_SETS(conidODBC, curID)) THEN
TEXT_IO.PUT_LINE(' more result sets ');
ELSE
TEXT_IO.PUT_LINE(' no more result sets ');
END IF;
...
EXEC_SQL.CLOSE_CONNECTION(conidODBC);
END;
About the EXEC_SQL
built-in package
Copyright © 1984, 2005, Oracle. All rights reserved.