Issue with column names CLUSTER & COMMENT during merge/insert command in oracle-CodePudding

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?

CodePudding user response：

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