From: PParker@ins.gte.com (Patrick Parker) Newsgroups: comp.databases.oracle.misc Subject: creating numbers from null values withing a set range already being used Date: 12 Dec 2001 14:11:22 -0800 Organization: http://groups.google.com/ Lines: 27 Message-ID: NNTP-Posting-Host: 207.175.126.241 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1008195083 5311 127.0.0.1 (12 Dec 2001 22:11:23 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 12 Dec 2001 22:11:23 GMT I am sure this has an easy solution but, Being a bonehead I am having difficulty with this. I work with a billing system that has a primary key that ranges between 32,000 thru -32,000 roughly, The contractors in their infinite wisdom decided to pick numbers randomly from this range to use the primary key for the initial Description table and foreign key for a number of other tables. I have been trying to write a stored procedure to find the null values and then give it the sequential number in the series of numbers and return the new set of numbers so that they can be used when new products are added to the database. I have been working on a stored procedure which uses a couple of variables set as v_max_value :=32,000, v_min_value, v_count, v_new_value the cursor I am using is select Desc_no /*the Primary key*/ from the table the procedure starts at the v_max_value and a checks if the value is not Null then v_count = desc_code If the value is null and count greater than 0 then v_new_value = v_count - 1, My question is has anyone else had this type of problem? and do you have an answer,logic, or stored procedure that you could share with me?