i got a error ORA-01747 when insert data into table

Question

i got trouble when insert data using this query :

INSERT ALL 
   INTO obat ('id_obat','nama_obat','tanggal_kadarluarsa','stock','harga') 
   VALUES (1, 'Indomethacin', '2023-09-01', 50, 3000)
   SELECT * FROM dual;

this is the table query :

CREATE TABLE obat (
    id_obat             INTEGER NOT NULL,
    nama_obat           VARCHAR2(255) NOT NULL,
    tanggal_kadarluarsa DATE NOT NULL,
    stock               INTEGER NOT NULL,
    harga               NUMBER(20, 2) NOT NULL,
    CONSTRAINT obat_pk PRIMARY KEY ( id_obat )
);

is somethin wrong with my code?

Answer 1

Single quotes are for string literals; identifiers (e.g. column names) should be in double quotes - but only have to be if they were created as quoted identifiers, and then the case of the name has to match the data dictionary name exactly. Yours are not quoted identifiers in the create statement, so you can just do:

INSERT ALL 
   INTO obat (id_obat,nama_obat,tanggal_kadarluarsa,stock,harga) 
   VALUES (1, 'Indomethacin', DATE '2023-09-01', 50, 3000)
   SELECT * FROM dual;

If you really wanted to quote them then you would need to do (including the table name to demonstrate, as the same rules apply):

INSERT ALL 
   INTO "OBAT" ("ID_OBAT","NAMA_OBAT","TANGGAL_KADARLUARSA","STOCK,HARGA") 
   VALUES (1, 'Indomethacin', DATE '2023-09-01', 50, 3000)
   SELECT * FROM dual;

but that's just more typing and arguably maybe harder to read, and easier to get wrong.

You can read more about quoted and non-quoted identifiers in the documentation.

With a single row you don't really need ALL, you can also do:

INSERT INTO obat (id_obat,nama_obat,tanggal_kadarluarsa,stock,harga) 
VALUES (1, 'Indomethacin', DATE '2023-09-01', 50, 3000)

Note that I've added the DATE keyword to these statements; '2023-09-01' is not a date, it's a string literal, so you're relying on Oracle implicitly converting that to an actual date, based on your current session NLS settings. With DATE '2023-09-01' that is now a date literal. Again, there is more in the documentation.