无法插入到Oracle SQL Developer中的SQL数据库

huangapple go评论70阅读模式
英文:

Cannot Insert into SQL Database in Oracle SQL Developer

问题

我已经使用以下代码创建了一个表格:

CREATE TABLE GLASACKO_MESTO(
	"ID_glasackog_mesta" NUMBER(10, 0) PRIMARY KEY,
	"Broj_registrovanih_biraca" NUMBER NOT NULL,
	"Broj_glasackog_mesta" NUMBER NOT NULL,
	"Izborna_jedinica" NUMBER NOT NULL
);

CREATE INDEX GLASACKO_MESTO_BROJ_REG_B ON GLASACKO_MESTO("Broj_registrovanih_biraca");

还有:

CREATE SEQUENCE "GLASACKO_MESTO_ID_SEQ"
	MINVALUE 1
	MAXVALUE 9999999999
	INCREMENT BY 1
	START WITH 101
	CACHE 5
	NOORDER
	NOCYCLE;

CREATE OR REPLACE TRIGGER "GLASACKO_MESTO_AUTO_PK"
    BEFORE INSERT
    ON GLASACKO_MESTO
    FOR EACH ROW
BEGIN
    :NEW."ID_glasackog_mesta" := GLASACKO_MESTO_ID_SEQ.NEXTVAL;
END;

看起来一切都成功创建了,但是当我尝试执行以下的INSERT命令时,我收到了错误消息:

INSERT INTO GLASACKO_MESTO(Broj_registrovanih_biraca, Broj_glasackog_mesta, Izborna_jedinica)
VALUES ('2', '3', '4');

并且我也尝试手动设置ID:

INSERT INTO GLASACKO_MESTO(ID_glasackog_mesta, Broj_registrovanih_biraca, Broj_glasackog_mesta, Izborna_jedinica)
VALUES (1, '2', '3', '4');

但是没有任何区别。我该如何修复这个问题?

英文:

I have created a table using following code:

CREATE TABLE GLASACKO_MESTO(
	"ID_glasackog_mesta" NUMBER(10, 0) PRIMARY KEY,
	"Broj_registrovanih_biraca" NUMBER NOT NULL,
        "Broj_glasackog_mesta" NUMBER NOT NULL,
        "Izborna_jedinica" NUMBER NOT NULL
);

CREATE INDEX GLASACKO_MESTO_BROJ_REG_B ON GLASACKO_MESTO("Broj_registrovanih_biraca");

and also:

CREATE SEQUENCE "GLASACKO_MESTO_ID_SEQ"
	MINVALUE 1
	MAXVALUE 9999999999
	INCREMENT BY 1
	START WITH 101
	CACHE 5
	NOORDER
	NOCYCLE;

CREATE OR REPLACE TRIGGER "GLASACKO_MESTO_AUTO_PK"
    BEFORE INSERT
    ON GLASACKO_MESTO
    FOR EACH ROW
BEGIN
    :NEW."ID_glasackog_mesta" := GLASACKO_MESTO_ID_SEQ.NEXTVAL;
END;

As i can see, it looks like everything was created successfully, but when try to execute following INSERT command, i get error.

INSERT INTO GLASACKO_MESTO(Broj_registrovanih_biraca,Broj_glasackog_mesta,Izborna_jedinica)
values ('2','3','4');

无法插入到Oracle SQL Developer中的SQL数据库

And also tried setting manualy ID

INSERT INTO GLASACKO_MESTO(ID_glasackog_mesta,Broj_registrovanih_biraca,Broj_glasackog_mesta,Izborna_jedinica)
values (1,'2','3','4');

But it didn't make any difference. How can I fix this?

答案1

得分: 1

您使用了字符串文字'2','3','4',但列类型已定义为number。您可能希望使用以下方式:

INSERT INTO GLASACKO_MESTO(Broj_registrovanih_biraca, Broj_glasackog_mesta, Izborna_jedinica)
VALUES (2, 3, 4)

另外,错误消息指出IZBORNA_JEDINICA是无效的标识符。这意味着在SQL或表定义中可能存在拼写错误或奇怪的Unicode字符,使得这个列名与表中定义的不匹配。

英文:

You used string literals '2','3','4', but the column types are defined as number. You probably want this:

INSERT INTO GLASACKO_MESTO(Broj_registrovanih_biraca,Broj_glasackog_mesta,Izborna_jedinica)
values (2,3,4)

Additionally, the error message says IZBORNA_JEDINICA is an invalid identifier. That means there's likely a typo or odd unicode character either in the SQL or the table definition so that this column name doesn't match what was defined on the table.

答案2

得分: 1

您创建了一个区分大小写的列名表格(因为您在列名周围加了引号),所以您始终需要在引号和相同大小写的情况下引用这些列。重新创建不在列名周围加引号的表格会节省您时间和麻烦。

请查看这个示例:

create table TABLE1 (column1 VARCHAR2(100));
Table TABLE1 created.
INSERT INTO TABLE1(COLUMN1) VALUES ('koen');
ORA-00904: "COLUMN1": invalid identifier
INSERT INTO TABLE1("column1") VALUES ('koen');
1 row inserted.

现在创建不在列名周围加引号的表格:

create table TABLE2 (column1 VARCHAR2(100));
Table TABLE2 created.
INSERT INTO TABLE1(COLUMN1) VALUES ('koen');
1 row inserted.

更多信息请参阅此处

英文:

You created the table with case sensitive columns (since you put quotes around the column names), so you always need to reference the columns with quotes and the same case. Recreate the table without the quotes around the column names. It will save you time and headaches.

Check this example:

create table TABLE1 ("column1" VARCHAR2(100));
Table TABLE1 created.
INSERT INTO TABLE1(COLUMN1) VALUES ('koen');
ORA-00904: "COLUMN1": invalid identifier
INSERT INTO TABLE1("column1") VALUES ('koen');
1 row inserted.

Now create the table with no quotes around the column name:

create table TABLE2 (column1 VARCHAR2(100));
Table TABLE2 created.
INSERT INTO TABLE1(COLUMN1) VALUES ('koen');
1 row inserted.

More info here

huangapple
  • 本文由 发表于 2023年5月17日 22:47:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/76273396.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定