Troubleshooting Oracle Community Connector Issue with SQLSyntaxErrorException

Summary

The user is facing an issue with the Oracle community connector while injecting data into Oracle. The error message indicates a java.sql.SQLSyntaxErrorException with ORA-01745. The user is looking for information on where to find logs for more details.


Question

Hello everyone,
I’m using the Oracle community connector to inject data into Oracle
So far, i’m having this issue

  "externalMessage" : "Something went wrong in the connector. See the logs for more details.",```
Where can i find logs for more details ?

<br>

---

This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. [Click here](https://airbytehq.slack.com/archives/C021JANJ6TY/p1709826979805929) if you want to access the original thread.

[Join the conversation on Slack](https://slack.airbyte.com)

<sub>
["oracle-community-connector", "data-injection", "sqlsyntaxerrorexception", "logs"]
</sub>

I add the full stack trace

        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-5.0.1.jar:?]
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-5.0.1.jar:?]
        at io.airbyte.integrations.destination.oracle.OracleOperations.lambda$insertRawRecordsInSingleQuery$1(OracleOperations.java:148) ~[io.airbyte.airbyte-integrations.connectors-destination-oracle-24.0.2.jar:?]
        at io.airbyte.db.jdbc.DefaultJdbcDatabase.execute(DefaultJdbcDatabase.java:46) ~[io.airbyte.airbyte-db-db-lib-24.0.2.jar:?]
        at io.airbyte.integrations.destination.oracle.OracleOperations.insertRawRecordsInSingleQuery(OracleOperations.java:120) ~[io.airbyte.airbyte-integrations.connectors-destination-oracle-24.0.2.jar:?]
        at io.airbyte.integrations.destination.oracle.OracleOperations.insertRecords(OracleOperations.java:105) ~[io.airbyte.airbyte-integrations.connectors-destination-oracle-24.0.2.jar:?]
        at io.airbyte.integrations.destination.jdbc.JdbcBufferedConsumerFactory.lambda$recordWriterFunction$2(JdbcBufferedConsumerFactory.java:187) ~[io.airbyte.airbyte-integrations.bases-bases-destination-jdbc-24.0.2.jar:?]
        at io.airbyte.integrations.destination.record_buffer.InMemoryRecordBufferingStrategy.flushAllBuffers(InMemoryRecordBufferingStrategy.java:87) ~[io.airbyte.airbyte-integrations.bases-base-java-24.0.2.jar:?]
        at io.airbyte.integrations.destination.record_buffer.InMemoryRecordBufferingStrategy.addRecord(InMemoryRecordBufferingStrategy.java:64) ~[io.airbyte.airbyte-integrations.bases-base-java-24.0.2.jar:?]```
It led us to this query

``` // Adapted from SqlUtils.insertRawRecordsInSingleQuery to meet some needs specific to Oracle syntax
  private static void insertRawRecordsInSingleQuery(final String tableName,
                                                    final String columns,
                                                    final String recordQueryComponent,
                                                    final JdbcDatabase jdbcDatabase,
                                                    final List&lt;AirbyteRecordMessage&gt; records,
                                                    final Supplier&lt;UUID&gt; uuidSupplier)
      throws SQLException {
    if (records.isEmpty()) {
      return;
    }

    jdbcDatabase.execute(connection -&gt; {

      // Strategy: We want to use PreparedStatement because it handles binding values to the SQL query
      // (e.g. handling formatting timestamps). A PreparedStatement statement is created by supplying the
      // full SQL string at creation time. Then subsequently specifying which values are bound to the
      // string. Thus there will be two loops below.
      // 1) Loop over records to build the full string.
      // 2) Loop over the records and bind the appropriate values to the string.
      //
      // The "SELECT 1 FROM DUAL" at the end is a formality to satisfy the needs of the Oracle syntax.
      // (see <https://stackoverflow.com/a/93724> for details)
      final StringBuilder sql = new StringBuilder("INSERT ALL ");
      records.forEach(r -&gt; sql.append(String.format("INTO %s %s VALUES %s", tableName, columns, recordQueryComponent)));
      sql.append(" SELECT 1 FROM DUAL");
      final String query = sql.toString();
      
      try (final PreparedStatement statement = connection.prepareStatement(query)) {
        // second loop: bind values to the SQL string.
        int i = 1;
        for (final AirbyteRecordMessage message : records) {
          // 1-indexed
          final JsonNode formattedData = StandardNameTransformer.formatJsonPath(message.getData());
          statement.setString(i, uuidSupplier.get().toString());
          statement.setString(i + 1, Jsons.serialize(formattedData));
          statement.setTimestamp(i + 2, Timestamp.from(Instant.ofEpochMilli(message.getEmittedAt())));
          i += 3;
        }

        statement.execute();
      }
    });
  }```
Could it be because table name and column is not escape in this query `String.format("INTO %s %s VALUES %s", tableName, columns, recordQueryComponent)`  and are using reserved oracle keyword ?

This is the failing query :

INTO ADMIN.airbyte_raw_FRECAFS_FREC_AFSSAPS (\"_AIRBYTE_AB_ID\", \"_AIRBYTE_DATA\", \"_AIRBYTE_EMITTED_AT\") VALUES (:4 , :5 , :6 )
.....
INTO ADMIN.airbyte_raw_FRECAFS_FREC_AFSSAPS (\"_AIRBYTE_AB_ID\", \"_AIRBYTE_DATA\", \"_AIRBYTE_EMITTED_AT\") VALUES (:138658 , :138659 , :138660 )
SELECT 1 FROM DUAL```

It appears to be a <Issues · airbytehq/airbyte · GitHub issue> but seems closed