Insert into remote database give wrong sql%rowcount

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 5 Apr 2001 20:42:18 +0100
Message-ID: <986499535.7301.0.nnrp-08.9e984b29@news.demon.co.uk>

Has anyone come across this before.
Oracle 8.1.6.0 on NT 4 sp5

{select statement} is:

    select /*+ rowid (ss) */

        lg.*
    from

        session_table ss,
        log_table lg
    where
        ss.rowid between r1 and r2

    and lg.s_id = ss.id

The statement conforms to the requirements of deleting from a join view - where log_table is the child table, and session_table is the parent, with a primary key of id.

The problem - extracted from the PL/SQL wrapper that it runs under:

select count(*) from (select statement)

    Count = 15,297

insert into log_backup_at_backup_db
select * from (select statement)

    sql%rowcount = 35 --- oh dear !!

delete from (select statement)

    sql%rowcount = 15,297

select count(*) from log_backup_at_backup_db

    count = 15,297

Nothing relevant seems to show up on metalink.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html

Received on Thu Apr 05 2001 - 14:42:18 CDT