| Using Packages with Connection Pools. [message #77046] |
Thu, 22 July 2004 05:55  |
Bob1200
Messages: 11
Registered: October 2001
|
Junior Member |
|
|
We have a 3 tier environment:
- Web browser
- IBM Websphere Application Server V4 using database Connection pool
- Oracle 8i Database
Are there any issues with using Oracle PL/SQL packages in conjunction with connection pools?
|
|
|
|
| Re: Using Packages with Connection Pools. [message #77049 is a reply to message #77046] |
Thu, 22 July 2004 14:24  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
You'd need to determine what the characteristics of Websphere's connection pool. In mod_plsql (Oracle connection from Apache/9iAS etc), package state is reset at the end of the call to the database. If the call from your app looks like "my_pkg.my_proc;" then mod_plsql will execute something like this on the database:
begin
my_pkg.my_proc;
DBMS_SESSION.reset_package;
end;
So - package state (pkg global variables) is reset. Things which aren't reset are other settings like NLS_LANG, SQL_TRACE, NLS_DATE_FORMAT etc which may have been changed in one of the pooled connections by an earlier transaction. If you see inconsistent behavior between web requests it could be that these settings may have been changed on some pooled connections by previous transactions using those connections. On 8i you could also experience the problem where one transaction opens a DB link one connection#1 and then a subsequent transaction tried to execute an autonomous transaction. Autonomous transactions and open DB links clash. If so, you would need to run transactions like this:
begin
my_pkg.my_proc;
DBMS_SESSION.reset_package;
commit;
FOR x IN (SELECT *
FROM SYS.v_$dblink) -- access to SYS.v_$dblink must be granted by DBA.
LOOP
EXECUTE IMMEDIATE 'alter session close database link ' || x.db_link || '';
END LOOP;
end; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
