55

i created a sequence using the following query,

create sequence qname_id_seq start with 1 increment by 1 nocache;

Now when i try to create a table which uses the above sequence, it is throwing the following error,

Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

I used the following query to create a table with sequence.nextval,

CREATE TABLE qname
(
    qname_id integer NOT NULL default qname_id_seq.nextval PRIMARY KEY,
    qname    VARCHAR2(4000) NOT NULL -- CONSTRAINT qname_uk UNIQUE
);
Murali
  • 811
  • 2
  • 7
  • 12

5 Answers5

65

Oracle 12c

We now finally have IDENTITY columns like many other databases, in case of which a sequence is auto-generated behind the scenes. This solution is much faster than a trigger-based one as can be seen in this blog post.

So, your table creation would look like this:

CREATE TABLE qname
(
    qname_id integer GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY,
    qname    VARCHAR2(4000) NOT NULL -- CONSTRAINT qname_uk UNIQUE
);

Oracle 11g and below

According to the documentation, you cannot do that:

Restriction on Default Column Values A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

The standard way to have "auto increment" columns in Oracle is to use triggers, e.g.

CREATE OR REPLACE TRIGGER my_trigger
  BEFORE INSERT 
  ON qname
  FOR EACH ROW
  -- Optionally restrict this trigger to fire only when really needed
  WHEN (new.qname_id is null)
DECLARE
  v_id qname.qname_id%TYPE;
BEGIN
  -- Select a new value from the sequence into a local variable. As David
  -- commented, this step is optional. You can directly select into :new.qname_id
  SELECT qname_id_seq.nextval INTO v_id FROM DUAL;

  -- :new references the record that you are about to insert into qname. Hence,
  -- you can overwrite the value of :new.qname_id (qname.qname_id) with the value
  -- obtained from your sequence, before inserting
  :new.qname_id := v_id;
END my_trigger;

Read more about Oracle TRIGGERs in the documentation

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Thank you very much! I am a newbie. May i know why you have used the line, :new.id := v_id; ? Pls help! – Murali May 16 '12 at 07:47
  • I'll update the answer. Note, I had a typo. It should be `:new.qname_id`, not `:new.id` – Lukas Eder May 16 '12 at 07:52
  • 4
    +1 for the trigger method, but i think you could lose the v_id variable completely and just select into the :new.qname_id. You might also just fire the trigger when new.qname_id is null as it allows code to reference the sequence nextval directly in the insert and bypass trigger execution. – David Aldridge May 18 '12 at 08:30
  • @DavidAldridge: *single statement*: You're right. I added the extra step to have more space for comments. Updated one of the comments. *null check*: good idea – Lukas Eder May 18 '12 at 08:35
  • 1
    @LukasEder, I don't think that the *null check* is a good idea- lets say that nextval=10 and someone inserted a record with qname_id=10000, everything will be fine! untill one day (perhaps a year later) nextval will reach 10000 ... – A.B.Cade Oct 10 '12 at 10:24
  • @A.B.Cade: It's up to the OP to decide. That's why I said *optionally restrict this trigger...*. But obviously, you're right in most cases. – Lukas Eder Oct 10 '12 at 10:27
  • when you do this with a trigger is there a way to know what value was used on your most recent insert? – ThatAintWorking Aug 19 '13 at 18:49
  • @RonSmith: If you're inserting through PL/SQL, you can use the [INSERT .. RETURNING](http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#i2122356) clause. A similar technique is also available through JDBC's `getGeneratedKeys()` – Lukas Eder Aug 20 '13 at 06:21
  • The shorter variant would be `SELECT qname_id_seq.nextval INTO :new.qname_id FROM DUAL;`. – masterxilo Jun 25 '14 at 15:46
  • @masterxilo This was already suggested by DavidAldrige. Please read comments before posting. – Piotr Dobrogost Apr 14 '16 at 09:04
51

In Oracle 12c, you can now specify the CURRVAL and NEXTVAL sequence pseudocolumns as default values for a column. Alternatively, you can use Identity columns; see:

E.g.,

CREATE SEQUENCE t1_seq;
CREATE TABLE t1 (
  id          NUMBER DEFAULT t1_seq.NEXTVAL,
  description VARCHAR2(30)
);
michael
  • 9,161
  • 2
  • 52
  • 49
8

I for myself prefer Lukas Edger's solution.

But you might want to know there is also a function SYS_GUID which can be applied as a default value to a column and generate unique ids.

you can read more about pros and cons here

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
3

You can use Oracle's SQL Developer tool to do that (My Oracle DB version is 11). While creating a table choose Advanced option and click on the Identity Column tab at the bottom and from there choose Column Sequence. This will generate a AUTO_INCREMENT column (Corresponding Trigger and Squence) for you.

ddsultan
  • 2,027
  • 1
  • 19
  • 19
2

In Oracle 12c you can also declare an identity column

CREATE TABLE identity_test_tab (
  id          NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  description VARCHAR2(30)
);

examples & performance tests here ... where, is shorts, the conclusion is that the direct use of the sequence or the new identity column are much faster than the triggers.

kkk
  • 818
  • 8
  • 11