Re: Oracle PL/SQL giving null values a numeric value in a stored proceedure

Your description is unclear. I put some questions inline:

PParker_at_ins.gte.com (Patrick Parker) wrote in message news:<e6e8dd72.0201230726.4bb64ce2_at_posting.google.com>...
> I am hoping I can get someone to give me some insite on a stored
> proceedure I am writing.
> I have a table that was created by the all knowing contractors within
> an Oracle database in an Arbor Billing System.
> The table was created and a primary key was set for a numbering scheme
> from aproximatly 32,000 to -32,000 except that the numbers are only
> created when a row is created in the table.
> The problem I have is that there are different number ranges being
> used randomly for row entries and null values in between.

DO you really mean NO ROWS in between?
>
> I am tring to create a stored proceedure to assighn numeric values to
> the random Null values in the sequence of the table.

Are you assigning to the Primary key or to another column?
>
> I have had a couple of Ideas one of which was to create another table
> with all of the numbering sequence in tact and have and inner join
> pull the values from that table where the value IS NULL for the table
> in question.

My best guess is that you have a table A with a numberic primary key K with a constraint to values from -32K to +32K and that not all the entries are sequencial.

The first question is why do you care that there are gaps?

Then your proposed solution sounds a lot like fixing the symptom and not the root problem. If there really should not be gaps, shouldn't you fix the application that's loading the table?

I'm guessing that the application that loads the rows is using a SEQUENCE. The gaps are due to the way ORACLE caches sequence values for each session. Assuming that this table is a processing queue, the procedure that reads it should just SELECT the next one, whether the next one is the current key value+1 or key value+17.

If what I'm suggesting seems wrong, please just supply more details. I could have made wrong assumptions. (HINT a sample of the code and results would help a lot.)

  HTH
   Ed Prochak Received on Wed Jan 23 2002 - 11:52:21 CST