Q&A
ご指摘有難うございます。
■環境
Oracle 12.1.0、embulk 1.9.15、solaris 11
特定条件で、oracleのデータをbigqueryへインサートしたいです。
Oracleのsqlplusからは成功するのですが、em bulkからでは失敗します。
SQL> select * from T_RS_TRAN where IF_FLAG = '1' and TO_DATE(TRAN_DATE) >= '26-FEB-19' and TO_DATE(TRAN_DATE) <= '27-FEB-19'; ORDER_NO R FACT ORG TRAN_DATE HINBAN ---------- - ---- --- ------------ ---------------------------------------- QTY SLIP_NO IT DIVISION_NO FROM FRO FROM_SUBIN FROM_VALUE_CODE ---------- ---------- -- ----------- ---- --- ---------- --------------- FROM_OPERATOR_NAME TO_F TO_ TO_SUBINV_ TO_VALUE_CODE -------------------------------- ---- --- ---------- --------------- TO_OPERATOR_NAME SU C ACT_CREATION WH_ACT_CREAT JO UNIT_PRICE E -------------------------------- -- - ------------ ------------ -- ---------- - DESCRIPTION LOCATION ---------------------------------------------------------------------- -------- REV OPE_COD BOX_KIND DEMAND_N SUB_NO TRAF I S SOURCE_ORDER_NO --- ------- -------------------- -------- ---------- ---- - - --------------- FT_DEFINE_ID FROM_SELL_BUY_ID TO_SELL_BUY_ID CREATION_DAT ------------ ---------------- -------------- ------------ CREATED_BY LAST_UPDATE_ LAST_UPDATED_BY -------------------------------- ------------ -------------------------------- UPDATE_REQUEST_ID ----------------- TIME_STAMP I --------------------------------------------------------------------------- - ISSUE_SEQ SOUMEI_ID G GCPCS_IF_CRE RECIE DELI DELIV_NO RECV_ SU ---------- ---------- - ------------ ----- ---- -------------------- ----- -- SHIP_DATE C_ T_ RECV S SR_IF_CREATI P PS_IF_CREATI CUSTO K SUPPLY_SEC ------------ -- -- ---- - ------------ - ------------ ----- - ---------- MANAGE_NO SEND SOUM SO PS_YAZAKI_HINBAN ---------- ---- ---- -- ---------------------------------------- INVOICE_CASE_INFO_NO GYK CASE_NO CASE_SUB_NO FROM_LOCATION TO_LOCATION -------------------- --- ---------- ----------- --------------- --------------- GY I -- - 7764062 1 7200 S5B 26-FEB-19 TEST ・ ・ ・ ・ ・ 27 rows selected. ■embulkで実行した場合 embulk preview config_T_RS_TRAN_split.yml 2019-04-11 11:14:02.756 +0900: Embulk v0.9.15 2019-04-11 11:14:03.236 +0900 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected. 2019-04-11 11:14:05.998 +0900 [INFO] (main): Gem's home and path are set by default: "/export/home/srdcs/.embulk/lib/gems" 2019-04-11 11:14:06.812 +0900 [INFO] (main): Started Embulk v0.9.15 2019-04-11 11:14:06.888 +0900 [INFO] (0001:preview): Loaded plugin embulk-input-oracle (0.9.3) 2019-04-11 11:14:06.929 +0900 [INFO] (0001:preview): Connecting to jdbc:oracle:thin:@srdcs04:1521:srdcsc options {oracle.jdbc.ReadTimeout=1800000, user=srdcs, password=***, oracle.net.CONNECT_TIMEOUT=300000} 2019-04-11 11:14:07.497 +0900 [INFO] (0001:preview): Using JDBC Driver 12.1.0.2.0 2019-04-11 11:14:07.818 +0900 [INFO] (0001:preview): Connecting to jdbc:oracle:thin:@srdcs04:1521:srdcsc options {oracle.jdbc.ReadTimeout=1800000, user=srdcs, password=***, oracle.net.CONNECT_TIMEOUT=300000} 2019-04-11 11:14:08.056 +0900 [INFO] (0001:preview): SQL: select * from T_RS_TRAN where IF_FLAG = '1' and TO_DATE(TRAN_DATE) >= '26-FEB-19' and TO_DATE(TRAN_DATE) <= '27-FEB-19' java.lang.RuntimeException: java.sql.SQLDataException: ORA-01858: 数値を指定する箇所に数値以外の文字が指定されています at com.google.common.base.Throwables.propagate(Throwables.java:160) at org.embulk.input.jdbc.AbstractJdbcInputPlugin.run(AbstractJdbcInputPlugin.java:509) at org.embulk.exec.PreviewExecutor$2$1.run(PreviewExecutor.java:131) at org.embulk.spi.util.Filters$RecursiveControl.transaction(Filters.java:84) at org.embulk.spi.util.Filters.transaction(Filters.java:42) at org.embulk.exec.PreviewExecutor$2.run(PreviewExecutor.java:121) at org.embulk.input.jdbc.AbstractJdbcInputPlugin.transaction(AbstractJdbcInputPlugin.java:206) at org.embulk.exec.PreviewExecutor.doPreview(PreviewExecutor.java:119) at org.embulk.exec.PreviewExecutor.doPreview(PreviewExecutor.java:107) at org.embulk.exec.PreviewExecutor.access$000(PreviewExecutor.java:30) at org.embulk.exec.PreviewExecutor$1.run(PreviewExecutor.java:74) at org.embulk.exec.PreviewExecutor$1.run(PreviewExecutor.java:71) at org.embulk.spi.Exec.doWith(Exec.java:22) at org.embulk.exec.PreviewExecutor.preview(PreviewExecutor.java:71) at org.embulk.EmbulkEmbed.preview(EmbulkEmbed.java:168) at org.embulk.EmbulkRunner.previewInternal(EmbulkRunner.java:215) at org.embulk.EmbulkRunner.preview(EmbulkRunner.java:107) at org.embulk.cli.EmbulkRun.runSubcommand(EmbulkRun.java:433) at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:91) at org.embulk.cli.Main.main(Main.java:64) Caused by: java.sql.SQLDataException: ORA-01858: 数値を指定する箇所に数値以外の文字が指定されています at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1104) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501) at org.embulk.input.jdbc.JdbcInputConnection$SingleSelect.fetch(JdbcInputConnection.java:194) at org.embulk.input.jdbc.AbstractJdbcInputPlugin.fetch(AbstractJdbcInputPlugin.java:571) at org.embulk.input.jdbc.AbstractJdbcInputPlugin.run(AbstractJdbcInputPlugin.java:480) ... 18 more Error: java.sql.SQLDataException: ORA-01858: 数値を指定する箇所に数値以外の文字が指定されています ■定義ファイル srdcs04% cat config_T_RS_TRAN_split.yml in: type: oracle path_prefix: null driver_path: /opt/oracle/product/12.1.0/db/jdbc/lib/ojdbc7.jar host: srdcs04 user: srdcs password: srdcs database: srdcsc query: select * from T_RS_TRAN where IF_FLAG = '1' and TO_DATE(TRAN_DATE) >= '26-FEB-19' and TO_DATE(TRAN_DATE) <= '27-FEB-19' out: {type: bigquery, auth_method: json_key, json_keyfile: '/home1/bigquery/conf/My First Project-6041ffecfba7.json', project: fluid-emissary-216806, dataset: embulk_test, auto_create_table: true, table: T_RS_TRAN_split_001, open_read_timeout_sec: 360000, send_timeout_sec: 360000, read_timeout_sec: 360000}
回答3件
0
何度も何度も何度も何度も書いていますが、SQLで使っているテーブルのCREATE TABLE は質問に載せてください。
TRAN_DATE はDATE型ではありませんか？
日時書式モデル をきちんと読んだことはありますか？
SQL
1SQL> ALTER SESSION SET NLS_LANGUAGE = AMERICAN ; 2 3Session altered. 4 5SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-YY') AS DATE1 FROM DUAL ; 6 7DATE1 8----------------------------- 911-APR-19
投稿2019/04/11 06:50
総合スコア16415
0
Oracleは詳しくないですが、TO_DATEのフォーマットを指定してみてはいかがでしょうか？
https://taityo-diary.hatenablog.jp/entry/2014/11/04/070450
投稿2019/04/11 05:37
総合スコア415
0
自己解決
query: select * from T_RS_TRAN where IF_FLAG = '1' and TO_DATE(TRAN_DATE) >= TO_DATE('20190226','yyyyMMdd') and TO_DATE(TRAN_DATE) <= TO_DATE('20190227','yyyyMMdd')
上記クエリで成功しました。
ご指摘有難うございました。
投稿2019/04/11 09:17
総合スコア77
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2019/04/11 09:15
2019/04/11 09:29