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

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 Received on Tue Aug 19 2003 - 09:37:37 CDT