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

EXEC_SQL.IS_OPEN built-in function

This fuction returns TRUE if a specified cursor is currently open on a specified connection.

Syntax


FUNCTION EXEC_SQL.IS_OPEN
([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 you want to determine if it is open.

Returns

TRUE or FALSE

Example


/* 
** This example illustrates the use of EXEC_SQL.IS_OPEN, 
** EXEC_SQL.CLOSE_CURSOR, EXEC_SQL.IS_CONNECTED and 
** EXEC_SQL.CLOSE_CONNECTION. 
*/
PROCEDURE esclosecursor.pld IS
  connection_id EXEC_SQL.CONNTYPE; 
  bIsConnected BOOLEAN;
  cr1 EXEC_SQL.CURSTYPE;
  sqlstr1 VARCHAR2(200);
  sqlstr2 VARCHAR2(200);
  nRes PLS_INTEGER;
 bOpen BOOLEAN;
 nRows PLS_INTEGER;
 loc_ename VARCHAR2(30);
 loc_eno NUMBER;
 loc_hiredate DATE;
BEGIN


BEGIN
 connection_id := EXEC_SQL.OPEN_CONNECTION('connection_str');
 EXCEPTION
  WHEN EXEC_SQL.PACKAGE_ERROR THEN
  TEXT_IO.PUT_LINE(' connection open failed ');
END;
--
-- confirm that connection is valid
--
  bIsConnected := EXEC_SQL.IS_CONNECTED(connection_id);
IF bIsConnected = FALSE THEN
 TEXT_IO.PUT_LINE('No present connection to any data source. Please connect before retrying.');
 RETURN;
END IF;
--
-- open a cursor and do an update
--
cr1 := EXEC_SQL.OPEN_CURSOR(connection_id);
 sqlstr1 := 'update emp set empno = 3600 where empno = 7839';
 EXEC_SQL.PARSE(connection_id, cr1, sqlstr1, exec_sql.V7);

nRes := EXEC_SQL.EXECUTE(connection_id, cr1);
--
-- reuse the same cursor, if open, to do another query.
--
sqlstr2 := 'select ename, empno, hiredate from emp ';
--
-- use IS_OPEN to check the state of the cursor
--
IS (EXEC_SQL.IS_OPEN(connection_id, cr1) != TRUE) THEN 
 TEXT_IO.PUT_LINE('Cursor no longer available ');
 RETURN;
END IF;
--
-- associate the cursor with another statement, and proceed to do the query.
--
 EXEC_SQL.PARSE(connection_id, cr1, sqlstr2, exec_sql.V7);
 EXEC_SQL.DEFINE_COLUMN(connection_id, cr1, 1, loc_ename, 30);
 EXEC_SQL.DEFINE_COLUMN(connection_id, cr1, 2, loc_eno);
 EXEC_SQL.DEFINE_COLUMN(connection_id, cr1, 3, loc_hiredate); 
 nIgn := EXEC_SQL.EXECUTE(connection_id, cr1);
  WHILE (EXEC_SQL.FETCH_ROWS(connection_id, cursorID) > 0 )
 LOOP
  nRows := nRows + 1;
  EXEC_SQL.COLUMN_VALUE(connection_id, cr1, 1, loc_ename);
  EXEC_SQL.COLUMN_VALUE(connection_id, cr1, 2, loc_eno); 
  EXEC_SQL.COLUMN_VALUE(connection_id, cr1, 3, loc_hiredate);
...
END LOOP;
-- 
-- close the cursor and connection to free up resources
--
 EXEC_SQL.CLOSE_CURSOR(connection_id, cr1); 
 EXEC_SQL.CLOSE_CONNECTION(connection_id);
END;

See also

About the EXEC_SQL built-in package

EXEC_SQL built-in package