Oracle表命名在插入数据时引起问题 [英] Oracle table naming causes issues when inserting data

问题描述

在Oracle 12c中，我有一个名为"CONTAINERS"的表，并且以下查询未能插入数据.

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);

错误:

命令行错误:1列:28错误报告-SQL错误: ORA-02000:缺少)关键字 02000. 00000-缺少％s关键字"

但这成功了

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

为了使一切正常工作，我不得不将表从"CONTAINERS"重命名为"CONTAINER".

有人可以解释为什么我会出现这种行为吗?

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
   )

解决方案

容器"似乎是保留名称".这是从您那里得到的我的测试用例:

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才起作用.

执行给出:

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.

选中此链接以 Oracle 12c的新功能

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
   )

解决方案

"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

这篇关于Oracle表命名在插入数据时引起问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！