Re: Moving data from DB2 to Oracle with Row Num

On 20 Aug 2002, carolek_at_ix.netcom.com wrote:
> Yes, we can load data fine - but row nums aren't recognized.
>
> To explain our pain, we have this application whose 'brain' is based
> on a couple dozen tables which do not have primary keys nor any
> indexes. Frankly, I've been sheltered and have never dealt with
> tables without PKs for determining updates/inserts.

I've never worked on one.

> Somehow the database keeps track of what is a new record and what is
> an update. On DB2 it works great: the tables know when to update and
> when to insert a new record.

I don't get this.

> When we load the data via SQL Loader to Oracle, Oracle starts over at
> 1. This creates interesting behavior in the application as another
> record has 1. When we apply a unique index, we then get a unique
> index violation - afterall there's already a record 1.

Then, in your loading of the data, you must be asking Oracle to create a new rownum for you. Stop doing that. Just load the data that comes from the DB2 tables.

Oracle doesn't just start adding rownums to the data unless you ask it.

> The vendor says we have to re-enter the data *by hand* ... well - it's
> a lot! I didn't join IT to do data entry, and I have to believe
> there's a way to get the row intelligence into Oracle...Isn't there?

Row intelligence? I really don't understand.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.