search

averemee-si / oracdc

Oracle database CDC (Change Data Capture)
http://a2-solutions.eu/
Apache License 2.0
107 stars 37 forks source link

ORA-00927: missing equal sign #2

Closed sonnguyen-dba closed 4 years ago

sonnguyen-dba commented 4 years ago

Hi Aleksej, ORA-00927: missing equal sign still exist after you fixed "Oracle MERGE statement for tables with only PK columns".

[2020-10-01 05:44:20,362] WARN Table PM_SPECIALSALEPROGRAM_INVSTT contains only primary key column(s)! (eu.solutions.a2.cdc.oracle.OraTable4SinkConnector:113) [2020-10-01 05:44:20,363] WARN Column list for PM_SPECIALSALEPROGRAM_INVSTT: (eu.solutions.a2.cdc.oracle.OraTable4SinkConnector:114) [2020-10-01 05:44:20,364] WARN SPECIALSALEPROGRAMID, JDBC Type -> DOUBLE (eu.solutions.a2.cdc.oracle.OraTable4SinkConnector:116) [2020-10-01 05:44:20,364] WARN INVENTORYSTATUSID, JDBC Type -> DOUBLE (eu.solutions.a2.cdc.oracle.OraTable4SinkConnector:116) [2020-10-01 05:44:20,385] ERROR Error while executing UPSERT statement merge into PM_SPECIALSALEPROGRAM_INVSTT D using (select ? SPECIALSALEPROGRAMID,? INVENTORYSTATUSID from DUAL) ORACDC on (D.SPECIALSALEPROGRAMID=ORACDC.SPECIALSALEPROGRAMID and D.INVENTORYSTATUSID=ORACDC.INVENTORYSTATUSID) when matched then update set when not matched then insert(SPECIALSALEPROGRAMID,INVENTORYSTATUSID) values(ORACDC.SPECIALSALEPROGRAMID,ORACDC.INVENTORYSTATUSID) (eu.solutions.a2.cdc.oracle.OraTable4SinkConnector:258) [2020-10-01 05:44:20,388] ERROR Error 'java.sql.BatchUpdateException: ORA-00927: missing equal sign ' when put to target system, SQL errorCode = 0, SQL state = 'null' (eu.solutions.a2.cdc.oracle.OraCdcJdbcSinkTask:138) [2020-10-01 05:44:20,390] ERROR java.sql.BatchUpdateException: ORA-00927: missing equal sign

java.sql.SQLException: java.sql.BatchUpdateException: ORA-00927: missing equal sign

    at eu.solutions.a2.cdc.oracle.OraTable4SinkConnector.execAndCloseCursors(OraTable4SinkConnector.java:259)
    at eu.solutions.a2.cdc.oracle.OraCdcJdbcSinkTask.put(OraCdcJdbcSinkTask.java:134)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.deliverMessages(WorkerSinkTask.java:545)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.poll(WorkerSinkTask.java:325)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.iteration(WorkerSinkTask.java:228)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.execute(WorkerSinkTask.java:200)
    at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:184)
    at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:234)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

Caused by: java.sql.BatchUpdateException: ORA-00927: missing equal sign

    at oracle.jdbc.driver.OraclePreparedStatement.executeLargeBatch(OraclePreparedStatement.java:9711)
    at oracle.jdbc.driver.T4CPreparedStatement.executeLargeBatch(T4CPreparedStatement.java:1447)
    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9487)
    at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:237)
    at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
    at eu.solutions.a2.cdc.oracle.OraTable4SinkConnector.execAndCloseCursors(OraTable4SinkConnector.java:256)
    ... 12 more

(eu.solutions.a2.cdc.oracle.OraCdcJdbcSinkTask:140) [2020-10-01 05:44:20,391] ERROR WorkerSinkTask{id=oracdc-sink-oracle-0} Task threw an uncaught and unrecoverable exception. Task is being killed and will not recover until manually restarted. Error: java.sql.SQLException: java.sql.BatchUpdateException: ORA-00927: missing equal sign (org.apache.kafka.connect.runtime.WorkerSinkTask:565) org.apache.kafka.connect.errors.ConnectException: java.sql.SQLException: java.sql.BatchUpdateException: ORA-00927: missing equal sign

    at eu.solutions.a2.cdc.oracle.OraCdcJdbcSinkTask.put(OraCdcJdbcSinkTask.java:141)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.deliverMessages(WorkerSinkTask.java:545)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.poll(WorkerSinkTask.java:325)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.iteration(WorkerSinkTask.java:228)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.execute(WorkerSinkTask.java:200)
    at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:184)
    at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:234)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

Caused by: java.sql.SQLException: java.sql.BatchUpdateException: ORA-00927: missing equal sign

    at eu.solutions.a2.cdc.oracle.OraTable4SinkConnector.execAndCloseCursors(OraTable4SinkConnector.java:259)
    at eu.solutions.a2.cdc.oracle.OraCdcJdbcSinkTask.put(OraCdcJdbcSinkTask.java:134)
    ... 11 more

Caused by: java.sql.BatchUpdateException: ORA-00927: missing equal sign

    at oracle.jdbc.driver.OraclePreparedStatement.executeLargeBatch(OraclePreparedStatement.java:9711)
    at oracle.jdbc.driver.T4CPreparedStatement.executeLargeBatch(T4CPreparedStatement.java:1447)
    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9487)
    at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:237)
    at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
    at eu.solutions.a2.cdc.oracle.OraTable4SinkConnector.execAndCloseCursors(OraTable4SinkConnector.java:256)
    ... 12 more

DDL:

CREATE TABLE PM_SPECIALSALEPROGRAM_INVSTT ( SPECIALSALEPROGRAMID NUMBER, INVENTORYSTATUSID NUMBER DEFAULT -1 NOT NULL, CONSTRAINT PK_SPECIALSALEPROGRAM_INVSTT PRIMARY KEY (SPECIALSALEPROGRAMID, INVENTORYSTATUSID) USING INDEX TABLESPACE DATAINDEX STORAGE (INITIAL 64K MAXEXTENTS UNLIMITED) ) TABLESPACE DATA STORAGE (INITIAL 64K MAXEXTENTS UNLIMITED) LOGGING; Regards, Son

averemee-si commented 4 years ago

For tables with primary key only replace MERGE statement or it equivalent to just insert and handle ORA-1. Working on it.

averemee-si commented 4 years ago

Hi Son, Could you please clone latest code with updated OraTable4SinkConnector.java and TargetDbSqlUtils.java and retest this issue. If this is fixed - please close this issue.

Regards, Aleksei

sonnguyen-dba commented 4 years ago

Hi Aleksej, Thank you for your fixed. Regards, Son