Re: Moving data from DB2 to Oracle with Row Num

On 20 Aug 2002 09:42:38 -0700, carolek_at_ix.netcom.com (Carole Kaufman) 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.
>
>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.
>
>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.
>
>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?
>
>Carole
>
>Galen Boyer <galenboyer_at_hotpop.com> wrote in message news:<uvg66avkr.fsf_at_hotpop.com>...
>> On 19 Aug 2002, Carolek_at_ix.netcom.com wrote:
>> > We have an application which uses Row Nums.
>>
>> In DB2, what's a Row Num?
>>
>> > We've been unsuccessful at moving the data from DB2/OS390 and loading
>> > the data into Oracle 9i via SQL Loader, but we can't get it to load
>> > into Oracle with row num recognition.
>>
>> Have you been successful at loading the data, but unsuccessful at Row
>> Num recognition?

If all you need is a number that keeps increasing as more data is loaded into the table, and you really do have a column to hold that number, then you can use the SEQUENCE keyword to tell SQL*Loader to load ascending values into that column. I use it with the MAX option so that it looks at the max value already in the column, and starts counting up from there.

http://tahiti.oracle.com/pls/db92/db92.drilldown?levelnum=2&toplevel=a96652&method=LIKE&chapters=0&book=&wildcards=1&preference=&expand_all=&verb=&word=sequence#a96652

John Received on Tue Aug 20 2002 - 23:00:11 CDT