質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.53%
Oracle Database 11g

Oracle DatabaseはRDBMSの商品です。具体的な発売商品として知られているのが、 Oracle9i、Oracle10g、Oracle 11gとOracle 12cです。

VBScript

VBScript(Visual Basic Scripting Edition)はMicrosftが開発したスクリプト言語であり、Visual Basicのサブセットです。

Q&A

解決済

1回答

14250閲覧

OracleDBのNumber型に文字列をInsertするとNullが入ってしまう

Kiri_Tanpo

総合スコア13

Oracle Database 11g

Oracle DatabaseはRDBMSの商品です。具体的な発売商品として知られているのが、 Oracle9i、Oracle10g、Oracle 11gとOracle 12cです。

VBScript

VBScript(Visual Basic Scripting Edition)はMicrosftが開発したスクリプト言語であり、Visual Basicのサブセットです。

0グッド

0クリップ

投稿2018/03/30 02:49

編集2018/03/30 05:45

前提・実現したいこと

2度目の質問投稿になってしまい、すいませんがよろしくお願いいたします。

OracleDBにADODB経由でアクセスし、バインド変数でカラムデータを指定して、
Insert文を実行しています。
データ型がNumberのカラムに空白でない文字列を指定すると、Nullが入ってしまう原因を知りたい。

発生している問題・エラーメッセージ

データ型がNumberのカラムに空白でない文字列を指定すると、
エラーが発生せず、正常終了してしまう。
テーブルを確認すると(Null)が入ってしまっている。

該当のソースコード

vbs

