Re: Problem with Sequences in SQL Load

Use the Oracle provided sequence mechanism, at least they work. Evidently you are calling your own function, because an Oracle sequence doesn't restart when you delete a record. There is no need for that as Oracle has sequences.

Regards,

Sybrand Bakker, Oracle DBA

<billmil_at_my-deja.com> wrote in message news:918ajn$rsu$1_at_nnrp1.deja.com...
> I have a problem regarding SQL Loader.
>
> Here's the background: when we created our database, we made all our
> numerically-generated (via sequences) keys alphanumeric instead of
> numeric. The reason? Because everything in the web-world is a string,
> this eliminated us from having to worry about converting strings to
> BigDecimals in our java/jsp code.
>
> How SQL Loader factors in: I use SQL Loader to load "log files" into
> database tables. When we record user actions (example: clicking on an
> ad), we write the action to a text file (comma delimited with user_id,
> ad_id, time, etc) then later import the text file into an "ad action"
> table via SQL Loader.
>
> The problem occurs when sql loader tries to generate new key values.
> Because the keys are alphanumeric, "MAX" doesn't work. (It
> thinks "99999" exceeds "239015"). "SEQUENCE(COUNT,1)" works fine,
> unless someone deletes a few records. (example: you have 239015
> records, each keyed from 1 to 239015. If you delete record 1, sql
> loader will want to start at 239015. This causes a duplicate key).
>
> We should probabably have right-justified-zero-filled our keys if we
> wanted to store them as alphanumerics. We really can't do that
> now...in the meantime does anyone have other suggestions?
>
> thanks
>
> bill milbratz
> chicago IL usa
>
>
>
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Wed Dec 13 2000 - 18:51:02 CET