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

EXEC_SQL.Is_Open

Description

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;

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