-4

populating number in a sequence for a column without altering table structure--need a query

The Impaler
  • 45,731
  • 9
  • 39
  • 76
Pavan Lion
  • 1
  • You need to try to do it yourself, show some of your work, and be specific about where you are stuck. SO isn't a free code writing service. See https://stackoverflow.com/help/how-to-ask – Aaron Dietz Jul 22 '18 at 17:43

1 Answers1

0

if you don't want to change or alter the database table structure, you have limited solutions actually.

First option might be using a sequence object

create sequence mySequence increment by 1;

After you have a sequence object, you can read from this sequence whenever you need this field value

select mySequence.nextval from dummy;

Unfortunately, each read will increment the current value of the sequence. This means if you read but did not insert the value into the table, next time you insert a new row there will be a gap in your table column. Or if you insert two rows from two different executions, one might read the sequence first but insert value later than the other thread; then the sequence will be like 11,12,14,13,15

Second option is creating a database table trigger AFTER INSERT statement. Then you can the max value from table column (or can store in a separate table) or from sequence, and update the column field with this sequence data

Eralper
  • 6,461
  • 2
  • 21
  • 27