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

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" <damorgan_at_x.washington.edu> wrote in message
> news:1067613108.215966_at_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.comar.no.spam_at_no.spam.btinternet.com> wrote in message
>>>news:bns3dh$ckk$2_at_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/ext/certificates/oad/oad_crs.asp
>>http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
>>damorgan_at_x.washington.edu
>>(replace 'x' with a 'u' to reply)
>>