merge_oracle_ORA-01747:无效的user.table.column、table.column或列规范

Question

我正在尝试在oracle:中进行合并

preparedStatement = dbConnection.prepareStatement("MERGE INTO dev_corpus a " +
                          "USING (SELECT ? corpusid, ? corpus_name, ? corpusdesc, ? corpusimageids, ? rocf1, ? rocf2, ? rocf3, ? rocc1, ? rocc2, ? rocc3, ? corpusactive, ? corpusrunfrequency, ? corpuslastrun, ? corpuslastsync, ? rocsettingid, ? corpusaffinity, ? corpusterms, ? corpusdomain FROM dual) incoming " +
                          "ON (a.corpusid = incoming.corpusid ) " +
                          "WHEN MATCHED THEN " +
                          "UPDATE SET (a.corpus_name = incoming.corpus_name AND a.corpusdesc = incoming.corpusdesc AND a.corpusimageids = incoming.corpusimageids AND a.rocf1 = incoming.rocf1 AND a.rocf2 = incoming.rocf2 AND a.rocf3 = incoming.rocf3 AND a.rocc1 = incoming.rocc1 AND a.rocc2 = incoming.rocc2 AND a.rocc3 = incoming.rocc3 AND a.corpusactive = incoming.corpusactive AND a.corpusrunfrequency = incoming.corpusrunfrequency AND a.corpuslastrun = incoming.corpuslastrun AND a.corpuslastsync = incoming.corpuslastsync AND a.rocsettingid = incoming.rocsettingid AND a.corpusaffinity = incoming.corpusaffinity AND a.corpusterms = incoming.corpusterms AND a.corpusdomain = incoming.corpusdomain) " +
                          "WHEN NOT MATCHED THEN " +
                          "INSERT (a.corpusid, a.corpus_name, a.corpusdesc, a.corpusimageids, a.rocf1, a.rocf2, a.rocf3, a.rocc1, a.rocc2, a.rocc3, a.corpusactive, a.corpusrunfrequency, a.corpuslastrun, a.corpuslastsync, a.rocsettingid, a.corpusaffinity, a.corpusterms, a.corpusdomain) " +
                          "VALUES (incoming.corpusid, incoming.corpus_name, incoming.corpusdesc, incoming.corpusimageids, incoming.rocf1, incoming.rocf2, incoming.rocf3, incoming.rocc1, incoming.rocc2, incoming.rocc3, incoming.corpusactive, incoming.corpusrunfrequency, incoming.corpuslastrun, incoming.corpuslastsync, incoming.rocsettingid, incoming.corpusaffinity, incoming.corpusterms, incoming.corpusdomain)");

我得到这个错误：

    ORA-01747: invalid user.table.column, table.column, or column specification
java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:505)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:223)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1328)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3593)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3674)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354)
    at com.mycompany.datalayer.DB.insertToTaxonomy(DB.java:261)
    at com.mycompany.datalayer.DB.restoreBackUp(DB.java:94)
    at com.mycompany.datalayer.DB.startProcess(DB.java:56)
    at com.mycompany.oraclebackup.App.main(App.java:13)

现在，我已经检查了列名，它们是正确的。你知道问题出在哪里吗？我该如何解决？（附言：我没有使用hibernate）

谢谢！

Answer 1

在Java Prepared语句中，您可以使用"？"仅适用于where子句中的列名。您正试图将此概念扩展到其他地方，这是无效的。以下是代码中有问题的部分：

SELECT ? corpusid, ? corpus_name, ? corpusdesc

以下是javadocs:的注释

注意：用于设置IN参数值的setter方法（setShort、setString等）必须指定兼容的类型具有所定义的输入参数的SQL类型。例如，如果IN参数具有SQL类型INTEGER，则方法setInt应为习惯于

Answer 2

我猜像corpusid、corpusimageids等字段可能不会作为字符串相关的数据类型（例如varchar）存储在数据库的dev_corpus表中。它们可能是整数或数字字段。

当你替换"？"中的参数时，我觉得它们是作为字符串字段传递的（例如，您可能正在使用字符串数组来传递这些参数）。在这种情况下，必须确保参照dev_corpus表中使用的数据类型，在查询本身中处理参数的数据类型。

例如，假设字段corpusid是数据库表中的类型号，则您的查询应该是：

preparedStatement = dbConnection.prepareStatement("MERGE INTO dev_corpus a " +
                          "USING (SELECT to_number(?) corpusid, ? corpus_name, ? corpusdesc, ? corpusimageids, ? rocf1,..."

字符串字段（char、varchar）以外的任何字段都可能需要特别注意。例如

to_date(column_name,'dd-mm-yyyy')