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

Question

前提・実現したいこと

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

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

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

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

該当のソースコード

'バッチホームディレクトリパス
Dim BATCH_HOME
Dim DATA_DIR
Dim INSERT_INI
Dim LOG_FILE
Dim CONTROL_FLAG
Dim COMPLETED_DIR
Dim END_CODE'終了コード

'DB名
Private Const DATABASE = "XE"
'ユーザー名
Private Const DB_UID = "scott"
'パスワード
Private Const DB_PWD = "tiger;"

'DB接続関係の定数
Private Const adDouble       = 5
Private Const adDBDate       = 7
Private Const adNumeric      = 131
Private Const adVarChar      = 200
Private Const adCmdText      = 1
Private Const adParamInput   = 1
Private Const adUseClient    = 3
Private Const adOpenStatic   = 3

～定数でエラーコード番号をセット～

Private Const TABLESQL = "select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from user_tab_columns where table_name = ? Order by COLUMN_ID"

'共通変数　宣言
Public objADO
Public objFSO
Public objLogFile
Public objIniFile
Public objCsvDataFile

'cmdからの引数を受け取る
～ファイルパス取得～

'終了コード初期化
END_CODE = 0

'---------------------------------------------------------------------
' 日時設定
'---------------------------------------------------------------------
～～
'---------------------------------------------------------------------
' ログファイルオープン
'---------------------------------------------------------------------
～～
'------------------------------------------------------------------'
' Iniファイル確認
'------------------------------------------------------------------'
～Iniファイルが存在しなければ、処理を終了する～
'------------------------------------------------------------------'
' DBコネクションの確立
'------------------------------------------------------------------'
Dim Connection
Dim objADODbConnErr

Set objADO = CreateObject("ADODB.Connection")
Connection = "Provider=OraOLEDB.Oracle;Data Source=" & DATABASE & ";User ID=" & DB_UID & ";Password=" & DB_PWD

'タイムアウト時間　無制限
objADO.ConnectionTimeout = 0
objADO.Open Connection

'DBコネクションが取得できない場合
～エラーログ出力～

'CSVファイル挿入のため、トランザクション開始
objADO.BeginTrans

'---------------------------------------------------------------------
' DATAフォルダ内のファイル情報取得（ファイル数分繰返し）
'---------------------------------------------------------------------
Dim objDataFile
Dim cntDataFile'処理ファイル数カウンタ
Dim strIniLine
Dim strInsertSql

cntDataFile = 0
Dim iniReadContinue

For Each objDataFile In objFSO.GetFolder(DATA_DIR).Files : For iniReadContinue = 1 To 1
　' ファイルの拡張子がCSV以外だったら、次のファイル情報へContinue
　～～
　' Csvファイル処理開始ログ出力
　～～
　'---------------------------------------------------------------------
　' Iniファイルデータ情報取得（Iniファイルのレコード数分繰返し）
　'---------------------------------------------------------------------
　Set objIniFile = objFSO.OpenTextFile(INSERT_INI)
　Do Until objIniFile.AtEndOfStream
　　'------------------------------------------------------------'
　　' Iniファイル定義情報とCSVファイル名のマッチング
　　'------------------------------------------------------------'
　　strIniLine = objIniFile.ReadLine
　　strInsertSql = Mid(strIniLine, Instr(strIniLine, "=") + 1)
　　'Csvファイル名がIniファイル内のCSVファイル名と一致しない場合、次のIniファイル行へContinue
　　If Left(strIniLine, Instr(strIniLine, "=") - 1) & ".csv" = objDataFile.Name Then
　　　Exit Do
　　End If
　　'一致しなかった場合、INSERT分を空白で初期化する
　　strInsertSql = ""
　Loop
　objIniFile.close

　'SQL文が空白の場合、INIからSQL文を取得できなかったため、エラーとする
　～～
　'------------------------------------------------------------'
　' Insert対象テーブル情報取得
　'------------------------------------------------------------'
　Dim objCMD
　Dim objRs
　Dim objADOErrItem
　Set objCMD = CreateObject("ADODB.Command")
　objCMD.ActiveConnection = objADO
　objCMD.CommandText = TABLESQL
　objCMD.CommandType = adCmdText
　Dim param
　Set param = objCMD.CreateParameter(0, adVarChar, adParamInput, 30)
　param.Value = Left(strIniLine, Instr(strIniLine, "=") - 1)
　objCMD.Parameters.Append param
　Set objRs = CreateObject("ADODB.Recordset")
　objRs.CursorLocation = adUseClient
　objRs.CursorType = adOpenStatic
　objRs.Open objCMD
　'DBに対象テーブルが存在しなかった場合のエラー処理
　～～
　'------------------------------------------------------------'
　' CSVファイルデータ情報取得（CSVファイルのレコード数分繰返し）
　'------------------------------------------------------------'
　Dim csvReadContinue
　Set objCsvDataFile = objFSO.OpenTextFile(objDataFile.Path, 1, False)
　Dim cntLoop
　cntLoop = 0
　END_CODE = 0

　Do Until objCsvDataFile.AtEndOfStream  : For csvReadContinue = 1 To 1
　　Dim tmp
　　Dim aryCsvResult
　　tmp = objCsvDataFile.ReadLine
　　aryCsvResult = Split(tmp, ",")

　　' CSV読込カウンタが0(1行目)の場合、ヘッダー行の為、次のCSVデータ行へContinue
　　～～

