EXEC_SQL.IS_OPEN
built-in functionThis fuction returns TRUE if a specified cursor is currently open on a specified connection.
FUNCTION EXEC_SQL.IS_OPEN
([connid IN CONNTYPE],
curs_id IN CURSTYPE)
RETURN BOOLEAN;
Parameter |
Description |
|
Is the handle to the connection you want to use. If you
do not specify a connection, |
|
Is the cursor handle you want to determine if it is open. |
TRUE
or FALSE
/*
** 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;
About the EXEC_SQL
built-in package
Copyright © 1984, 2005, Oracle. All rights reserved.