How to create a sequence with a dynamic start value

#11

On Jun 13, 7:44*am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

Quote:

On Jun 12, 2:17 am, Norbert Pürringer <thalio... (AT) graffiti (DOT) net> wrote:

Hello,

what is the best way to create a sequence with a dynamic start value.

The following statement is not valid:

create sequence TEXT_SEQ start with select NVL(max(ID),0) + 1 from
TEXT;

Thank you,
Norbert

The core question is why do you need to do that?
also why do you need to do it in one statement?
* Ed

Many developers over the years have tried to read the table for the
last ID value issued only to learn that he or she had failed to take
into account the Oracle read consistency model as part of the
transaction design.

Readers do not wait on writers so it is possbile for more than one
user session to select the same max value and hence have inserts fail
on duplicate key errors if a unique index exists on the column
otherwise duplicate values exist for the column in the absence of the
unique index.

Just use a sequence. Who cares if there are missing values? Under
relational design theory a numeric generated key is a meaningless
identifier. It just identifies the row, nothing more.

HTH -- Mark D Powell --

#12

On Jun 13, 7:44*am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

Quote:

On Jun 12, 2:17 am, Norbert Pürringer <thalio... (AT) graffiti (DOT) net> wrote:

Hello,

what is the best way to create a sequence with a dynamic start value.

The following statement is not valid:

create sequence TEXT_SEQ start with select NVL(max(ID),0) + 1 from
TEXT;

Thank you,
Norbert

The core question is why do you need to do that?
also why do you need to do it in one statement?
* Ed

Many developers over the years have tried to read the table for the
last ID value issued only to learn that he or she had failed to take
into account the Oracle read consistency model as part of the
transaction design.

Readers do not wait on writers so it is possbile for more than one
user session to select the same max value and hence have inserts fail
on duplicate key errors if a unique index exists on the column
otherwise duplicate values exist for the column in the absence of the
unique index.

Just use a sequence. Who cares if there are missing values? Under
relational design theory a numeric generated key is a meaningless
identifier. It just identifies the row, nothing more.

HTH -- Mark D Powell --

#13

On Jun 13, 7:44*am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:

Quote:

On Jun 12, 2:17 am, Norbert Pürringer <thalio... (AT) graffiti (DOT) net> wrote:

Hello,

what is the best way to create a sequence with a dynamic start value.

The following statement is not valid:

create sequence TEXT_SEQ start with select NVL(max(ID),0) + 1 from
TEXT;

Thank you,
Norbert

The core question is why do you need to do that?
also why do you need to do it in one statement?
* Ed

Many developers over the years have tried to read the table for the
last ID value issued only to learn that he or she had failed to take
into account the Oracle read consistency model as part of the
transaction design.

Readers do not wait on writers so it is possbile for more than one
user session to select the same max value and hence have inserts fail
on duplicate key errors if a unique index exists on the column
otherwise duplicate values exist for the column in the absence of the
unique index.

Just use a sequence. Who cares if there are missing values? Under
relational design theory a numeric generated key is a meaningless
identifier. It just identifies the row, nothing more.

HTH -- Mark D Powell --

#14

On Jun 12, 2:17 am, Norbert Pürringer <thalio... (AT) graffiti (DOT) net> wrote:

Quote:

Hello,

what is the best way to create a sequence with a dynamic start value.

The following statement is not valid:

create sequence TEXT_SEQ start with select NVL(max(ID),0) + 1 from
TEXT;

Thank you,
Norbert

One of the way, using sqlplus , generate a script file & execute it :

spool seq.ddl
select 'create sequence text_seq start with' || nvl(max(id),0) +1

Quote:

|';' from text;
spoo off

@seq.ddl

-Madhu Sreeram

#15

On Jun 12, 2:17 am, Norbert Pürringer <thalio... (AT) graffiti (DOT) net> wrote:

Quote:

Hello,

what is the best way to create a sequence with a dynamic start value.

The following statement is not valid:

create sequence TEXT_SEQ start with select NVL(max(ID),0) + 1 from
TEXT;

Thank you,
Norbert

One of the way, using sqlplus , generate a script file & execute it :

spool seq.ddl
select 'create sequence text_seq start with' || nvl(max(id),0) +1

Quote:

|';' from text;
spoo off

@seq.ddl

-Madhu Sreeram

#16

On Jun 12, 2:17 am, Norbert Pürringer <thalio... (AT) graffiti (DOT) net> wrote:

Quote:

Hello,

what is the best way to create a sequence with a dynamic start value.

The following statement is not valid:

create sequence TEXT_SEQ start with select NVL(max(ID),0) + 1 from
TEXT;

Thank you,
Norbert

One of the way, using sqlplus , generate a script file & execute it :

spool seq.ddl
select 'create sequence text_seq start with' || nvl(max(id),0) +1

Quote:

|';' from text;
spoo off

@seq.ddl

-Madhu Sreeram

#17

On Jun 12, 2:17 am, Norbert Pürringer <thalio... (AT) graffiti (DOT) net> wrote:

Quote:

Hello,

what is the best way to create a sequence with a dynamic start value.

The following statement is not valid:

create sequence TEXT_SEQ start with select NVL(max(ID),0) + 1 from
TEXT;

Thank you,
Norbert

One of the way, using sqlplus , generate a script file & execute it :

spool seq.ddl
select 'create sequence text_seq start with' || nvl(max(id),0) +1

Quote:

|';' from text;
spoo off

@seq.ddl

-Madhu Sreeram