Problem with Sequences in SQL Load

[Quoted] [Quoted] 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:11:26 CET