Returns a connection handle that uses the same database connection originally established by Oracle Developer. EXEC_SQL.Default_Connection replaces EXEC_SQL.Curr_Connection.
FUNCTION EXEC_SQL.Default_Connection
RETURN EXEC_SQL.ConnType;
The default connection is the primary Oracle Developer 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.
/*
** 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;