Re: Last Row Inserted

at"@ wrote:
> Hi,
>
> I'm using Oracle 10G on 64 bit Redhat Linux.
>
> I need to perform a bulk insert:
> create table table1 (
> pk1 number primary key,
> value1 number
> );
>
> create table table2 (
> pk2 number primary key,
> value2 number
> );
>
> create sequence seq1 start with 1;
> create sequence seq2 start with 1;
>
> insert into table1 (pk1, value1)
> select seq1.nextval, object_id
> from user_objects;
>
> Next I want to insert these values into table2, somehow returning the
> last pk1 value:
>
> insert into table2 (pk2, value2)
> select seq2.nextval, value1
> from table1
> where pk1 > v_last_known_pk1_value
> returning max(pk1) into v_max_pk1_value;
>
> The aim is to replicate new data from table1 in table2, using a sequence
> specific to table2. Many other process write to table2 using seq2.
>
> Does anybody know of any efficient/elegant way to do this, I could use a
> cursor and record the pk1 value, setting v_max_pk1_value on each
> iteration, but I believe (correct me) that this will not be as efficient
> as a bulk insert.
>
> I have not used Oracle Replication before, nor do I know enough about it
> to know if it could solve my problem of replicating table1's data while
> allowing other processes to write to table2. Our Sysdba told me he has
> not installed it as a feature but can if I request it.
>
> Many Thanks,
>
> Kevin

In a single schema?
In a single database?
Across databases?
For disaster recovery?
For a reporting data mart?
Just to waste disk space? ;-)

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)