Re:Snapshot too old error

From: <dgoulet_at_vicr.com>
Date: Mon, 16 Sep 2002 14:08:18 -0800
Message-ID: <F001.004D0C64.20020916140818@fatcity.com>

Raj,

    I would suggest the following as a way around your problems:

    Open a cursor on your table with "ORDER BY ROWNUM" at the end of the query. This forces the database to accumulate your data into a temp segment where it will remain for the duration that you need it. Since all of the data is so accumulated the rollback segments are not needed.

    Next make sure your code is CLOSING the cursor at the end. Some people assume that whenever sqlca.sqlcode > 0 that the cursor has implicitly been closed. WRONG. It is still open and a new EXEC SQL OPEN <cursor_name> only re-populates it with the original SCN as the read consistent view point hence the ORA-01555. OH, BTW, I NEVER rely on the EXEC SQL WHENEVER NOTFOUND GOTO <someplace> to handle the problem. Try something like the following:

    EXEC SQL DECLARE <cursor_name> FOR

             <SQL_statement>
               ORDER BY ROWNUM;

    if(sqlca.sqlcode != 0) <do_something>;     EXEC SQL OPEN <cursor_name>;
    if(sqlca.sqlcode != 0) <do_something_again>;     do
    { EXEC SQL FETCH <cursor_name> into :<host_variable>;

       if(sqlca.sqlcode != < 0)
         {  EXEC SQL ROLLBACK WORK;
            break;
         }
         if(sqlca.sqlcode > 0) break;
         <whatever>
      }while(sqlca.sqlcode == 0);
      EXEC SQL CLOSE <cursor_name>;

Try putting this into an independent function, then <do_something> becomes return;

Dick Goulet

____________________Reply Separator____________________
Author: "Pillai; Rajesh" <Rajesh.Pillai_at_nordstrom.com>
Date:       9/13/2002 6: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: 
  INET: dgoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Mon Sep 16 2002 - 17:08:18 CDT