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

EXEC_SQL.More_Result_Sets

Description

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;

Parameters

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.

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;