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

EXEC_SQL.CLOSE_CURSOR built-in procedure

This procedure closes a specified cursor and releases the memory allocated to it.

Syntax


PROCEDURE EXEC_SQL.CLOSE_CURSOR
([Connid IN CONNTYPE],
Curs_Id IN OUT CURSTYPE);

Parameter

Mode

Description

Connid

IN

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.

Connid

IN

OUT

Is the cursor handle you want to close.

Sets to NULL.

Usage notes

When you no longer need a cursor, you must close it. Otherwise, you may not be able to open new cursors.

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