ORA-00922 error when set schema in Oracle

[shin1103]

ENV

embulk-0.11.0
embulk-input-jdbc-0.13.2

TO REPRODUCE

Set datasource oracle and set schema.

in:
    type: jdbc
    driver_path: ./jdbc8.jar
    driver_class: oracle.jdbc.driver.OracleDriver
    url: XXXXXX
    user: XXXXX
    password: XXXXX
    schema: my_schema
    table: my_table

REASON

setSearchPath method in embulk-input-jdbc/src/main/java/org/embulk/input/jdbc/JdbcInputConnection.java is hard coded for Postgresql clause.

    protected void setSearchPath(String schema) throws SQLException
    {
        String sql = "SET search_path TO " + quoteIdentifierString(schema);
        executeUpdate(sql);
    }

in removed module embulk-input-oracle, OracleInputConnection overrides setSearchPath method like below.

    @Override
    protected void setSearchPath(String schema) throws SQLException
    {
        connection.setSchema(schema);
    }

WORK AROUND

using query option instead of schema and table. But it is little bit difficult to understand.

I think JdbcInputConnection 's setSearchPath should be same as JdbcInputConnection 's setSearchPath .
And PostgreSQLInputConnection overrides old JdbcInputConnection 's setSearchPath for interchangeability.
And Create RedshiftInputConnection class and overrides old JdbcInputConnection 's setSearchPath for interchangeability.

ERROR LOG

[ec2-user@ip-111-111-11-11 oracle]$ java -jar ~/. embulk/lib/embulk-0.11.0. jar preview config-yml
2024-02-28 04:27:24.131 +0000 [INFO] (main): embulk home is set by the location of embulk.properties found in: /home/ec2-user/. embulk
2024-02-28 04:27:24.144 +0000 [INFO] (main): m2_repo is set as a sub directory of embulk home: /home/ec2-user/embulk/lib/m2/repository
2024-92-28 84:27:24.144+0000
2024-02-28 04:27:24.146 +0080 [INFO] (main): gem_path is set empty.
[INFO] (main): gem_home is set as a sub directory of embulk home: /home/ec2-user/. embulk/lib/gems
2024-02-28 84:27:24.146 +0080 [DEBUG] (main): Embulk system property "default guess plugin" 1s set to: "gzip, bzip2, json, csv"
2924-02-28 04:27:24.495 +0000 [INFO] (main): Started Embulk v0.11.0
2024-02-28 04:27:29.997 +0808 [INFO] (0001:preview): Gen's home and path are set by systen configs "gem _home": */home/ec2-user/embulk/lib/gems", "gem_path": **
2024-02-28 04:27:31.980 +0000 [INFO] (0001:preview): Loaded JRuby runtime 9.4.5.0
2024-02-28 04:27:32.101 +0000 [INFO] (0001:preview): Loaded plugin embulk-input-jdbc (0.13.2)
2924-02-28 04:27:32.439 +0000 [WARN] (0001:preview): "UTC" is recognized as "Z" to be compatible with the legacy style.
2024-02-28 04:27:32.511 +0000 [INFO]
(0001: preview): Connecting to jdbc:oracle:thin:@222.222.22.22:1521/my_sid options (user=my_user, password-***)
2024-02-28 04:27:33.255 +0000 [INFO]
(0001: preview): SQL: SET search _path TO "my_schema"
java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-00922: missing on invalid option
at org-embulk.input.jdbc.AbstractJdbcInputPlugin.transaction(AbstractJdbcInputPlugin-java: 227)
at org.embulk.exec.PreviewExecutor.doPreview(PreviewExecutor-java:119)
at org.embulk.exec.PreviewExecutor.doPreview(PreviewExecutor.java:102)
at org.embulk.exec.PreviewExecutor.access$000(PreviewExecutor.java:31)
at org.embulk.exec.PreviewExecutor$1.run(PreviewExecutor-java:69)
at org-embulk.exec.PreviewExecutor$1.run(PreviewExecutor-java:66)
at org.embulk.spi.ExecInternal.dowith(ExecInternal.java: 26)
at org.embulk.exec.PreviewExecutor-preview(PreviewExecutor.java:66)
at org-embulk.EmbulkEmbed.preview(EmbulkEmbed.java: 267)
at org-embulk.EmbulkRunner-previevInternal(EmbulkRunner.java:212)
at org-embulk.EmbulkRunner-preview(EmbulkRunner.java:104)
at org-embulk.cli.EmbulkRun.runInternal(EmbulkRun.java:105)
at org-embulk.cli.EmbulkRun.run(EmbulkRun.java:24)
at org.embulk.cli.Main.main(Main-java:53)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option
at oracle.jdbc.driver.T4CTTIoer11.processErrorTCTTIoer11.java:494)
at oracle.jdbc.driver.TCTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C80all.processError(T4C80all.java:1054)
at oracle-jdbc.driver.T4CTTIfun.receiveTCTTIfun.java:623)
at oracle.jdbc.driver.TACTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C80all.doOALL(T4C80a11.java:612)
at oracle.jdbc.driver.TCStatement.doOall8(T4CStatement.java:213)
at oracle. jdbc.driver.TACStatement.do0a118(4CStatement.java:37)
at oracle-jdbc.driver.T4CStatement. executeForRowsT4CStatement.java:896)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java: 1119) at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1661)
at oracle-jdbc.driver.OracleStatement.executeLargeUpdate(OracleStatement.java:1626)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1613)

