Re: Snapshot too old error

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Fri, 13 Sep 2002 20:03:18 -0800
Message-ID: <F001.004D0129.20020913200318@fatcity.com>

You can't. It is not an option -- Oracle will not allow inconsistent read under any conditions. Be grateful; this is a major plus...

My advice is to make your cursor resilient to ORA-01555. If the cursor fetch returns ORA-01555, then catch it, close and re-open the cursor, and re-fetch. Of course, this means that your cursor must be "re-entrant", that it will pick up exactly where it left off. Algorithm would look something like this:

    loop

        open cursor;
        loop
            fetch cursor into ....;
            if NO_DATA_FOUND then
                   exit both loops;
            elsif SNAPSHOT_TOO_OLD then
                    exit inner loop only;
            elsif OTHERS then
                    raise error condition and exit program;
            end if;
            do work;
        end loop
        close cursor;

    end loop;

Hope this helps...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, September 13, 2002 8:43 PM

Hi All,

   I am running a pro*c program which has a cursor which fetches data from a table which is constantly being updated by front-end, so I am getting "snapshot too old error". My requirement is not to adhere to the consistency of the data. I am fine if the data that my program is reading is not consistent. How can I ask oracle to not to give me this "snapshot too old" error even when the data is consistent/inconsistent.

Thanks a lot,
Rajesh

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Pillai, Rajesh
  INET: Rajesh.Pillai_at_nordstrom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Fri Sep 13 2002 - 23:03:18 CDT