Application design - connections

We have a web application that performs all database operations via stored procedures and the big_user approach. So, when someone clicks on the 'List Products' button, a java class grabs a db connection, calls the corresponding store procedure and finally closes the connection. All the application was designed that way.

And here is when doubts arises...

1- With this approach, every time a user performs some action against the db, a db connection is opened and closed. Is it a good practice ? What are the caveats ?

2- Or..it'd be better to have only one connection opened, when user logs in, and keep it
until it logs out ? (no matter if users closes their browsers or session expires).

3- With the first approach, If I want to implement secure app roles, do I have to call the procedure wich enables the role before any operation, after getting the connection ? (I'd also have a logon trigger that sets a context.)

Sorry for my english, I hope you can understand my questions.

(I am running a 9i database on a Win 2003 server)

Thanks.

Regards. Received on Mon Feb 20 2006 - 20:21:52 CST