Re: oracle JDBC - how to detect uncommitted work on an open Connection?

[Edoardo]

thanks guys,
I was hoping to be able to get the info from some method on oracle's own
jdbc implementation classes,
as both suggestion to use these view imply another trip to the db from
the application - just to know if there was uncommitted work.

thanks,
Edo

mcstock wrote:

exception handled:

'or by a user that has privileges granted by SYS'

yes, there are risks, which i should have mentioned, but it is sometimes
necessary to do things in SYS -- just like it's sometimes necessary to
actually edit the windows registry

not all users have privileges on the V_$ views, which are owned by SYS --
so, how do you grant selective privileges on V$ stuff? grant
SELECT_CATALOG_ROLE and let the user have access to all V$'s and DBA views?

so, for a more carefully advised approach (assuming there isn't some other
mechanism to accomplish the task, i.e., that i'm not overlooking some
normally available stat, v$, or function)

[_] create a user name something like SYS_EXTENSIONS with basic privileges
(create session, create view, create public synonym)
[_] connect as SYS
[_] grant select on v_$session to sys_extensions;
[_] grant select on v_$transaction to sys_extensions;
[_] connect as SYS_EXTENSIONS
[_] create the view based on the sys V_$ views
[_] create a public synonym for the view
[_] grant select on the view to public

-- mcs

"Daniel Morgan" <da******@x.washington.eduwrote in message
news:1067613108.215966@yasure...

>>mcstock wrote:

>>>i can't think of any default way to find out about open transactions, but
you could create a view (either owned by SYS or by a user that has
privileges granted by SYS like this:

CREATE OR REPLACE VIEW MY_TRANSACTION ( SID,
START_SCNB, START_SCNW ) AS select
s.sid
, t.start_scnb
, t.start_scnw
from
v$session s
, v$transaction t
where
s.saddr = t.ses_addr
and
s.AUDSID = USERENV('SESSIONID')

then create a public synonym and grant select to public -- selecting from
the view will then show if there is an open transaction.

-- mcs

"Edoardo" <e.*************@no.spam.btinternet.comwrote in message
news:bn**********@hercules.btinternet.com...

Hi

is there a way, using Oracle's JDBC drivers to find out if a given
java.sql.Connection has uncommitted work pending on it ???

the reason is, close() will commit work if there is any - while if the
close happens as a cleanup action after an error, it would be desirable
to rollback (but I don't want to rollback always before close).

TIA.
Edoardo Comar
www.choreology.com

I take exception to any advice telling someone to create an object as SYS.

If all someone wants is open transactions there are a boatload of V_$
magic views that
provide all of that information.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)