This procedure releases any resources used by the connection handle and invalidates it.
PROCEDURE EXEC_SQL.Close_Connection
([Connid IN OUT CONNTYPE]);
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. |
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.
/*
** 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;