Sequential Numbering of Records

If you want to sequentially number the records in a table, putting the numbers in a field of the table, you can use the rownum pseudocolumn, doing something like "update inv_recs set dline = rownum;".  However, you can't guarantee that the records will get the same number as the order in which they were entered into the table.  Also, you can't use the "order by" clause in an update statement.   To solve this, you can put an unnamed PL/SQL block into your SQL script to do the numbering, using cursors and for loops and other looping constructs that are available in PL/SQL.

For example, I have a table (inv_hdrs) of invoice numbers and a table (inv_recs) of invoice detail lines associated with those invoices.  I wanted to number (dline) the detail lines from 1 through N for each invoice, ordered by a sequence number field (seq_num) which may have gaps in the numbering or may be repeated or may be null.  I also wanted to exclude from the numbering any detail line with a zero amount, as well as the entire invoice if it had a zero total amount.  The following snippet of PL/SQL was used to do this in my SQL script (be sure to include the "/" after the block to execute it; otherwise, it just gets parsed but not run):

declare
     cursor hdr_cur is select invh_code from inv_hdrs where total != 0.00;
     cursor rec_cur(code_in varchar2) is select rowid row_id from inv_recs
          where invh_code = code_in and amt != 0.00 order by nvl(seq_num,0);
     i integer;
begin
     for hdr_rec in hdr_cur
     loop
          i := 0;
          for rec_rec in rec_cur(hdr_rec.invh_code)
          loop
               i := i + 1;
               update inv_recs set dline = i where rowid = rec_rec.row_id;
          end loop;
     end loop;
end;
/

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.