The following update forms the basis of a DIY connection pool. It is intended to allocate an unused session to whoever requests it:
declare sess connection_pool.session_name%type; begin update connection_pool set in_use = 'Y', last_used = systimestamp where rowid in ( select rowid from ( select rowid from connection_pool where in_use = 'N' order by last_used ) where rownum = 1 ) returning session_name into sess; dbms_output.put_line ( sess ); end; /
There’s a serious flaw with this approach however – can you spot it?
Answers in the comments!