EXEC_SQL.DEFAULT_CONNECTION
built-in functionThis function returns a connection handle that uses the same database connection
originally established. EXEC_SQL.DEFAULT_CONNECTION
replaces EXEC_SQL.CURR_CONNECTION
.
FUNCTION EXEC_SQL.DEFAULT_CONNECTION
RETURN EXEC_SQL.CONNTYPE;
A handle to the primary connection.
EXEC_SQL.DEFAULT_CONNECTION
is called, the default connection is found, placed in a cache within the EXEC_SQL
package, and a handle is returned to the user. Subsequent calls to EXEC_SQL.DEFAULT_CONNECTION
simply retrieves the handle from the cache.
Since this connection handle is cached, if you are accessing data from only
the default connection, then you do not need to explicitly specify the connection
handle in calls to other EXEC_SQL
methods; EXEC_SQL
automatically looks up the cache to obtain the connection handle.
To clear the cache, call EXEC_SQL.CLOSE_CONNECTION
on the connection
handle that is obtained from calling EXEC_SQL.DEFAULT_CONNECTION
.
For default connections, EXEC_SQL.CLOSE_CONNECTION
does not terminate
the connection, but only frees up the resources used by EXEC_SQL
.
/*
** This example illustrates the use of
** EXEC_SQL.DEFAULT_CONNECTION and
** EXEC_SQL.CURR_CONNECTION.
*/
PROCEDURE esdefaultcon2 IS
connection_id EXEC_SQL.CONNTYPE;
bIsConnected BOOLEAN;
cursorID EXEC_SQL.CURSTYPE;
sqlstr VARCHAR2(1000);
nIgn PLS_INTEGER;
nRows PLS_INTEGER := 0;
nTimes PLS_INTEGER := 0;
mynum NUMBER;
BEGIN
--
-- obtain the default connection and check that it is valid
--
connection_id := EXEC_SQL.DEFAULT_CONNECTION;
bIsConnected := EXEC_SQL.IS_CONNECTED;
IF bIsConnected = FALSE THEN
TEXT_IO.PUT_LINE('No primary connection. Please connect before retrying.');
RETURN;
END IF;
--
-- subsequent calls to EXEC_SQL.OPEN_CURSOR, EXEC_SQL.PARSE, EXEC_SQL.DEFINE_COLUMN,
-- EXEC_SQL.EXECUTE, EXEC_SQL.FETCH_ROWS, EXEC_SQL.COLUMN_VALUE,
-- EXEC_SQL.CLOSE_CURSOR, EXEC_SQL.CLOSE_CONNECTION all use this connection
-- implicitly from the cache
--
cursorID := EXEC_SQL.OPEN_CURSOR;
sqlstr := 'select empno from emp';
EXEC_SQL.PARSE(cursorID, sqlstr, exec_sql.V7);
EXEC_SQL.DEFINE_COLUMN(cursorID, 1, mynum);
nIgn := EXEC_SQL.EXECUTE(cursorID);
LOOP
IF (EXEC_SQL.FETCH_ROWS(cursorID) > 0) THEN
EXEC_SQL.COLUMN_VALUE(cursorID, 1, mynum);
...
ELSE
exit;
END IF;
END LOOP;
EXEC_SQL.CLOSE_CURSOR(cursorID);
EXEC_SQL.CLOSE_CONNECTION;
END;
About the EXEC_SQL
built-in package
Copyright © 1984, 2005, Oracle. All rights reserved.