1'バッチホームディレクトリパス 2Dim BATCH_HOME 3Dim DATA_DIR 4Dim INSERT_INI 5Dim LOG_FILE 6Dim CONTROL_FLAG 7Dim COMPLETED_DIR 8Dim END_CODE'終了コード 9 10'DB名 11Private Const DATABASE = "XE" 12'ユーザー名 13Private Const DB_UID = "scott" 14'パスワード 15Private Const DB_PWD = "tiger;" 16 17'DB接続関係の定数 18Private Const adDouble = 5 19Private Const adDBDate = 7 20Private Const adNumeric = 131 21Private Const adVarChar = 200 22Private Const adCmdText = 1 23Private Const adParamInput = 1 24Private Const adUseClient = 3 25Private Const adOpenStatic = 3 26 27~定数でエラーコード番号をセット~ 28 29Private Const TABLESQL = "select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from user_tab_columns where table_name = ? Order by COLUMN_ID" 30 31'共通変数 宣言 32Public objADO 33Public objFSO 34Public objLogFile 35Public objIniFile 36Public objCsvDataFile 37 38'cmdからの引数を受け取る 39~ファイルパス取得~ 40 41'終了コード初期化 42END_CODE = 0 43 44'--------------------------------------------------------------------- 45' 日時設定 46'--------------------------------------------------------------------- 47~~ 48'--------------------------------------------------------------------- 49' ログファイルオープン 50'--------------------------------------------------------------------- 51~~ 52'------------------------------------------------------------------' 53' Iniファイル確認 54'------------------------------------------------------------------' 55~Iniファイルが存在しなければ、処理を終了する~ 56'------------------------------------------------------------------' 57' DBコネクションの確立 58'------------------------------------------------------------------' 59Dim Connection 60Dim objADODbConnErr 61 62Set objADO = CreateObject("ADODB.Connection") 63Connection = "Provider=OraOLEDB.Oracle;Data Source=" & DATABASE & ";User ID=" & DB_UID & ";Password=" & DB_PWD 64 65'タイムアウト時間 無制限 66objADO.ConnectionTimeout = 0 67objADO.Open Connection 68 69'DBコネクションが取得できない場合 70~エラーログ出力~ 71 72'CSVファイル挿入のため、トランザクション開始 73objADO.BeginTrans 74 75'--------------------------------------------------------------------- 76' DATAフォルダ内のファイル情報取得(ファイル数分繰返し) 77'--------------------------------------------------------------------- 78Dim objDataFile 79Dim cntDataFile'処理ファイル数カウンタ 80Dim strIniLine 81Dim strInsertSql 82 83cntDataFile = 0 84Dim iniReadContinue 85 86For Each objDataFile In objFSO.GetFolder(DATA_DIR).Files : For iniReadContinue = 1 To 1 87 ' ファイルの拡張子がCSV以外だったら、次のファイル情報へContinue 88 ~~ 89 ' Csvファイル処理開始ログ出力 90 ~~ 91 '--------------------------------------------------------------------- 92 ' Iniファイルデータ情報取得(Iniファイルのレコード数分繰返し) 93 '--------------------------------------------------------------------- 94 Set objIniFile = objFSO.OpenTextFile(INSERT_INI) 95 Do Until objIniFile.AtEndOfStream 96  '------------------------------------------------------------' 97  ' Iniファイル定義情報とCSVファイル名のマッチング 98  '------------------------------------------------------------' 99  strIniLine = objIniFile.ReadLine 100  strInsertSql = Mid(strIniLine, Instr(strIniLine, "=") + 1) 101  'Csvファイル名がIniファイル内のCSVファイル名と一致しない場合、次のIniファイル行へContinue 102  If Left(strIniLine, Instr(strIniLine, "=") - 1) & ".csv" = objDataFile.Name Then 103   Exit Do 104  End If 105  '一致しなかった場合、INSERT分を空白で初期化する 106  strInsertSql = "" 107 Loop 108 objIniFile.close 109 110 'SQL文が空白の場合、INIからSQL文を取得できなかったため、エラーとする 111 ~~ 112 '------------------------------------------------------------' 113 ' Insert対象テーブル情報取得 114 '------------------------------------------------------------' 115 Dim objCMD 116 Dim objRs 117 Dim objADOErrItem 118 Set objCMD = CreateObject("ADODB.Command") 119 objCMD.ActiveConnection = objADO 120 objCMD.CommandText = TABLESQL 121 objCMD.CommandType = adCmdText 122 Dim param 123 Set param = objCMD.CreateParameter(0, adVarChar, adParamInput, 30) 124 param.Value = Left(strIniLine, Instr(strIniLine, "=") - 1) 125 objCMD.Parameters.Append param 126 Set objRs = CreateObject("ADODB.Recordset") 127 objRs.CursorLocation = adUseClient 128 objRs.CursorType = adOpenStatic 129 objRs.Open objCMD 130 'DBに対象テーブルが存在しなかった場合のエラー処理 131 ~~ 132 '------------------------------------------------------------' 133 ' CSVファイルデータ情報取得(CSVファイルのレコード数分繰返し) 134 '------------------------------------------------------------' 135 Dim csvReadContinue 136 Set objCsvDataFile = objFSO.OpenTextFile(objDataFile.Path, 1, False) 137 Dim cntLoop 138 cntLoop = 0 139 END_CODE = 0 140 141 Do Until objCsvDataFile.AtEndOfStream : For csvReadContinue = 1 To 1 142  Dim tmp 143  Dim aryCsvResult 144  tmp = objCsvDataFile.ReadLine 145  aryCsvResult = Split(tmp, ",") 146 147  ' CSV読込カウンタが0(1行目)の場合、ヘッダー行の為、次のCSVデータ行へContinue 148  ~~ 149 150  'CSVデータと、SQL文の項目一致チェック 151  ~~ 152  '------------------------------------------------------------' 153  ' CSVのデータ内容をSQLにバインドし、SQL実行 154  '------------------------------------------------------------' 155  Set objCMD = CreateObject("ADODB.Command") 156  objCMD.ActiveConnection = objADO 157  objCMD.CommandText = strInsertSql 158  objCMD.CommandType = 1 159  Dim paramNum 160  paramNum = 0 161  objRs.MoveFirst 162 163  Do Until objRs.EOF 164   Dim columnType 165   columnType = getColumnType(objRs.Fields("DATA_TYPE").Value) 166   Set param = objCMD.CreateParameter(paramNum, columnType, adParamInput, objRs.Fields("DATA_LENGTH").Value ) 167 168~①~ 169 170   If Trim(aryCsvResult(paramNum)) = "" Then 171    param.Value = null 172   Else 173    param.Value = aryCsvResult(paramNum) 174   End If 175   objCMD.Parameters.Append param 176   paramNum = paramNum + 1 177   objRs.MoveNext 178  Loop 179  Err.Number = 0 180  objCMD.Execute() 181  '------------------------------------------------------------' 182  ' SQL実行時エラーの確認 183  '------------------------------------------------------------' 184~追記~ 185  If Err.Number <> 0 Then 186   '--------------------------------------------------------' 187   'SQLエラー情報のログ出力 188   '--------------------------------------------------------' 189   Set objADOErrItem = objADO.Errors.Item(0) 190   ~エラーログ出力~ 191   If objADOErrItem.NativeError = "ORA-00060" Then 192    END_CODE = ERR_DeadLouk 193   Else 194    END_CODE = ERR_SQL 195   End If 196   Set objADOErrItem = Nothing 197   '--------------------------------------------------------' 198   'INSERT失敗処理 199   '--------------------------------------------------------' 200   objCsvDataFile.Close 201   objADO.RollbackTrans 202   Call NgInsert( objDataFile.Path ) 203   ExitProc ( END_CODE ) 204   objCMD.Close 205   Set objCMD = Nothing 206   Exit Do 207  End If 208  Set objCMD = Nothing 209  ' CSV読込カウンタ 210  cntLoop = cntLoop + 1 211 Next : Loop 212 213 If END_CODE = 0 Then 214  '--------------------------------------------------------' 215  'INSERT成功処理 216  '--------------------------------------------------------' 217  objCsvDataFile.Close 218  objADO.CommitTrans 219  Call OkInsert( objDataFile.Path ) 220 End If 221 222 ' 処理ファイル数カウンタ 223 cntDataFile = cntDataFile + 1 224 objIniFile.Close 225 226 'CSVファイル挿入のため、トランザクション開始 227 objADO.BeginTrans 228 229Next : Next 230 231~ログを出力後、オブジェクトをClose、破棄し、終了コードをBatchに返す~ 232 233'--------------------------------------------------------------------- 234' エラー処理関数 235'--------------------------------------------------------------------- 236~追記~ 237Sub ExitProc(END_CODE) 238 'データベースを閉じる 239 objCMD.Close 240 objCsvDataFile.Close 241 If CONTROL_FLAG = 0 Then 242  objADO.Close 243  Set objDataFile = Nothing 244  Set objDataFolder = Nothing 245  Set objFSO = Nothing 246  Set objLogFile = Nothing 247  Set objCsvDataFile = Nothing 248  WScript.Quit(END_CODE) 249 End If 250End Sub 251'--------------------------------------------------------------------- 252' INSERT成功処理 253'--------------------------------------------------------------------- 254~CSVファイルリネーム処理~ 255'--------------------------------------------------------------------- 256' INSERT失敗処理 257'--------------------------------------------------------------------- 258~CSVファイルリネーム処理~ 259'--------------------------------------------------------------------- 260' INSERTSQLと、CSVデータの項目件数一致判定処理 261'--------------------------------------------------------------------- 262~一致していたら、Trueを返す~ 263'--------------------------------------------------------------------- 264' データタイプ判定処理 265' iType : Oracleテーブル定義の型情報 266'--------------------------------------------------------------------- 267Public Function getColumnType ( iType ) 268 269Dim retVal 270 271Select Case iType 272Case "VARCHAR2" 273retVal = adVarChar 274 275Case "NUMBER" 276retVal = adNumeric 277 278Case "BINARY_FLOAT" 279retVal = adDouble 280 281Case "BINARY_DOUBLE" 282retVal = adDouble 283 284Case Else 285retVal = adVarChar 286End Select 287 288getColumnType = retVal 289 290End Function 291

