Originally posted by swapnil paranjape:
please can you explain the side effects in that ?i really want to know,do the closing of the connection and returing it to pool has some sideeffetcs?
For example, the Oracle "ALTER SESSION" statements can:
change your current schemachange the transactionality of your connection from serializable to read committed or vice versachange the default date format, which therefore changes the allowable format for implicit string to date conversionschange your sort order With the change of the default date format, the SQL statement:
would work with a connection that had been created directly, but if a connection retrieved from a pool had had ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd'; applied to it before being returned, then the SQL statement will fail. This type of problem can be excruciatingly hard to debug because the error will be intermittent and the code
unit will always work correctly in isolation.
Using one type of pooler or the other doesn't really solve the problem; the issue of side-effects can occur with either style. Howver, a developer who is reminded that the code is targeted at a pooled environment is going to more likely remember that statements that alter an aspect of the connection will need to be undone when the alteration is no longer needed; totally hiding the possibility of pooling, by using close() to return the connection to the pool, hides that reminder.
[ May 04, 2006: Message edited by: stu derby ]