Re: Last Row Inserted

at\"@ <""caoimhinocrosbai(at\"@)yahoo.com"> wrote in news:1125674464.e8e339d34ebc4394521c61470c53cc34_at_teranews:

> 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
>

If the aim is to replicate t1 to t2, how to do plan to handle updates and deletes? Is table 2 read only or can it be updated as well? If t2 can be updated, do you want bidirectional replication?

Oracle has processes already for what you want to do. I would suggest using those instead of reinventing the wheel. Read the manual on advanced repliciatio, and data guard. You can probably figure out what you want to use in less than a day and what you will learn will be well worth the time invested. Received on Fri Sep 02 2005 - 09:34:48 CDT