CODEWITHSUNDEEP
oracle-databaseinsert
Siya Sosibo
Siya Sosibo

Reputation: 348

Oracle table naming causes issues when inserting data

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
   )

Upvotes: 1

Views: 573

Answers (1)

Marco Polo
Marco Polo

Reputation: 738

"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

Upvotes: 3

Related Questions