Re: Control for "Isolation level" at SELECT statement level

Yong Huang wrote:

> Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3F40F63C.D251AA92_at_exxesolutions.com>...
> > Hillel Eilat wrote:
> >
> > > Hi folks.
> > >
> > > Some RDBMS products support the following syntax format:
> > >
> > > SELECT .... <whatever...> WITH {READCOMMITTED | REPEATABLEREAD | ... |
> > > SERIALIZABLE}
> > >
> > > The WITH clause provides the means for overriding the default isolation
> > > level (or the one
> > > specified upon SET TRANSACTION) at a specific SELECT statement.
> > >
> > > WITH clause is applicable for INSERT,UPDATE,DELETE as well.
> > >
> > > Is there an equvalent to the "WITH" clause in Oracle's SQL?
> > >
> > > Your answers will be appreciated.
> > >
> > > Hillel.
> >
> > No. But I am fascinated on how one session can allow dirty reads to other
> > sessions that don't.
> >
> > Seems like a capability searching for a reason to exist. And for which I
> > can find none.
>
> Hi, Daniel,
>
> If you need to notify another session what your current session has
> done but your current session has not committed, consider DBMS_PIPE.
> That's about the only way you can achieve "dirty read", but not in the
> sense of a simple SELECT statement. Of course, a more awkward way is
> to use UTL_FILE to write something to an external file and let the
> other session read it.
>
> Yong Huang

I have no need. I was trying to figure out how anyone could implement a dirty-read in one session without simultaneously having it in all.

But I think inter-session notification is better done with DBMS_ALERT than DBMS_PIPE.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)