Sequence in create table?

Question

So I failed in exam with score 60% which needed 3% for pass, and I want to know about sequence in create table statement, in question given us that => in the database "exist" sequence for example SEQ_001 and used in for example=>

SQL>create table ( id number default seq_001.nextval ); or
    create table ( id number default seq_001.currval );

And in the topic of 1z0 071 written that =>

This exam was validated against 11g Release 2 version 11.2.0.1.0 and up to 19c

And I know that sequence is possible in create table from 12c but not in previous versions, so how I can know that which version is for this questions and how to check that the exist sequence is used with nextval, exists not means that it is used( I am about nextval after creating, if no currval can give error ). In this type questions I confused.

I am not sure whether you are asking a technical question about functionality in Oracle or asking a question about how a particular Oracle certification exam is written or something else. I'm not seeing a question here so I'm not sure how to answer. — Justin Cave, Oct 31 '19 at 19:55
I am about versions of oracle, if I choose the answer of currval so logically this answer can be valid and not valid so it is confused, because in versions before 12c you cannot use curval,nextval in create table statement and vice versa.The exam questions is between 11g up to 19c which I wrote in the top. — , Oct 31 '19 at 20:16
I'm still not sure I understand the question. If you are asking what the correct answer on a certification exam would be, it would help to show the actual question that was asked. Technically, you would never want to use the `currval` of a sequence to populate a column though it is syntactically valid in 12c. If you actually used the `currval` when setting the default, your `insert` would fail unless some previous statement in the current session had retrieved the `nextval` of the sequence. Currently, on that exam, the answer should not be different whether you're using 11.2 or 19c. — Justin Cave, Oct 31 '19 at 20:47
I'd also point out that neither of the `create table` statements you posted are syntactically valid. I'm guessing that is an error in posting this question but it is possible that it was something you were meant to notice on the certification exam. — Justin Cave, Oct 31 '19 at 20:49
If you are asking how to test functionality on different versions of Oracle, there are sites such as [SQLFiddle](http://sqlfiddle.com) and [DBFiddle](https://dbfiddle.uk) that provide Oracle 11.2 environments. — William Robertson, Oct 31 '19 at 22:10
Sorry, I cannot explain my opinion, my english bad and thank you for help. — , Nov 01 '19 at 06:47

score 0 · Answer 1 · answered Oct 31 '19 at 20:29

Regarding the question of Oracle versions for taking the 1z0-071 exam I would say that although there are differences between 11g up to 19c, most of the questions should be based on concepts that are standard between these versions.

More specifically for the Sequence part of the question, although it is possible to create a table with a default value pointing to currval, it would seldom be useful. Using the example of ID above, imagine using this sequence to create user IDs. We would expect different users to have different IDs, and with currval there is a strong possibility that different users would have the same ID making the column essentially useless.

Although not specified in your question, I would assume that most examples of using a sequence default would be for a surrogate primary key, which would need to be unique. Nextval would ensure that these would be unique assuming that rows are not entered with values greater than the existing sequence currval by an outside user.

score 0 · Accepted Answer · answered Nov 01 '19 at 15:31

The following statement

create table t23 ( id number default seq_001.nextval );

succeeds on Oracle 18c (demo on db<>fiddle).

but fails on Oracle 11gR2 with this error:

ORA-00984: column not allowed here

db<>fiddle uses XE editions, but I don't think that's the explanation. So it seems the exam was not "was validated against 11g Release 2 version 11.2.0.1.0" with full rigour.

Sequence in create table?

2 Answers2