Issue with column names CLUSTER & COMMENT during merge/insert command in oracle
I am having a table where two columns are having reserved keywords of oracle named CLUSTER & COMMENT. I am trying to use the merge command from spring JDBC which is throwing the below error.
ORA-01747: invalid user.table.column, table.column, or column specification
MERGE INTO TABLE DESTINATION
USING ( SELECT ? AS NAME, ? AS CLUSTER, ? AS COMMENT FROM DUAL) SOURCE
ON (SOURCE.NAME = DESTINATION.NAME)
WHEN MATCHED THEN
UPDATE SET DESTINATION.CLUSTER = SOURCE.CLUSTER, DESTINATION.COMMENT = SOURCE.COMMENT
WHEN NOT MATCHED THEN
INSERT (NAME, CLUSTER, COMMENT)
VALUES (SOURCE.NAME, SOURCE.CLUSTER,SOURCE.COMMENT)
Can some please help me how to resolve this without changing the column names?
If you are going to use reserved words as identifers then you need to use quoted identifiers (and the exact case used in the database):
MERGE INTO table_name DESTINATION
USING (
SELECT ? AS NAME,
? AS "CLUSTER",
? AS "COMMENT"
FROM DUAL
) SOURCE
ON (SOURCE.NAME = DESTINATION.NAME)
WHEN MATCHED THEN
UPDATE
SET DESTINATION."CLUSTER" = SOURCE."CLUSTER",
DESTINATION."COMMENT" = SOURCE."COMMENT"
WHEN NOT MATCHED THEN
INSERT (NAME, "CLUSTER", "COMMENT")
VALUES (SOURCE.NAME, SOURCE."CLUSTER",SOURCE."COMMENT");
However, a better solution would be to change the identifiers to non-reserved words.
db<>fiddle here