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

EXEC_SQL.CLOSE_CONNECTION built-in procedure

This procedure releases any resources used by the connection handle and invalidates it.

Syntax

PROCEDURE EXEC_SQL.CLOSE_CONNECTION
([Connid IN OUT CONNTYPE]);

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.

 

OUT

Sets the handle to NULL. All memory allocated to the handle is also released.

Usage notes

If the connection is opened by EXEC_SQL.OPEN_CONNECTION, EXEC_SQL.CLOSE_CONNECTION also closes the database connection. If it is opened by EXEC_SQL.DEFAULT_CONNECTION, EXEC_SQL.CLOSE_CONNECTION does not close the database connection.

It is important to close the connection when you do not need it. If you do not close the connection, the database connection remains open and any memory allocated to the connection, including opened cursors, remain in use. This may result in connection deadlocks.

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;
    nIgn PLS_INTEGER;
   bOpen BOOLEAN; 
    nRows PLS_INTEGER := 0;
    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
 --
  IF (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, crl) > 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