RE: fetch across commit

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Tue, 30 Nov 2004 12:46:41 -0500
Message-ID: <4001DEAF7DF9BD498B58B45051FBEA6501DFA602@25exch1.vicorpower.vicr.com>

 Jared,

        Excellent suggestion and actually the "correct" way to avoid this particular error since the cursor is forced into an invalid status on each commit. One added bonus is that it prohibits another session from messing with your data rows. Problem is that it's not intutitave when your not messing with the same table.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Jared Still [mailto:jkstill_at_gmail.com]=20 Sent: Tuesday, November 30, 2004 10:08 AM To: Goulet, Dick
Cc: Oracle List
Subject: Re: fetch across commit

Hi all,

Have any of you considered using a 'where current of' update cursor to work around this?

I've used it in the past to avoid both ora-1555 and 'fetch across commit'.

Here's a bit of code with an example, stripped to the relevant parts.

It would of course be a good idea not to commit every row as this code is doing.

=20

        cursor csr_dblink
        is
        select
                instance, username, table_owner, table_name,
                precedence, db_link_instance, db_link_username,
                rowid
        from data_sources;
=20
        cursor csr_dblink_upd ( rowid_in rowid )
        is
        select
                instance, username, table_owner, table_name,
                precedence, db_link_instance, db_link_username,
                remote_instance, remote_table_name, remote_table_owner
        from data_sources
        where rowid =3D rowid_in
        for update;
=20
        csr_dblink_upd_rec csr_dblink_upd%rowtype;
=20
        for dblinkrec in csr_dblink
        loop
=20
                if csr_dblink_upd%isopen then
                        close csr_dblink_upd;
                end if;
=20
                open csr_dblink_upd(dblinkrec.rowid);
=20
                fetch csr_dblink_upd into csr_dblink_upd_rec;
=20
                if csr_dblink_upd%notfound then
                        raise_application_error(-20100,'could not find
current record');
                end if;
=20
                update data_sources
                set
                        remote_instance =3D remote_instance_inout,
                        remote_table_name =3D remote_table_name_inout,
                        remote_table_owner =3D remote_table_owner_inout
                where current of csr_dblink_upd;
=20
                if csr_dblink_upd%isopen then
                        close csr_dblink_upd;
                end if;
=20
                commit;
=20
        end loop;

=20
Jared

On Tue, 30 Nov 2004 09:44:38 -0500, Goulet, Dick <dgoulet_at_vicr.com> wrote:
> Ganesh,

>=20
> The answer is yes and no. According to the SQL standard once
a
> commit is issues all open cursors are invalid & need reopening.
Oracle,
> being the NICE dbms that it is allows us to do otherwise. The problem
> is that the current SCN of your session is no longer the same as that
of
> your cursor and you've released all interest in rollback segments
before
> the now current scn. Although it is much more common to have the
> problem caused by a second session updating the cursor table, the
insert
> table can also be affected. Delayed Block Cleanout is one potential
> culprit, the second can become a integrity constraint check or an
index
> update or some other matter. OTS has at one time pointed me to a data
> dictionary update as the problem. Namely if you create the insert
table
> with very small extents then getting that third or fourth extent can
> cause the problem. The answer in my case was to create the insert
table
> with one very large initial extent.

>=20
>=20

--=20
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 30 2004 - 11:43:32 CST