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.  

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

Jared

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

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l