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

EXEC_SQL.IS_CONNECTED built-in function

This function returns TRUE if a specified connection handle is currently connected to a data source.

Syntax


FUNCTION EXEC_SQL.IS_CONNECTED
[connid IN CONNTYPE]
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.

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

Built-in packages examples