9

I try to create an auto incremented column on a table and as I see in this post there are 2 ways, the second implementation with the Identity column is a more elegant solution, but when I try to implement it I get the following error:

    Error at Command Line : 3 Column : 31
    Error report -
    SQL Error: ORA-02000: missing ALWAYS keyword
    02000. 00000 -  "missing %s keyword"

Actual table script implementation:

CREATE TABLE "PLATFORM"."AUTH_PERMISSION"
(
    ID NUMBER(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY, 
    -- ID NUMBER(19,0) PRIMARY KEY NOT NULL,
    NAME VARCHAR2(50) UNIQUE NOT NULL,
    ACTION_ID NUMBER(19,0) NOT NULL,
    RESOURCE_ID NUMBER(19,0) NOT NULL,
    ENVIRONMENT_ID NUMBER(19,0) NOT NULL,
    CONSTRAINT "ACTION_ID" FOREIGN KEY ("ACTION_ID")
      REFERENCES "AUTH_ACTION" ("ID") ENABLE,
    CONSTRAINT "ENVIRONMENT_ID" FOREIGN KEY ("ENVIRONMENT_ID")
      REFERENCES "AUTH_ENVIRONMENT" ("ID") ENABLE,
    CONSTRAINT "RESOURCE_ID" FOREIGN KEY ("RESOURCE_ID")
      REFERENCES "AUTH_RESOURCE" ("ID") ENABLE,
    UNIQUE (ACTION_ID, ENVIRONMENT_ID, RESOURCE_ID)
);

It can bee seen that the column that I try to auto-increment is the primary key of the table.

This is the reference from where I got the solution.

The problem was that I used an older version of Oracle, 11g.

aurelius
  • 3,946
  • 7
  • 40
  • 73
  • `GENERATED BY DEFAULT` statement is a void. check out more information here http://oracle-base.com/articles/11g/virtual-columns-11gr1.php . It exists from oracle 12c, specify your database version?? – Exhausted Mar 02 '15 at 09:38
  • 1
    your oracle version?? – Prashant Mar 02 '15 at 09:43
  • the oracle version is 12c – aurelius Mar 02 '15 at 09:44
  • 1
    Are you sure the server is 12c, not just your client? It looks very much like your server is 11g. What does `select * from product_component_version` or `select * from v$version` report? – Alex Poole Mar 02 '15 at 09:57

3 Answers3

10

Perhaps the Oracle database (server) you are trying to connect to is 12c, however the client (installed locally) you are using doesn't support the feature. Please check your Oracle client version, it could be 11g or lower which doesn't support it. You need to download a higher client version.

Works perfectly on version 12.1.0.1.

SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> CREATE TABLE AUTH_PERMISSION
  2  (
  3      ID NUMBER(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY,
  4      -- ID NUMBER(19,0) PRIMARY KEY NOT NULL,
  5      NAME VARCHAR2(50) UNIQUE NOT NULL,
  6      ACTION_ID NUMBER(19,0) NOT NULL,
  7      RESOURCE_ID NUMBER(19,0) NOT NULL,
  8      ENVIRONMENT_ID NUMBER(19,0) NOT NULL
  9  );

Table created.
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
1

If you are run into this issue, and you can't update the database/client from 11 to 12 then here is the workaround.

  1. Add the following line of code to your Dbcontext Class`
optionsBuilder.UseOracle(configuration.GetConnectionString("DefaultConnection"), b =>
            b.UseOracleSQLCompatibility("11"));

Explanation: Oracle 11g do not support IDENTITY command. The PKs must be generated with a trigger and a sequence. When you add UseOracleSQLCompatibility("11") to the DbContext, EF will generate migrations scripts compatible with oracle 11g. In this case, A sequence table will be generated to use for the PKS.

  1. Another issue you might run into is this: ORA-00972: identifier is too long

If you run into this error, it means oracle database versions less than 12 only support identifiers of max length 30.

Solution:

  1. For Dotnet Core below 3.1 use add this to your DBContext class

modelBuilder.Model.Relational().MaxIdentifierLength = 30;

  1. For Dotnet Core 3.1 above, use this instead.

modelBuilder.Model.SetMaxIdentifierLength(30);

caged_raven_wings
  • 113
  • 2
  • 9
0

I was getting same error but when I tried running as script in editor, it worked. I later pasted in .sql file to run the script as @<path>\<filename>

code:

CREATE TABLE TESTING(
  ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  NAME VARCHAR2(20)
  );

This worked for me. I am using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64 bit Production PL/SQL Release 12.2.0.1.0

you may also try:

CREATE TABLE TESTING(
  ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY(START WITH 101 MAXVALUE 9999999 
       MINVALUE 1 NOCYCLE CACHE 20) NOT NULL, 
  NAME VARCHAR2(20)
  );

start with : is from where you want to start your counter (I used 101) maxvalue is till what value it should increment minvalue is increment by nocycle cashe is how many numbers u want to keep in ram

Pavn
  • 160
  • 1
  • 10