Oracle 11g – How to Create a New Table

oracleoracle-11g

Hello I recently changed companies and the databases as well. Having worked with postgres so far, oracle is still fuzzy to me, so here I am stuck with creating a table. This is my SQL attempt :

CREATE SEQUENCE binaries_seq;
      CREATE TABLE binaries (
       id       NUMBER DEFAULT binaries_seq.NEXTVAL,
       PRIMARY KEY (id),
       data     BLOB NOT NULL
      )

Hardly descriptive error shows up :

SQL Error: ORA-00984: column not allowed here

Even found an article suggesting this approach :
http://oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1.php

What am I doing wrong? I was trying to create a table having one blob field and an id field.

This is my oracle version :

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Best Answer

You are, as per your tag, not using Oracle 12c.

The syntax id NUMBER DEFAULT t1_seq.NEXTVAL, is only valid from 12c onwards, so you get an error.

On Oracle 11 and prior, you want to use an insert trigger to apply the sequence's nextval to the primary key:

create or replace trigger binaries_ins
  before insert on binaries
  for each row
  when (new.id is null)
begin

  select binaries_seq.nextval into :new.id from dual;

end binaries_ins;
Related Question