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

EXEC_SQL.Close_Connection

Description

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

Syntax


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

Parameters

Name 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 Oracle Developer 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;
  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;