　　'CSVデータと、SQL文の項目一致チェック
　　～～
　　'------------------------------------------------------------'
　　' CSVのデータ内容をSQLにバインドし、SQL実行
　　'------------------------------------------------------------'
　　Set objCMD = CreateObject("ADODB.Command")
　　objCMD.ActiveConnection = objADO
　　objCMD.CommandText = strInsertSql
　　objCMD.CommandType = 1
　　Dim paramNum
　　paramNum = 0
　　objRs.MoveFirst

　　Do Until objRs.EOF
　　　Dim columnType
　　　columnType = getColumnType(objRs.Fields("DATA_TYPE").Value)
　　　Set param = objCMD.CreateParameter(paramNum, columnType, adParamInput, objRs.Fields("DATA_LENGTH").Value )

～①～

　　　If Trim(aryCsvResult(paramNum)) = "" Then
　　　　param.Value = null
　　　Else
　　　　param.Value = aryCsvResult(paramNum)
　　　End If
　　　objCMD.Parameters.Append param
　　　paramNum = paramNum + 1
　　　objRs.MoveNext
　　Loop
　　Err.Number = 0
　　objCMD.Execute()
　　'------------------------------------------------------------'
　　' SQL実行時エラーの確認
　　'------------------------------------------------------------'
～追記～
　　If Err.Number <> 0 Then
　　　'--------------------------------------------------------'
　　　'SQLエラー情報のログ出力
　　　'--------------------------------------------------------'
　　　Set objADOErrItem = objADO.Errors.Item(0)
　　　～エラーログ出力～
　　　If objADOErrItem.NativeError = "ORA-00060" Then
　　　　END_CODE = ERR_DeadLouk
　　　Else
　　　　END_CODE = ERR_SQL
　　　End If
　　　Set objADOErrItem = Nothing
　　　'--------------------------------------------------------'
　　　'INSERT失敗処理
　　　'--------------------------------------------------------'
　　　objCsvDataFile.Close
　　　objADO.RollbackTrans
　　　Call NgInsert( objDataFile.Path )
　　　ExitProc ( END_CODE )
　　　objCMD.Close
　　　Set objCMD = Nothing
　　　Exit Do
　　End If
　　Set objCMD = Nothing
　　' CSV読込カウンタ
　　cntLoop = cntLoop + 1
　Next : Loop

　If END_CODE = 0 Then
　　'--------------------------------------------------------'
　　'INSERT成功処理
　　'--------------------------------------------------------'
　　objCsvDataFile.Close
　　objADO.CommitTrans
　　Call OkInsert( objDataFile.Path )
　End If

　' 処理ファイル数カウンタ
　cntDataFile = cntDataFile + 1
　objIniFile.Close

　'CSVファイル挿入のため、トランザクション開始
　objADO.BeginTrans

Next : Next

～ログを出力後、オブジェクトをClose、破棄し、終了コードをBatchに返す～

'---------------------------------------------------------------------
' エラー処理関数
'---------------------------------------------------------------------
～追記～
Sub ExitProc(END_CODE)
　'データベースを閉じる
　objCMD.Close
　objCsvDataFile.Close
　If CONTROL_FLAG = 0 Then
　　objADO.Close
　　Set objDataFile    = Nothing
　　Set objDataFolder  = Nothing
　　Set objFSO         = Nothing
　　Set objLogFile     = Nothing
　　Set objCsvDataFile = Nothing
　　WScript.Quit(END_CODE)
　End If
End Sub
'---------------------------------------------------------------------
' INSERT成功処理
'---------------------------------------------------------------------
～CSVファイルリネーム処理～
'---------------------------------------------------------------------
' INSERT失敗処理
'---------------------------------------------------------------------
～CSVファイルリネーム処理～
'---------------------------------------------------------------------
' INSERTSQLと、CSVデータの項目件数一致判定処理
'---------------------------------------------------------------------
～一致していたら、Trueを返す～
'---------------------------------------------------------------------
' データタイプ判定処理
' iType : Oracleテーブル定義の型情報
'---------------------------------------------------------------------
Public Function getColumnType ( iType )

Dim retVal

Select Case iType
Case "VARCHAR2"
retVal = adVarChar

Case "NUMBER"
retVal = adNumeric

Case "BINARY_FLOAT"
retVal = adDouble

Case "BINARY_DOUBLE"
retVal = adDouble

Case Else
retVal = adVarChar
End Select

getColumnType = retVal

End Function

試したこと

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

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

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

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

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

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

Windows7
OracleDB 11g
VBScript
Batch

Answer 1

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

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

で質問にある、

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

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

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

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

Comments

[Kiri_Tanpo]

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

[sazi]

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

[sazi]

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

[sazi]

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

[sazi]

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

[Kiri_Tanpo]

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

[Kiri_Tanpo]

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

[sazi]

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

[sazi]

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

[Kiri_Tanpo]

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

[sazi]

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

[Kiri_Tanpo]

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

[sazi]

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

[Kiri_Tanpo]

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

[sazi]

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

[sazi]

上記は割とお手軽ですけど、それなりにソース書き換えないとだめなので、以下も紹介しておきます。
http://algorhythnn.jp/blg/2013/11/06/vbs-debug-use-visual_studio2010/
http://d.hatena.ne.jp/kkz_tech/20131210/1386660690
※使ったことはありません。

[Kiri_Tanpo]

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