0

I am trying to create a table using SQL commands on an Oracle database 12C Enterprise Edition 12.2.0.1.0.

I want a new TRANS_num to be created when I add a new values to the table starting at 1 and incrementing by one.

my code is as follows:

CREATE TABLE Sales
(
    TRANS_num INTEGER  IDENTITY (1,1) PRIMARY KEY,
    c_name VARCHAR (35) NOT NULL,
    c_address VARCHAR (35) NOT NULL,
    c_phonenumber CHAR (10) NOT NULL,
);

This results in the below image after I copy and paste into Tera Term and run the code

enter image description here

any ideas at all? I have browsed some of the answers on here for similar problems but nothing seems to fix it. I am very new to coding, sorry if it is something simple.

Noiz
  • 23
  • 1
  • 5
  • The syntax is described here (it's part of `create table`): https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6 – William Robertson Dec 30 '19 at 10:29
  • Also, the strings should all be [`varchar2`](https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#SQLRF50956), not `varchar` and not `char`. – William Robertson Dec 30 '19 at 10:32

2 Answers2

3

Whereever you got the syntax from, it must have said, that it is for SQL Server or maybe some other DBMS but not Oracle.

The closest thing since Oracle 12c is GENERATED ... AS IDENTITY. You can use

...
trans_num integer GENERATED AS IDENTITY ...
...

or

...
trans_num integer GENERATED ALWAYS AS IDENTITY ...
...

(ALWAYS is the default option if omitted) if you want, that the value is always generated and cannot be overwritten in an INSERT,

...
trans_num integer GENERATED BY DEFAULT AS IDENTITY ...
...

if you want that the value is generated when trans_num isn't specified in an INSERT but take the value given if it is specified (except for a given value of NULL, that will throw an error), or

...
trans_num integer GENERATED BY DEFAULT ON NULL AS IDENTITY ...
...

which will behave like BY DEFAULT without ON NULL except that NULL values can be given in an INSERT but will be overwritten by a generated value.

Optionally options also used for CREATE SEQUENCE can follow, for example

... GENERATED ... AS IDENTITY (START WITH 1 INCREMENT BY 1) ...

to start the values at 1 and increment them by 1 for each subsequent generated value. (START WITH 1 INCREMENT BY 1) would be what you have. But that's also the default, so you can leave it too.

sticky bit
  • 36,626
  • 12
  • 31
  • 42
2

There are two issues with your code.

  • identity column must have the syntax: generated always as IDENTITY. You can omit always keyword as it is default. Also identity column is supported on oracle 12.1 and higher.
  • comma (,) after last column must be removed.

So your code must look like following:

CREATE TABLE Sales
(
    TRANS_num INTEGER generated always as IDENTITY  PRIMARY KEY, -- changes here
    c_name VARCHAR (35) NOT NULL,
    c_address VARCHAR (35) NOT NULL,
    c_phonenumber CHAR (10) NOT NULL -- removed comma
);

Db<>fiddle demo

Cheers!!

Popeye
  • 35,427
  • 4
  • 10
  • 31