[destination-oracle] ORA-01745: invalid host/bind variable name When syncing more data

[hugovandernotte]

Connector Name

destination-oracle

Connector Version

1.0.0

What step the error happened?

During the sync

Relevant information

When syncing data to Oracle I got this error:
flush worker error: java.sql.SQLSyntaxErrorException: ORA-01745: invalid host/bind variable name

After investigation, it seems this is due because there are too many bind variables in the sql query that Airbyte is trying to insert (I have around 200k rows and airbyte tries to insert 5 columns per row). Testing with the same table but with a limit worked.

I know that oracle destination is supported by the community and is not your priority, but there was an ongoing discussion here with the exact issue I am facing. However the link provided by @alafanechere in his last message is private... Is it possible to have an update of this issue?

Relevant log output

No response

Contribute

• Yes, I want to contribute

[marcosmarxm]

Hello @hugovandernotte I added this to the team backlog but as you said at the moment it won't be prioritized. I don't think it has any workaround for now :(

[hugovandernotte]

Hello @hugovandernotte I added this to the team backlog but as you said at the moment it won't be prioritized. I don't think it has any workaround for now :(

Hey @marcosmarxm,

Thanks for your answer!

Could you at least tell me what happened to the link that was posted by your team in the discuss.airbyte.io? Here is the link https://github.com/airbytehq/airbyte/issues/14624 but it seems it was moved somewhere else

I think that a quick fix on you side would be to write per batch of 1k rows (there are 5 columns so we should be below what is acceptable in term of bind variables number)

[marcosmarxm]

@hugovandernotte you can try doing the change locally and build the connector to your use case. You need to use the airbyte-ci for that. As I said, this won't get fix for now unfortunately.

[hugovandernotte]

I think I might have found a workaround @marcosmarxm, but I would need your advice on how to proceed :)

I am running incremental run and at every run I only have a fund hundreds of rows to insert (which should work, from what i tested). The issue is coming from the initial load. I think that I can do this initial load myself and then ask airbyte to start the incremental append from that.

However, I don't know, how I could "fake" airbyte for it to consider the initial load was done by itself and that it only has the new data to insert, is there a doc somewhere in which I could found how to do that?