試したこと

現在は、上記ソースの①の部分でIf文をかませてエラーを発生させるよう誘導していますが、
なんでNullが入ってしまうのか原因がわからないので、再度投稿しています。

vbs

1'数字項目に文字が入っていた場合 2If columnType = adNumeric And Not IsNumeric(Trim(aryCsvResult(paramNum))) And Not Trim(aryCsvResult(paramNum)) = "" Then 3 'error処理 4 'エラーメッセージ出力 5 END_CODE = ERR_SQL 6 Exit Do 7End If 8

補足情報(FW/ツールのバージョンなど)

設定ファイルに下記のようにInsert文の雛形をおいています。

sql

1CSVファイル名=INSERT INTO "テーブル名" ("カラム名1","カラム名2",) values (?,?,)

cmdファイルをたたいてVBSを呼び出しています。
設定ファイル内のCSVファイル名とディレクトリ内のCSVファイルを比較して、マッチングしたらInsert文を引っ張ってきて、CSVファイルの読込を始め、上記問題のソースでパラメータに実際のデータをセットしています。

Windows7
OracleDB 11g
VBScript
Batch

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

非常に見辛いコードです。可能ならリファクタリングをお奨めします。

処理の概要は以下のような事だと解釈しました。
1.oracleのディクショナリのUSER_TAB_COLUMNSを取得し、CSVの項目のデータ属性のチェックを行う。
2.CSVデータはADOのパラメータ経由で、パラメータを埋め込んだ雛形のINSERT文により登録する。

で質問にある、

データ型がNumberのカラムに空白でない文字列を指定すると、エラーを取得したい。
データ型がNumberのカラムに空白でない文字列を指定すると、エラーが発生せず、正常終了してしまう。
テーブルを確認すると(Null)が入ってしまっている。

についてですが、エラー発生時は中断したいのですよね?
少なくとも属性のチェックでNGの場合は、パラメータの追加は中断されるロジックのようです。
その状況でも、INSERTが行われたというのは、ADOでは参照しているパラメータが登録されていなくても、
エラーとはならず、Null等に置き換えて処理されていると思われます。

