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

EXEC_SQL.MORE_RESULT_SETS built-in function

This function applies to non-Oracle connections only. It determines if there is another result set to retrieve for a specified cursor.

Syntax


FUNCTION EXEC_SQL.MORE_RESULT_SETS
([connid IN CONNTYPE],
curs_id IN CURSTYPE)
RETURN BOOLEAN;

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 connection handle from the cache.

curs_id

Is the cursor handle to the SQL statement from which you want to fetch.

Returns

TRUE or FALSE

Usage notes

If used against Oracle databases, the function always returns 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.

Example


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;

See also

About the EXEC_SQL built-in package

EXEC_SQL built-in package