Cannot Insert into SQL Database in Oracle SQL Developer

Question

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

enter image description here

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?

Posting images of technical information like code or error messages, instead of formatted text, is the fastest way I've seen here other than spam for a question to be downvoted and then closed or ignored. It worked out this time, but you'll want to avoid this in the future. — Joel Coehoorn, May 17 '23 at 14:54
when you put identifiers in double quotes, they are stored as case sensitive. — ibre5041, May 17 '23 at 15:00
Please check the [Database Object Naming Rules](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Database-Object-Names-and-Qualifiers.html#GUID-75337742-67FD-4EC0-985F-741C93D918DA) for quoted and nonquoted identifiers. And don't use quoted identifiers because you'll always have to quote them (which became interesting when someone put lower L instead of upper I in the object name or more funny - cyrillic C or O instead of Latin) — astentx, May 17 '23 at 15:35

score 1 · Answer 1 · answered May 17 '23 at 14:51

1

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.

answered May 17 '23 at 14:51

Joel Coehoorn

399,467
113
570
794

I tried ```INSERT INTO TABLE3(c) VALUES ('1');``` where c is a column of datatype NUMBER and it executes just fine, oracle does the implicit conversion. – Koen Lostrie May 17 '23 at 15:05

Koen Lostrie · Answer 2 · 2023-05-17T15:04:25.597

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

Cannot Insert into SQL Database in Oracle SQL Developer

2 Answers2