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

EXEC_SQL.DEFAULT_CONNECTION built-in function

This function returns a connection handle that uses the same database connection originally established. EXEC_SQL.DEFAULT_CONNECTION replaces EXEC_SQL.CURR_CONNECTION.

Syntax


FUNCTION EXEC_SQL.DEFAULT_CONNECTION
RETURN EXEC_SQL.CONNTYPE;

Returns

A handle to the primary connection.

Usage notes

The default connection is the primary connection. The first time 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.

Example


/* 
** 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;

See also

About the EXEC_SQL built-in package

EXEC_SQL built-in package