Author: Mark Fatkulin

Page: 1 2

We mostly encounter a scenario where we have to re-sequence a numbered column. Consider a schema for a departmental store with its own stock room. Each shelf in the store is represented as a location and each location can store more than one items.

Our store will experience the normal functions such as items will be added and removed frequently. Whenever an item is added then value in the location sequence will increment by one and whenever an item is removed then we will make its quantity as zero.

Create Table:

Suppose below table holds the data of the items. The business key to below table will be a unique index on MYLOC_ID + MYLOC_SEQ column combination.

CREATE TABLE MYSTORE
(
  MYSTOCK_ID NUMBER(9) NOT NULL,
  MYLOC_ID VARCHAR2(10) NOT NULL,
  MYLOC_SEQ NUMBER(3) NOT NULL,
  ITEM_NUM VARCHAR2(10) NOT NULL,
  QUANTITY NUMBER(10) DEFAULT 0 NOT NULL,
  QUANTITY_MAX NUMBER(10) DEFAULT 0 NOT NULL,
  CONSTRAINT PK_MYSTORE PRIMARY KEY(MYSTOCK_ID)
)
/

Analyze Records:

As our store will experience frequent addition and removal of elements so we may reach a case where we reach the limit and no more items can be stored in the table. That's what happens here.

MYSTOCK_ID       STORE_LOCN       MYLOC_SEQ
———————–   ———————     ———————
        1                         LOC1000001                 5
        4                         LOC1000001                 11
        2                         LOC1000001                 18
        6                         LOC1000001                 35
        3                         LOC1000001                 99
        5                         LOC1000001                 245
        7                         LOC1000001                 803
        8                         LOC1000001                 922
        9                         LOC1000001                 945
        10                       LOC1000001                 999
        11                       LOC1000002                 1
        12                       LOC1000002                 2
        13                       LOC1000002                 15
        14                       LOC1000002                 411
        15                       LOC1000002                 999
        16                       LOC1000003                 1
        17                       LOC1000004                 1
        18                       LOC1000004                 3
        19                       LOC1000004                 5
        20                       LOC1000005                 999

The frequent movement of items has made us reach the maximum limit (999) of MYLOC_SEQ for a particular MYLOC_ID. Now if we want to add another record into table then it will result in ORA-1401 error (ORA-01401: inserted value too large for column). This is because we tried to insert a value into a column that exceeds the maximum width for the column.

Re-sequencing Values:

In order to solve this problem we will re-sequence the values. Now if we add another record in our table then with the increment in MYLOC_ID column our value of MYLOC_SEQ column will be re-sequenced and the gaps due to deletion of records will be re-used.

Oracle Analytic Functions:

Oracle has provided us with a rich collection of analytical function that can be very useful for development as well as troubleshooting purposes. Oracle analytic functions can be very much helpful in re-sequencing a numbered column. For our purpose we will use the row_number() and the partition clauses of the analytic functions. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Step by Step Guide to Oracle Parsing
   Beware of Oracle Outage with database growth!!
   Beware of major Oracle Failures: Are you Safe??
   Oracle Guide: Recovering accidentally dropped tables!!