This 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 |
---|---|
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. |
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.
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;