at oracle. jdbc.driver.TACTIoer11.processError(TCTTIoer11.java:494)
at oracle.jdbc.driver.TCTTIoer11.processError(T4CTTIoer11.java:446)
at oracle. jdbc.driver.T4C80all.processError(T4C80all. java:1054)
at oracle.jdbc.driver.TACTTIfun. receive(T4CTTIfun.java:623)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java: 252)
at oracle. jdbc.driver.T4C80all.doOALLT4C80all. java:612)
at oracle. jdbc.driver.TCStatement.doOall8(T4CStatement.java:213)
at oracle. jdbc.driver.TCStatement.doOall8(T4CStatement.java:37)
at oracle. jdbc.driver.T4CStatement.executeForRowsT4CStatement.java:896)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1661)
at oracle.jdbc.driver.OracleStatement.executeLargeUpdate(OracleStatement.java:1626)
at oracle. jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1613)
at oracle.jdbc.driver.OracleStatementhrapper.executeUpdate(OracleStatementwrapper.java:282)
at org.embulk.input.jdbc.JdbcInputConnection.executeUpdate(JdbcInputConnection.java:216)
at org.embulk.input. jdbc.Jdbc.InputConnection.setSearchPath(JdbcInputConnection.java:57)
at org.embulk.input. jdbc.JdbcInputConnection.<init>(JdbcInputConnection.java:49)
at org.embulk.input.jdbc.JdbcInputPlugin.newConnection(JdbcInputPlugin.java:89)
at org.embulk.input.jdbc.AbstractJdbcInputPlugin.transaction(AbstractJdbcInputPlugin.java:213)
... 13 more
Caused by: Error: 922, Position : 4, Sql = SET search_path TO "my_schema", Originalsql - SET search_path TO "my_schema", Error Msg - ORA-00922: missing or invalid option
at oracle.jdbc.driver.TCTTIoer11.processError(TCTTIoer11.java:498)
... 31 more
Error: java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option 

[hiroyuki-sato]

Hello, @shin1103
Why don't you use embulk-input-oracle?
Repo(embulk-input-jdbc-external

[shin1103]

Hello, @hiroyuki-sato
Thank you for your response.
When I use embulk-input-oracle with embulk-0.11.0, TypeNotPresentException is occured.
So, I think embulk-input-oracle is not updated, when remove from embulk repository.

org.embulk.config.ConfigException: java.lang.TypeNotPresentException: Type com.fasterxml.jackson.databind.JsonNode not present.

[hiroyuki-sato]

Oh, embulk-input-oracle doesn't support embulk v0.11 yet. A temporary workaround, use Embulk 0.9.23. Have you ever tried it?

[shin1103]

Thanks,
I don't try 0.9.23 yet.
But, my workaround is work fine, I continue to use my workaround.

[hiroyuki-sato]

It seems that embulk-input-oracle supports embulk 0.11. Could you create an issue when you want to solve it?
https://github.com/hito4t/embulk-input-jdbc-external/issues

[shin1103]

I see.
I think It's better way to embulk.