中断したいのなら、objCMD.Execute()は実行せずに外側のループもexitする必要があります。
※2重ループをForとDoのように使い分けると一度のexitで済みます。

後は、RollbackTransも明示的にした方が良いのではないかと思います。

投稿2018/03/30 04:20

sazi

総合スコア25032

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Kiri_Tanpo

2018/03/30 04:44

RollbackTransは省略したソースの中で行っています。記述していませんでしたが、cmdからvbsを呼び出す際に、強制終了以外のエラーが発生した時に中断するのか続行するのか、フラグを投げる仕様になっているのと、その際各CSVファイルのリネーム処理が発生するため、①に追加した条件分岐ではExit DoでCSVファイル内のデータのループから抜けるようになっています。 私の質問の仕方がまずかったのですが、今回は誰がNullに置き換えているのか、知りたいのです。ADODBもしくはOracleDBのどちらかの仕様だとは思うのですが、情報をネットから拾えなかったので知っている方がいればと思って質問しました。
sazi

2018/03/30 04:51

どちらで行っているかは、雛形のSQLの内容が不明だと断言はできませんが、この作りだと、ORACLEは発行されたSQLを実行しているだけなので、このVBSの処理でしょうね。 エラーのCSVはスキップする仕様ですか?だとすると再実行時の考慮がされているロジックも省略されているんですね。
sazi

2018/03/30 04:54

SQLトレースログ等で、発行されているSQLの内容は確認されていますか?
sazi

2018/03/30 05:08

雛形のSQLでは、 insert into xxx (:1,:2,,,,) のような単純な記述ですか? バインド変数が残っているというような事はないですか?
sazi

2018/03/30 05:12

あ、OLEDB経由だからパラメータは「?」ですね?
Kiri_Tanpo

2018/03/30 05:30

雛形のSQLはINSERT INTO "テーブル名" ("カラム名1","カラム名2",‥) values (?,?,‥)の形以外存在しません。AccessからOracleにDBのデータ移行が目的なので、Insert文は全カラムデータを全テーブルに移行する形です。
Kiri_Tanpo

2018/03/30 05:31

文字量が多すぎて、全て入りきれなかったので、省略している部分は多いかと思います。
sazi

2018/03/30 06:11 編集

やはりORACLEはこの問題に関して介在しようが無いですね。 回答の本文でも指摘していますが、エラーがあるとパラメータを追加していませんよね。 不足しているパラメータを参照したとき?が省略されて、 values(,,)のように区切りだけのSQLが生成されているものと思われます。 ※SQLトレースで確認すれば明らかになるでしょう。
sazi

2018/03/30 06:09

>AccessからOracleにDBのデータ移行が目的 質問とは全く関係なくなってしまいますが、VBSじゃなくて、oracle data pumpかaccessVBAで行った方が効率よさそうですけどね。
Kiri_Tanpo

2018/03/30 07:03

エラーというのは、数値項目に文字が入っていた場合を指していますか?
sazi

2018/03/30 07:05

質問文にあるエラーという表現は①の部分しかありませんよね?
Kiri_Tanpo

2018/03/30 07:09

VBSで行っているのは仕様設計で決まってしまっていたので、出来れば私も使いたくはなかったのですが。。 数値項目なのに文字列を入れようとして何も入らず If Trim(aryCsvResult(paramNum)) = "" Then  param.Value = null Else  param.Value = aryCsvResult(paramNum) End If ここで空文字判定通ってNullになったという感じですかね。 ありがとうございました。
sazi

2018/03/30 07:15

いえ、質問でロジックが分かれているので、纏めた場合のロジックを想定しての回答ですが、①の部分でexit doするとパラメータの追加は行われない処理になっているように見えるんですけど。
Kiri_Tanpo

2018/03/30 07:21

すいません、①に追記した処理はそもそも数値に文字が入っていたら該当CSVファイルをロールバックして読み飛ばす処理なので、パラメータの追加もSQLの実行も行わないようになっていて、動作確認も出来ているロジックになります。わかりづらくてすいませんでした。
sazi

2018/03/30 07:29 編集

VBSは、先にエクセルとかaccessで作成してデバッグした方が効率良いかと思います。 上記のような個所はステップ実行で確認できますし。
Kiri_Tanpo

2018/03/30 07:47

ありがとうございます! 今回の分は既に自分の手元から離れてしまっているので、今後の参考にさせていただきます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.53%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

同じタグがついた質問を見る

Oracle Database 11g

Oracle DatabaseはRDBMSの商品です。具体的な発売商品として知られているのが、 Oracle9i、Oracle10g、Oracle 11gとOracle 12cです。

VBScript

VBScript(Visual Basic Scripting Edition)はMicrosftが開発したスクリプト言語であり、Visual Basicのサブセットです。