Home » Fusion Middleware & Colab Suite » Weblogic & Application Server » Using Packages with Connection Pools.
Using Packages with Connection Pools. [message #77046] Thu, 22 July 2004 05:55 Go to next message
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 Go to previous message
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;
Previous Topic: Help @ MarshallingXAException !
Next Topic: upload file using psp
Goto Forum:
  


Current Time: Tue May 17 21:29:09 CDT 2022