Re: Problem with Sequences in SQL Load

Bill,

[Quoted] [Quoted] Why don't you just write your user activity directly to the database from the web app? Set up a trigger to fire on inserts that gets the nexval from the sequence and you are done. It seems like a lot of work to go from web app to flat file to sql loader to table? What is [Quoted] [Quoted] the limiting factor here? I am also unclear as to why you would have chosen to define your uniquely genereated id's as alpha. I can't really think of a reason not to define the value as numeric. If you [Quoted] [Quoted] have to process a flat file, another approach may be to write a procedure to load it. I would use a sequence in this case as well.

Hope this helps

lol

Chris

On Wed, 13 Dec 2000 17:11:26 GMT, billmil_at_my-deja.com wrote:

>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 - 23:03:59 CET