1

In Oracle 12c, I had a table named "CONTAINERS" and the following query was failing to insert data.

insert  into CONTAINERS (ID,CONTAINER_NAME, HIERARCHY_SUB_TYPES_ID, HIERARCHY_TYPES_ID, SEGMENT_ID, SUB_SEGMENT_ID, USERS_ID_HIERARCHY_OWNER) 
    values  (44,'ContainerName', 1, 1, 1, 1, 1);

Error:

Error at Command Line : 1 Column : 28 Error report - SQL Error: ORA-02000: missing ) keyword 02000. 00000 - "missing %s keyword"

But this worked successfully

insert  into CONTAINERS    values  (3,'ContainerName', 1, 1, 1, 1, 1);

I had to rename the table from "CONTAINERS" to "CONTAINER" for everything to work normally.

Can someone explain why I got this behavior?

DDL:

  CREATE TABLE "RELANDHIER"."CONTAINERS" 
   (    "ID" NUMBER, 
    "CONTAINER_NAME" VARCHAR2(200 BYTE), 
    "USERS_ID_HIERARCHY_OWNER" NUMBER, 
    "SEGMENT_ID" NUMBER, 
    "SUB_SEGMENT_ID" NUMBER, 
    "HIERARCHY_TYPES_ID" NUMBER, 
    "HIERARCHY_SUB_TYPES_ID" NUMBER
   )
Siya Sosibo
  • 338
  • 1
  • 10
  • 23
  • can you post the CREATE TABLE statement – davegreen100 Feb 18 '16 at 16:22
  • First insert mentions 8 columns and you only have 7 values. You must specify 8 values, default values won't work here. Second insert DOES NOT specify columns and works. The table MUST have a default value for one of the columns which gets in play in the second insert. – Marco Polo Feb 18 '16 at 16:25
  • @davegreen100 Just did. – Siya Sosibo Feb 18 '16 at 16:26
  • 1
    @MarcoPolo First insert has 7 columns not 8. Please check the statement again. – Siya Sosibo Feb 18 '16 at 16:29
  • Still, it's a default value problem I am sure. Post a "Describe" or "Create Table" for the table. – Marco Polo Feb 18 '16 at 16:42
  • @MarcoPolo Please see the create table SQL in the question. You will see there is no default value specified. And how do you explain why it works when the name changes. – Siya Sosibo Feb 18 '16 at 18:01

1 Answers1

3

"CONTAINERS" seems to be a "reserved name". Here is my test case derived from yours :

drop table "DEMO"."CONTAINERS";

CREATE TABLE "DEMO"."CONTAINERS" 
   (    "ID" NUMBER, 
    "CONTAINER_NAME" VARCHAR2(200 BYTE), 
    "USERS_ID_HIERARCHY_OWNER" NUMBER, 
    "SEGMENT_ID" NUMBER, 
    "SUB_SEGMENT_ID" NUMBER, 
    "HIERARCHY_TYPES_ID" NUMBER, 
    "HIERARCHY_SUB_TYPES_ID" NUMBER
   );

insert into CONTAINERS (ID,CONTAINER_NAME, HIERARCHY_SUB_TYPES_ID, HIERARCHY_TYPES_ID, SEGMENT_ID, SUB_SEGMENT_ID, USERS_ID_HIERARCHY_OWNER) 
    values  (44,'ContainerName', 1, 1, 1, 1, 1);
insert into "DEMO".CONTAINERS (ID,CONTAINER_NAME, HIERARCHY_SUB_TYPES_ID, HIERARCHY_TYPES_ID, SEGMENT_ID, SUB_SEGMENT_ID, USERS_ID_HIERARCHY_OWNER) 
    values  (44,'ContainerName', 1, 1, 1, 1, 1);

INSERT only works if I prefix the owner schema to it.

Execution gives :

Table dropped.


Table created.

insert into CONTAINERS (ID,CONTAINER_NAME, HIERARCHY_SUB_TYPES_ID, HIERARCHY_TYPES_ID, SEGMENT_ID, SUB_SEGMENT_ID, USERS_ID_HIERARCHY_OWNER)
                          *
ERROR at line 1:
ORA-02000: missing ) keyword



1 row created.

Check this link to Oracle 12c new features

Marco Polo
  • 728
  • 5
  • 10
  • 1
    Your answer makes senses. I think Oracle needs to updates it's documention for Oracle SQL Reserved Words. https://docs.oracle.com/database/121/SQLRF/ap_keywd001.htm#SQLRF55621 – Siya Sosibo Feb 18 '16 at 18:09
  • I think just quoting your table name would also resolve the issue. i.e. insert into "CONTAINERS"... – Marco Polo Feb 19 '16 at 12:19
  • The problem was that this query was being generated by Hibernate. Not much control of the output. – Siya Sosibo Feb 19 '16 at 12:38
  • 1
    It all comes down to good modeling standards. That's why our modeling standards adds an application prefix to all our tables. For example, in our ERP, all our tables are prefixed with "ERP_". This prevents any problems with reserved words. We have tables called "ERP_DATES" and in your case, the table would be named "ERP_CONTAINERS". – Marco Polo Feb 19 '16 at 12:46