Oracle-EBS-SQL Loader個人總結(jié)-Oracle EBS技術(shù)文檔

@Ryoulong.peng@REFDocTitleOracleERP技術(shù)實踐October,17,2015STYLEREFHD2DocumentControlIfSection1>1“DateAuthorVersionChangeReference17,Oct,2015Youlong.Peng1.0Create19,Oct,2015Youlong.Peng1.5Update24,Oct,2015Youlong.Peng2.0Update31,Oct,2015Youlong.Peng3.0Update01,Nov,2015Youlong.Peng4.0UpdateReviewersNamePositionDistributionCopyNo.NameLocationNoteToHolders:ContentsTOC\o"2-3"DocumentControl iiPreface 11. SQL*Loader簡介 21.1. 簡述 21.2. 特點 21.3. 本質(zhì) 21.4. 加載模式 32. SQL*Loader原理及使用 42.1. 原理 42.2. 使用命令 53. 控制文件 73.1. 簡介 73.2. 編寫方式 73.3. 命令解釋&參數(shù)釋義 74. 案例講解 114.1. 有控制文件 114.2. 多個文件加載 174.3. 常見需求及場景 215. 調(diào)試及日志 275.1. 問題所在 275.2. 如何應(yīng)對 286. 性能問題 297. 總結(jié) 307.1. 使用總結(jié) 307.2. 對比通用導(dǎo)入 308. OpenandClosedIssuesforthisDeliverable 31OpenIssues 31ClosedIssues 31PAGE10Preface參考資料：/database/121/SUTIL/GUID-A07484AF-4EE8-432F-A587-5E158FF0B4C1.htm#SUTIL978《OracleDatabaseOnlineDocumentation》/25103190/viewspace-739765/《SQL*Loader詳解》/database/201308/235325.html《SQL*Loader詳細使用教程》SQL*Loader簡介開篇先了解一下，什么是SQL*Loader？有什么用？為什么要用？簡述SQL*Loader是Oracle數(shù)據(jù)庫用于將數(shù)據(jù)從外部文件導(dǎo)入到Oracle數(shù)據(jù)庫的工具。用于高性能數(shù)據(jù)加載，數(shù)據(jù)可以從任何文本文件加載，并插入到數(shù)據(jù)庫。它和DB2的Load工具相似，但是提供了很多參數(shù)以供選擇，支持變化的加載模式（負(fù)載格式、選擇性加載和多表荷載），可選的加載及多表加載;它可以把一些以文本格式存放的數(shù)據(jù)順利的導(dǎo)入到oracle數(shù)據(jù)庫中，是一種在不同數(shù)據(jù)庫之間進行數(shù)據(jù)遷移的非常方便而且通用的工具。缺點就速度比較慢，另外對blob等類型的數(shù)據(jù)就有點麻煩了。特點能通過網(wǎng)絡(luò)加載位于不同數(shù)據(jù)庫系統(tǒng)的數(shù)據(jù)文件；加載數(shù)據(jù)的時候可以指定數(shù)據(jù)的字符集。選擇性地加載的數(shù)據(jù)，即只加載數(shù)據(jù)文件中指定記錄。生成復(fù)雜的錯誤報告，極大地幫助排除故障。多個不同數(shù)據(jù)類型文件能通過相同的負(fù)載會話期間加載，且多個數(shù)據(jù)文件可以加載到多個表；可加載固定格式，自由定界以及可度長格式的數(shù)據(jù)使用操作系統(tǒng)的文件系統(tǒng)訪問數(shù)據(jù)文件。從磁盤、磁帶或命名的管道加載數(shù)據(jù)。任意復(fù)雜的對象-關(guān)系數(shù)據(jù)加載。為加載Lob和集合使用次要數(shù)據(jù)文件。使用傳統(tǒng)的、直接的路徑或外部表加載?？梢詫⑽募械恼妥址詣愚D(zhuǎn)成壓縮十進制并裝入列表中。SQL*Loader有很多特點，不過到目前為止項目上用到的程序，都是基礎(chǔ)的數(shù)據(jù)導(dǎo)入，能應(yīng)用到也只有前五條。更加復(fù)雜的功能得應(yīng)用于很復(fù)雜的數(shù)據(jù)加載。本質(zhì)SQL*Loader在使用過程中很簡單輕便，本質(zhì)就是命令執(zhí)行的過程：sqlldrscott/tigercontrol=ulcase1.ctllog=ulcase1.log只是一些控制文件等參數(shù)可以靈活控制，如案例sqlldrapps@apps/passwordcontrol=loader.ctl或sqlldrapps/apps@servercontrol=loader.ctlloader.ctl文件如下：loaddatainfile'c:\data\pyldata.csv'intotablecux_employee_testfieldsterminatedby","optionallyenclosedby'"' (empno,empname,sal,deptno)pyldata.csv文件如下：10001,"ScottTiger",1000,4010002,"FrankNaude",500,20總之，與我常用的通用導(dǎo)入也有類似之處，就是獲得數(shù)據(jù)文件，解讀數(shù)據(jù)文件數(shù)據(jù)，執(zhí)行insert/infileinto。加載模式傳統(tǒng)路徑加載：等同于insert語句直接路徑加載：繞過SGA，把數(shù)據(jù)直接導(dǎo)入高水位線(HWM)以上，可設(shè)置并行加載，性能比傳統(tǒng)路徑加載更高，但限制也更多外部表加載：先在數(shù)據(jù)文件上創(chuàng)建一個外部表，然后再把數(shù)據(jù)從外部表insert到目標(biāo)表中SQL*Loader原理及使用原理2.1.1、兩種加載方式選擇SQL*Loader的數(shù)據(jù)加載可選擇是否有控制文件（ControlFile）兩種。1）無控制文件：SQL*Loader程序主體由一個文件組成，該文件的內(nèi)容要包括指定加載的數(shù)據(jù)源文件、加載數(shù)據(jù)命令、數(shù)據(jù)加載相關(guān)的控制命令和驗證邏輯。但是，如果數(shù)據(jù)加載的邏輯很復(fù)雜是不建議用這種方式的，否則該文件會很龐大，而且邏輯代碼變得很臃腫。這樣的話就違背了我使用SQLLoader簡單的初衷。因此，無控制文件的加載方式適合于一些很簡單的數(shù)據(jù)問價加載。2）控制文件：控制文件是用一種SQL*Loader語言（其實就是一些SQL*Loader自身能識別的命令）寫的文本文件，這個文本文件能被SQL*LOADER識別。用于控制數(shù)據(jù)導(dǎo)入的行為方式（最重要的文件）。并且分析和解釋這些數(shù)據(jù)，根據(jù)命令做相關(guān)操作?？刂莆募扇齻€部分組成：a)一些變量，全局選項，行，跳過的記錄數(shù)等會話信息；b)INFILE子句指定的輸入數(shù)據(jù)；(如果使用sqlldr的data參數(shù)，這里用*,"INFILE*")【通俗的說，也就是table/column信息】c)數(shù)據(jù)特性說明。一般第二種方式更實用，因為設(shè)計靈活，模板和數(shù)據(jù)分離。該圖引用自[/database/201308/235325.html]從圖中可以看出，我把Data_Source和Ctl_File傳入SQL*Loader，輸出的結(jié)果包括日志，壞文件（錯誤的文件），丟棄文件（被拒絕的記錄）和導(dǎo)入成功的數(shù)據(jù)四部分。參數(shù)文件（可選）：可以把參數(shù)直接寫在控制文件里，也可以單獨寫一個參數(shù)文件。（參數(shù)文件的意義：如果有多個導(dǎo)入程序而且他們部分參數(shù)都相同時，可以不必重復(fù)的在各個程序中都輸入相同的參數(shù)，定義成參數(shù)文件調(diào)用即可，參數(shù)文件的調(diào)用方法：在控制文件的OPTIONS字句里調(diào)用，但是，控制文件中命令行參數(shù)的優(yōu)先級最高，可以覆蓋參數(shù)文件里的參數(shù)配置）數(shù)據(jù)文件(DataFiles)：放置源數(shù)據(jù)的平面文件(可以一次性導(dǎo)入多個數(shù)據(jù)文件）。壞文件(BadFile)：在數(shù)據(jù)加載時，把無法正確加載的數(shù)據(jù)放入錯誤文件中（比如數(shù)據(jù)格式、數(shù)據(jù)類型問題等）。丟棄文件（可選）(DiscardFile)：有些數(shù)據(jù)，雖然數(shù)據(jù)格式、數(shù)據(jù)類型沒有問題，但它被邏輯條件過濾掉了（由控制文件WHEN設(shè)置），會被放入丟失文件。日志文件(LogFile)：記錄SQL*Loader的數(shù)據(jù)加載過程。使用命令2.2.1、命令SQL*Loader可以在Windows系統(tǒng)下使用，也可以在Unix系統(tǒng)下使用，大同小異sqlldr/sqlloadkeyword=value[,keyword=value,…]如果忘記了某些參數(shù)的含義時，也可以通過這個方式快速查詢：輸入sqlldr，后面不接任何參數(shù)，將顯示所有的命令行參數(shù)的簡單描述及其默認(rèn)值2.2.2、有效關(guān)鍵字（即給sql*loader命令的參數(shù)），基本后面的不怎么用，作為參考即可；且一些常用常見的命令在此節(jié)只做基本解釋，后續(xù)案例中看詳細用法。注：以下有效關(guān)鍵字可以定義在controlfile中的option里Userid--Oracleusername/passwordControl--控制文件Log--加載日志Bad--壞數(shù)據(jù)文件，如果有的數(shù)據(jù)沒有被處理，將會出現(xiàn)在該參數(shù)所設(shè)的目錄下Data--數(shù)據(jù)文件，等同于控制文件里的INFILE，可以指定多個數(shù)據(jù)文件Discard--丟棄的數(shù)據(jù)文件discardmax--允許丟棄數(shù)據(jù)的最大值(全部默認(rèn))skip--指定忽略最開始的多少條記錄(默認(rèn)0)load--最多允許多少條記錄可以被加載(全部默認(rèn))errors–允許的錯誤記錄數(shù)，如果超過該數(shù)字，將停止加載(默認(rèn)50)rows--Numberofrowsinconventionalpathbindarrayorbetweendirectpathdatasaves（每次提交的記錄數(shù)，默認(rèn):常規(guī)路徑64,所有直接路徑）bindsize--Sizeofconventionalpathbindarrayinbytes(默認(rèn)256000)（每次提交記錄的緩沖區(qū)的大小(字節(jié)為單位，默認(rèn)256000)）silent--禁止輸出信息(header,feedback,errors,discards,partitions)direct--使用直通路徑方式導(dǎo)入(默認(rèn)FALSE)parfile--數(shù)文件名（.par）（只有在非常復(fù)雜的導(dǎo)入/通用/或者參數(shù)需要保密時才需要）parallel--并行導(dǎo)入(默認(rèn)FALSE)file--該參數(shù)只在直接路徑并行加載時有用--與bindsize成對使用，其中較小者會自動調(diào)整到較大者。sqlldr先計算單條記錄長度，乘以rows，如小于bindsize，不會試圖擴張rows以填充bindsize；如超出，則以bindsize為準(zhǔn)。external_table--是否使用外部表加載方式NOT_USED,GENERATE_ONLY,EXECUTE(默認(rèn)NOT_USED)columnarrayrows--Numberofrowsfordirectpathcolumnarray(默認(rèn)5000)streamsize--Sizeofdirectpathstreambufferinbytes(默認(rèn)256000)skip_index_maintenance--true表示在直接路徑加載過程中不維護索引，導(dǎo)致索引狀態(tài)變?yōu)閡nusablemultithreading--usemultithreadingindirectpathresumable--enableordisableresumableforcurrentsession(默認(rèn)FALSE)resumable_name--textstringtohelpidentifyresumablestatementresumable_timeout--waittime(inseconds)forRESUMABLE(默認(rèn)7200)date_cache--size(inentries)ofdateconversioncache(默認(rèn)1000)2.2.3、需要注意的點在SQL*Loader命令中，有兩種方式可以指定命令行參數(shù)：通過位置或者通過關(guān)鍵字。通過位置的例子：'sqlldrscott/tigertest.ctl'，通過關(guān)鍵字的例子：'sqlldruserid=scott/tigercontrol=testlog=test.log'；但是不要前面使用關(guān)鍵字指定后面通過位置制定的混合方式；為清楚起見最好所有命令行參數(shù)都用關(guān)鍵字指定?？刂莆募竟?jié)主要介紹控制文件的意義，編寫，參數(shù)配置簡介控制文件是SQL*Loader里最重要的文件，它定義數(shù)據(jù)文件的位置、數(shù)據(jù)的格式、以及配置數(shù)據(jù)加載過程的行為。所有的命令行參數(shù)都可以在控制文件里配置。編寫方式圍繞一個完整的小例子來講解。注：該段代碼完成的是加載/prod/temp目錄下的CUXKATASHIKI.csv文件中2015年的數(shù)據(jù)到數(shù)據(jù)庫cux_oa_weeks_iface表中OPTIONS(skip=1,BINDSIZE=100000,SILENT=(ERRORS,FEEDBACK))loaddatainfile/prod/temp/CUXKATASHIKI.csvbadfiledata.baddiscardfiledata.dscappendintotablecux_oa_weeks_ifacewhereyear=‘2015’trailingnullcolsfieldsterminatedby","optionallyenclosedby'"'(text,company_code,year,month,act_bud,mgnt_number,katashiki_code,katashiki_attribute,katashiki_attribute_desc,import_idsequence(max,1))BEGINDATA測試數(shù)據(jù),01,2015,10,test,test,test,test,test,-993.3節(jié)是對該例的詳細釋義命令解釋&參數(shù)釋義3.3.1、開始初始化參數(shù)可以使用OPTIONS在控制文件里配置命令行參數(shù)，如下所示：OPTIONS(skip=1,BINDSIZE=100000,SILENT=(ERRORS,FEEDBACK))是可選的參數(shù)列表，<2.2.2>章節(jié)的參數(shù)都可在此配置skip=1：要跳過的邏輯記錄的數(shù)量（csv文件第一行是表頭，調(diào)過一行不導(dǎo)入表頭）BINDSIZE=100000:每次提交記錄的緩沖區(qū)的大小是111620字節(jié)SILENT：在運行期間禁止顯示的消息，可選值為(header,feedback,errors,discards,partitions)3.3.2、配置數(shù)據(jù)文件loaddata：表示開始加載數(shù)據(jù)infile/prod/temp/CUXKATASHIKI.csv：讀取數(shù)據(jù)文件/prod/temp/CUXKATASHIKI.csv利用INFILE可以配置一個或多個數(shù)據(jù)文件，如果配置多個數(shù)據(jù)文件，還可以為每個數(shù)據(jù)文件分別配置壞文件和丟棄文件，如：INFILEmydat1.datBADFILEmydat1.badDISCARDFILEmydat1.disINFILEmydat2.datINFILEmydat3.datDISCARDFILEmydat3.disINFILEmydat4.datDISCARDMAX100如果數(shù)據(jù)包含在控制文件里，可以用*表示，如：INFILE*并用BEGINDATA來標(biāo)識哪些是數(shù)據(jù)：BEGINDATAdatabadfiledata.bad：表示壞文件存放的文件名（以下三種情況可能會導(dǎo)致壞文件的出現(xiàn)：a/INSERT時發(fā)生Oracle錯誤b/數(shù)據(jù)加載時格式有誤c/違反約束）discardfiledata.dsc：丟棄文件寫入的文件名（以下兩種情況可能會導(dǎo)致壞文件的出現(xiàn)：a/記錄不符合WHEN定義的記錄b/加載記錄數(shù)大于DISCARDMAX配置數(shù)將停止加載c/違反約束）3.3.3、選擇加載數(shù)據(jù)模式（以下是四種數(shù)據(jù)加載模式）APPEND原先的表有數(shù)據(jù)就加在后面——也正是此處所選模式INSERT裝載空表如果原先的表有數(shù)據(jù)sqlloader會停止默認(rèn)值REPLACE原先的表有數(shù)據(jù)原先的數(shù)據(jù)會全部刪除(表級別數(shù)據(jù)的替換)TRUNCATE指定的內(nèi)容和replace的相同會用truncate語句刪除現(xiàn)存數(shù)據(jù)3.3.4、篩選記錄并導(dǎo)入數(shù)據(jù)庫目標(biāo)表intotablecux_oa_weeks_iface表示導(dǎo)入的目標(biāo)Table是：cux_oa_weeks_iface此處需要注意的是，可以根據(jù)某字段的值來控制，將一份數(shù)據(jù)源插入不同的表中可以根據(jù)第一列的值的不同分別插入兩個不同的表：INTOTABLEdeptWHENrecid=1(recidFILLERPOSITION(1:1)INTEGEREXTERNAL,deptnoPOSITION(3:4)INTEGEREXTERNAL,dnamePOSITION(8:21)CHAR)INTOTABLEempWHENrecid<>1(recidFILLERPOSITION(1:1)INTEGEREXTERNAL,empnoPOSITION(3:6)INTEGEREXTERNAL,enamePOSITION(8:17)CHAR,deptnoPOSITION(19:20)INTEGEREXTERNAL)記錄篩選控制：whereyear=‘2015’（注意，這里存在一個問題：如果文件中數(shù)據(jù)源的列少于表的列數(shù)如何處理？當(dāng)然，SQL*loader已經(jīng)考慮到這樣的情況。我可以在命令中告訴SQL*Loader如果記錄的列少于目標(biāo)表的列，則用NULL代替INTOTABLEdeptTRAILINGNULLCOLS(deptnoCHARTERMINATEDBY"",dnameCHARTERMINATEDBYWHITESPACE,locCHARTERMINATEDBYWHITESPACE)）3.3.5、分隔符定義（列與列分隔符）1.TERMINATEDBY2.ENCLOSEDBYfieldsterminatedby","optionallyenclosedby'"'以逗號分割，或雙引號3.3.6、數(shù)據(jù)字段配置字段屬性的設(shè)置包括：位置、數(shù)據(jù)類型、條件、分隔符等。數(shù)據(jù)字段的偏移量（POSITION）指示從哪個字符開始，到哪個字符結(jié)束，如：enamePOSITION(1:20)CHARempnoPOSITION(22-26)INTEGEREXTERNALallowPOSITION(*+2)INTEGEREXTERNALTERMINATEDBY"/"上面的*是值上一個的末尾+偏移量1，也就是從27。解析字段（FILLER）解析字段并不真正導(dǎo)入到表中，而是作為邏輯判斷的條件。數(shù)據(jù)類型默認(rèn)的數(shù)據(jù)類型為CHAR，INTERGER表示二進制數(shù)據(jù)，INTERGEREXTERNAL表示字符數(shù)據(jù)INTERGET(n),SMALLINT,FLOAT,DOUBLE,BYTEINT,ZONED,DECIMAL,VARCHAR,CHAR,DatetimeandInterval案例講解由于一般開發(fā)我都不會直接在命令行敲一個命令去導(dǎo)入數(shù)據(jù)，所以，無控制文件的SQL*Loader案例在此就不做講解了，它其實本身就是一個命令，并且在參數(shù)里面聲明數(shù)據(jù)源和數(shù)據(jù)導(dǎo)向。有控制文件4.1.1、案例需求現(xiàn)在XX企業(yè)的外圍OA系統(tǒng)，提供了一份數(shù)據(jù)，這份數(shù)據(jù)以csv的方式保存并且已經(jīng)通過OA系統(tǒng)接口以FTP方式上傳到了ERP系統(tǒng)應(yīng)用服務(wù)器的/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/temp/OADATA目錄下。要求，ERP需要有個程序?qū)⒃撐募械臄?shù)據(jù)導(dǎo)入到系統(tǒng)中“OA系統(tǒng)數(shù)據(jù)周記表”內(nèi)?！敬颂幬覍懙睦又屑僭O(shè)周記表名為CUX_OA_WEEKS_IFACE】.注，數(shù)據(jù)已經(jīng)通過嚴(yán)密審核，無需校驗4.1.2、實現(xiàn)過程及思路1）思路我肯定是先到服務(wù)器指定目錄下，查看該csv文件，發(fā)現(xiàn)數(shù)據(jù)不是很復(fù)雜，假設(shè)有8個列?；跀?shù)據(jù)的簡略，和需求無需校驗，我可以選定，以SQL*Loader的方式來完成數(shù)據(jù)導(dǎo)入，輕便簡潔。在erp系統(tǒng)中，首選通過并發(fā)程序來完成這個請求，因為在整個加載數(shù)據(jù)的過程中，無需人為手工的去獲取該csv文件，也不需要打開加載界面上傳csv文件。（這大概也可以算是與通用導(dǎo)入大不同的一個地方，因為通用導(dǎo)入還是要先獲取csv文件再加載的）數(shù)據(jù)樣式：文本查看（逗號分隔符）2）實現(xiàn)過程編寫一個加載數(shù)據(jù)的.ctl文件，由于我采用的是并發(fā)程序?qū)崿F(xiàn)，所以在該文件中，首先獲取程序相關(guān)參數(shù)，接著讀取目標(biāo)目錄的文件，進行解析，導(dǎo)入到數(shù)據(jù)庫表中。程序文件名CUXOAWDIMP.ctl.查看表結(jié)構(gòu)*腳本中的各列名必須與表結(jié)構(gòu)的列名一一對應(yīng)*此處的執(zhí)行方法必須是“SQL*Loader程序”，且執(zhí)行文件必須在$CUX_TOP/bin目錄下存在，如下圖：*在請求參數(shù)中，傳入要導(dǎo)入的文件路徑和文件名4.1.3、執(zhí)行文件代碼/命令如下無控制文件的導(dǎo)入，數(shù)據(jù)加載的文件中既要包括控制命令，也要包括加載數(shù)據(jù)的命令OPTIONS(skip=1,BINDSIZE=111620)loaddatainfile'&1'badfiledata.baddiscardfiledata.dscappendintotablecux_oa_weeks_ifacefieldsterminatedby","optionallyenclosedby'"'(text,company_code,year,month,act_bud,mgnt_number,katashiki_code,katashiki_attribute,katashiki_attribute_desc,import_idsequence(max,1))命令解釋：1）開始初始化參數(shù)OPTIONS(skip=1,BINDSIZE=111620)是可選的參數(shù)列表skip=1：要跳過的邏輯記錄的數(shù)量（csv文件第一行是表頭，調(diào)過一行不導(dǎo)入表頭）BINDSIZE=111620:每次提交記錄的緩沖區(qū)的大小是111620字節(jié)2）開始加載數(shù)據(jù)loaddata：表示開始加載數(shù)據(jù)infile'&1'：讀取文件，此處&1是接收并發(fā)程序傳入的第一個參數(shù)，因為我在請求的參數(shù)設(shè)置了文件的路徑和文件名badfiledata.bad：表示壞文件存放的文件名discardfiledata.dsc：丟棄文件寫入的文件名3）選擇加載數(shù)據(jù)模式（以下是四種數(shù)據(jù)加載模式）APPEND原先的表有數(shù)據(jù)就加在后面——也正是此處所選模式4）導(dǎo)入數(shù)據(jù)庫目標(biāo)表intotablecux_glo_import_temp表示導(dǎo)入的目標(biāo)Table是：cux_oa_weeks_iface5）如何分割（列與列分隔符）fieldsterminatedby","optionallyenclosedby'"'以逗號分割，類內(nèi)容可包含雙引號6）表的column列表就這樣，沒幾行代碼，就可以完成一份外圍系統(tǒng)的數(shù)據(jù)導(dǎo)入，很輕便的就開發(fā)好了一個接口程序。查看請求日志：+|正在啟動并發(fā)執(zhí)行程序...+變元/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/temp/OADATA/OA_IFACE_DATA01.csvSQL*Loader:Release.0-Productionon2102719:18:342015Copyright(c)1982,2005,Oracle.Allrightsreserved.控制文件:/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/bin/CUXOAWDIMP.ctl數(shù)據(jù)文件:/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/temp/OADATA/OA_IFACE_DATA01.csv錯誤文件:/u02/dev/app/fs_ne/inst/DEV_ebstest/logs/appl/conc/out/o35075019.out廢棄文件:/u02/dev/app/fs_ne/inst/DEV_ebstest/logs/appl/conc/out/o35075019.out:(可廢棄所有記錄)要加載的數(shù):ALL要跳過的數(shù):1允許的錯誤:50綁定數(shù)組:64行,最大111620字節(jié)繼續(xù):未作指定所用路徑:常規(guī)表CUX_OA_WEEKS_IFACE,已加載從每個邏輯記錄插入選項對此表APPEND生效列名位置長度中止包裝數(shù)據(jù)類型OA_HEADER_IDFIRST*,O(")CHARACTERDEPT_ITEMNEXT*,O(")CHARACTERDEPT_NUMBERNEXT*,O(")CHARACTEROA_DEPT_TYPENEXT*,O(")CHARACTERSOURCE_CODENEXT*,O(")CHARACTERDEPT_DATA_TOKENNEXT*,O(")CHARACTERAMOUNTNEXT*,O(")CHARACTERAMOUNT_CNYNEXT*,O(")CHARACTERROWS參數(shù)所用的值已從64更改為54表CUX_OA_WEEKS_IFACE:23行加載成功。由于數(shù)據(jù)錯誤,0行沒有加載。由于所有WHEN子句失敗,0行沒有加載。由于所有字段都為空的,0行沒有加載。為綁定數(shù)組分配的空間:111456字節(jié)(54行)讀取緩沖區(qū)字節(jié)數(shù):1048576跳過的邏輯記錄總數(shù):1讀取的邏輯記錄總數(shù):23拒絕的邏輯記錄總數(shù):0廢棄的邏輯記錄總數(shù):0從2102719:18:342015開始運行在2102719:18:342015處運行結(jié)束經(jīng)過時間為:00:00:00.10CPU時間為:00:00:00.00從請求的日志中可以看到：輸出信息很詳細，我可以清楚的看到數(shù)據(jù)導(dǎo)入的大致流程，而且很簡便的就可以找出輸出文件。導(dǎo)入成功。4.1.4、案例分析小結(jié)在該案例中，其實我可以舉一反三，因為SQL*Loader的可選參數(shù)列表很龐大，所以，即便是遇到另外一些需求，我也可以通過這么一段輕便的代碼來處理；例如源數(shù)據(jù)的字符編碼方式與目標(biāo)目標(biāo)環(huán)境字符編碼方式不一致，又或是所給的不是一個csv文件，是一個以||分割數(shù)據(jù)的txt文本等等。所以在使用的過程中，我要學(xué)會靈活變通，充分利用SQL*Loader的功能。另外，通過這個例子，我可以和通用導(dǎo)入做一個對比，通用導(dǎo)入界面很靈活，但是首先需要獲取到csv文件再上傳。在對于一些數(shù)據(jù)安全性的層面來說，使用這種方式也未嘗不是更好。因此在使用過程中，我需要正確選擇數(shù)據(jù)導(dǎo)入方式。多個文件加載其實通過這個個案例，我大致可以猜到，多個文件加載如何處理了？遇到不同的需求，首選看SQL*Loader提供的參數(shù)和控制方法是否可以實現(xiàn)，而對于多文件的加載，我也是可以配置的。下面繼續(xù)以一個小例子來說明。4.2.1、案例需求由于業(yè)務(wù)擴張，現(xiàn)在OA系統(tǒng)每個周都會向ERP傳文件，且銷售科和宣傳科都會傳文件過來，分別是兩份數(shù)據(jù)，現(xiàn)在需要我將這兩份數(shù)據(jù)導(dǎo)入到cux_oa_notice_iface表中，數(shù)據(jù)放于應(yīng)用服務(wù)器/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/temp/OADATA目錄。4.2.2、實現(xiàn)過程及程序源碼由于前面的詳細概述，從本例就不詳細的對每一個操作進項詳述了，在此例中，我還是使用并發(fā)請求的方式來實現(xiàn)導(dǎo)入，但是我導(dǎo)入是針對多個文件來操作的。導(dǎo)入成功后：以下是案例的.ctl控制文件代碼OPTIONS(skip=2,BINDSIZE=111620)LOADDATAINFILE*--第一個數(shù)據(jù)文件（數(shù)據(jù)源位于控制文件本身內(nèi)）INFILE--第二個數(shù)據(jù)文件'/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/temp/OADATA/OA_SALES_IFACE_DATA.csv'INFILE--第三個數(shù)據(jù)文件'/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/temp/OADATA/OA_NOTICE_IFACE_DATA.csv'BADFILE'sales_and_notice.bad'--壞文件DISCARDFILE'sales_and_notice.dsc'--丟棄文件INTOTABLECUX_OA_NOTICE_IFACEreplace--全表清空的方式導(dǎo)入（先清空表再導(dǎo)入）fieldsterminatedby","optionallyenclosedby'"'(OA_HEADER_ID,DEPT_ITEM,DEPT_NUMBER,OA_DEPT_TYPE,SOURCE_CODE,DEPT_DATA_TOKEN,AMOUNTFILLER,AMOUNT_CNY,ERROR_CODE,IMPORTER)BEGINDATA29064,BEGINDATA測試數(shù)據(jù),13,CASH_ITEM_RANGE,CUX,-,18,18,-1,BOB請求日志如下：控制文件:/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/bin/CUXFILESIMP.ctl共有3個數(shù)據(jù)文件:數(shù)據(jù)文件:/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/bin/CUXFILESIMP.ctl錯誤文件:/u02/dev/app/fs_ne/inst/DEV_ebstest/logs/appl/conc/out/o35075178.out廢棄文件:/u02/dev/app/fs_ne/inst/DEV_ebstest/logs/appl/conc/out/o35075178.out:數(shù)據(jù)文件:/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/temp/OADATA/OA_SALES_IFACE_DATA.csv錯誤文件:/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/bin/OA_SALES_IFACE_DATA.bad數(shù)據(jù)文件:/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/temp/OADATA/OA_NOTICE_IFACE_DATA.csv錯誤文件:sales_and_notice.bad廢棄文件:sales_and_notice.dsc:要加載的數(shù):ALL要跳過的數(shù):2允許的錯誤:50綁定數(shù)組:64行,最大111620字節(jié)繼續(xù):未作指定所用路徑:常規(guī)表CUX_OA_NOTICE_IFACE,已加載從每個邏輯記錄插入選項對此表REPLACE生效列名位置長度中止包裝數(shù)據(jù)類型OA_HEADER_IDFIRST*,O(")CHARACTERDEPT_ITEMNEXT*,O(")CHARACTERDEPT_NUMBERNEXT*,O(")CHARACTEROA_DEPT_TYPENEXT*,O(")CHARACTERSOURCE_CODENEXT*,O(")CHARACTERDEPT_DATA_TOKENNEXT*,O(")CHARACTERAMOUNTNEXT*,O(")CHARACTER(FILLERFIELD)AMOUNT_CNYNEXT*,O(")CHARACTERERROR_CODENEXT*,O(")CHARACTERIMPORTERNEXT*,O(")CHARACTERROWS參數(shù)所用的值已從64更改為48表CUX_OA_NOTICE_IFACE:48行加載成功。由于數(shù)據(jù)錯誤,0行沒有加載。由于所有WHEN子句失敗,0行沒有加載。由于所有字段都為空的,0行沒有加載。為綁定數(shù)組分配的空間:111456字節(jié)(48行)讀取緩沖區(qū)字節(jié)數(shù):1048576跳過的邏輯記錄總數(shù):2讀取的邏輯記錄總數(shù):48拒絕的邏輯記錄總數(shù):0廢棄的邏輯記錄總數(shù):0從2102721:20:402015開始運行在2102721:20:402015處運行結(jié)束從上述兩例中，我可以清楚的看到，其實我從請求日志中是可以很清晰的看出導(dǎo)入數(shù)據(jù)所走流程的，包括從初始化參數(shù)到導(dǎo)入成功，攔截記錄，成功記錄等。常見需求及場景由于案例眾多，情景也有很多，我就不一一列出案例代碼了，列出關(guān)鍵代碼，以供參考，因為本質(zhì)上都是一些控制命令，使用時關(guān)鍵代碼貼到ctl文件中即可。4.3.1、加載可變長度或指定長度的記錄loaddatainfile*replaceintotablecux_slqldr_test_tmp(DATA_SEG1position(02:05)char(4),DATA_SEG2position(08:27)char(20))begindata'The*Sql*loader*Test'1234567890,ABCDEFGHIJKLMNOPQSTUVWXYZjklasdfk,"A,B,C,D,,dafd"導(dǎo)入結(jié)果如下：命令解釋：不管數(shù)據(jù)源有多少列，只按字符位置和長度來篩選數(shù)據(jù)源中的數(shù)據(jù)插入表（兩列）：第一列取第二個字符至第五個字符，長度4第二列取第八個字符至第二十七個字符，長度20。如上述文件中：【1234567890,ABCDEFGHIJKLMNOPQSTUVWXYZ】結(jié)果為2345890,ABCDEFGHIJKLMNOP查看請求日志如下：控制文件:/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/bin/CUXSQLLDRIM.ctl數(shù)據(jù)文件:/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/bin/CUXSQLLDRIM.ctl錯誤文件:/u02/dev/app/fs_ne/inst/DEV_ebstest/logs/appl/conc/out/o35083204.out廢棄文件:/u02/dev/app/fs_ne/inst/DEV_ebstest/logs/appl/conc/out/o35083204.out:(可廢棄所有記錄)要加載的數(shù):ALL要跳過的數(shù):0允許的錯誤:50綁定數(shù)組:64行,最大256000字節(jié)繼續(xù):未作指定所用路徑:常規(guī)表CUX_SLQLDR_TEST_TMP,已加載從每個邏輯記錄插入選項對此表REPLACE生效列名位置長度中止包裝數(shù)據(jù)類型DATA_SEG12:54CHARACTERDATA_SEG28:2720CHARACTER表CUX_SLQLDR_TEST_TMP:3行加載成功。由于數(shù)據(jù)錯誤,0行沒有加載。由于所有WHEN子句失敗,0行沒有加載。由于所有字段都為空的,0行沒有加載。為綁定數(shù)組分配的空間:1792字節(jié)(64行)讀取緩沖區(qū)字節(jié)數(shù):10485764.3.2、跳過數(shù)據(jù)行可以用"SKIPn"關(guān)鍵字來指定導(dǎo)入時可以跳過多少行數(shù)據(jù)。如：OPTIONS(skip=1)LOADDATAINFILE*INTOTABLEload_positional_data(data1POSITION(1:5),data2POSITION(6:15))BEGINDATA11111AAAAAAAAAA22222BBBBBBBBBB這個跳過多少條記錄的功能在前面我已經(jīng)詳細講解了，所以此處就不多說了。上面的例子導(dǎo)入到table中只會有一條記錄22222BBBBBBBBBB4.3.3、導(dǎo)入數(shù)據(jù)時修改數(shù)據(jù)在導(dǎo)入數(shù)據(jù)到數(shù)據(jù)庫時，可以修改數(shù)據(jù)。LOADDATAINFILE*INTOTABLEcux_slqldr_test_tmp(DATA_SEG1"cux_slqldr_test_tmp_s.nextval",DATA_SEG2CONSTANT'31',DATA_SEG3"to_char(SYSDATE,'HH24:MI')",DATA_SEG4POSITION(1:5)":DATA_SEG4/100",DATA_SEG5POSITION(6:15)"upper(:DATA_SEG5)",DATA_SEG6POSITION(16:24)"to_date(:DATA_SEG6,'YYYY/MM/DD')")BEGINDATA11111AAAAAAAAAA1999120122222BBBBBBBBBB1999011233333BBBBBBBBBB2013011244444BBBBBBBBBB2014011255555BBBBBBBBBB2015011266666BBBBBBBBBB20160112導(dǎo)入結(jié)果如下：命令解釋：1）DATA_SEG1"cux_slqldr_test_tmp_s.nextval",第一列不從數(shù)據(jù)源文件中取值，而是取索引cux_slqldr_test_tmp_s的值2）DATA_SEG2CONSTANT'31',第二列不從數(shù)據(jù)源文件中取值，默認(rèn)313）DATA_SEG3"to_char(SYSDATE,'HH24:MI')",第三列不從數(shù)據(jù)源文件中取值，取當(dāng)前系統(tǒng)的時間（小時：分鐘）4）DATA_SEG4POSITION(1:5)":DATA_SEG4/100",第四列取數(shù)據(jù)源字符串中的第一個字符至第五個字符，長度5，縮小100倍插入5）DATA_SEG5POSITION(6:15)"upper(:DATA_SEG5)",第五列取第六至第十五個字符，并且所有字符大寫6）DATA_SEG6POSITION(16:24)"to_date(:DATA_SEG6,'YYYY/MM/DD')"第六列取第十六至第二十四個字符，并且以日期格式Y(jié)YYY/MM/DD格式插入日志文件如下：控制文件:/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/bin/CUXSQLLDRIM.ctl數(shù)據(jù)文件:/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/bin/CUXSQLLDRIM.ctl錯誤文件:/u02/dev/app/fs_ne/inst/DEV_ebstest/logs/appl/conc/out/o35083219.out廢棄文件:/u02/dev/app/fs_ne/inst/DEV_ebstest/logs/appl/conc/out/o35083219.out:(可廢棄所有記錄)要加載的數(shù):ALL要跳過的數(shù):0允許的錯誤:50綁定數(shù)組:64行,最大256000字節(jié)繼續(xù):未作指定所用路徑:常規(guī)表CUX_SLQLDR_TEST_TMP,已加載從每個邏輯記錄插入選項對此表INSERT生效列名位置長度中止包裝數(shù)據(jù)類型DATA_SEG1FIRST1CHARACTER列的SQL串:"cux_slqldr_test_tmp_s.nextval"DATA_SEG2CONSTANTValueis'31'DATA_SEG3NEXT1CHARACTER列的SQL串:"to_char(SYSDATE,'HH24:MI')"DATA_SEG41:55CHARACTER列的SQL串:":DATA_SEG4/100"DATA_SEG56:1510CHARACTER列的SQL串:"upper(:DATA_SEG5)"DATA_SEG616:249CHARACTER列的SQL串:"to_date(:DATA_SEG6,'YYYY/MM/DD')"表CUX_SLQLDR_TEST_TMP:6行加載成功。由于數(shù)據(jù)錯誤,0行沒有加載。由于所有WHEN子句失敗,0行沒有加載。由于所有字段都為空的,0行沒有加載。4.3.4、將數(shù)據(jù)導(dǎo)入多個表LOADDATAINFILE*REPLACEINTOTABLEcux_slqldr_test_tmp1WHENdata_seg1!='1234'(data_seg1POSITION(1:4)INTEGEREXTERNAL,data_seg2POSITION(6:15)CHAR,data_seg3POSITION(17:18)CHAR,data_seg4POSITION(20:23)INTEGEREXTERNAL)INTOTABLEcux_slqldr_test_tmp2WHENdata_seg2!='9876'(data_seg1POSITION(25:27)INTEGEREXTERNAL,data_seg2POSITION(1:4)INTEGEREXTERNAL)begindata1234567890987654321987654321012345678901234567890987654321987654321012345678901234567890987654321導(dǎo)入結(jié)果如下：命令解釋：INTOTABLEcux_slqldr_test_tmp1WHENdata_seg1!='1234'(data_seg1POSITION(1:4)INTEGEREXTERNAL,data_seg2POSITION(6:15)CHAR,data_seg3POSITION(17:18)CHAR,data_seg4POSITION(20:23)INTEGEREXTERNAL)將前四位字符不是1234的數(shù)據(jù)導(dǎo)入表cux_slqldr_test_tmp1中INTOTABLEcux_slqldr_test_tmp2WHENdata_seg2!='9876'(data_seg1POSITION(25:27)INTEGEREXTERNAL,data_seg2POSITION(1:4)INTEGEREXTERNAL)將前四位字符不是9876的數(shù)據(jù)導(dǎo)入表cux_slqldr_test_tmp2中日志如下：控制文件:/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/bin/CUXSQLLDRIM.ctl數(shù)據(jù)文件:/u02/dev/app/fs1/EBSapps/appl/cux/12.0.0/bin/CUXSQLLDRIM.ctl錯誤文件:/u02/dev/app/fs_ne/inst/DEV_ebstest/logs/appl/conc/out/o35083224.out廢棄文件:/u02/dev/app/fs_ne/inst/DEV_ebstest/logs/appl/conc/out/o35083224.out:(可廢棄所有記錄)要加載的數(shù):ALL要跳過的數(shù):0允許的錯誤:50綁定數(shù)組:64行,最大256000字節(jié)繼續(xù):未作指定所用路徑:常規(guī)表CUX_SLQLDR_TEST_TMP1,已加載在DATA_SEG1!=0X31323334(字符'1234')插入選項對此表REPLACE生效列名位置長度中止包裝數(shù)據(jù)類型DATA_SEG11:44CHARACTERDATA_SEG26:1510CHARACTERDATA_SEG317:182CHARACTERDATA_SEG420:234CHARACTER表CUX_SLQLDR_TEST_TMP2,已加載在DATA_SEG2!=0X39383736(字符'9876')插入選項對此表REPLACE生效列名位置長度中止包裝數(shù)據(jù)類型DATA_SEG125:273CHARACTERDATA_SEG21:44CHARACTER表CUX_SLQLDR_TEST_TMP1:2行加載成功。由于數(shù)據(jù)錯誤,0行沒有加載。由于所有WHEN子句失敗,3行沒有加載。由于所有字段都為空的,0行沒有加載。表CUX_SLQLDR_TEST_TMP2:3行加載成功。由于數(shù)據(jù)錯誤,0行沒有加載。由于所有WHEN子句失敗,2行沒有加載。由于所有字段都為空的,0行沒有加載。4.3.5、導(dǎo)入選定的記錄（即where篩選）如下例：(01)代表第一個字符,(30:37)代表30到37之間的字符:LOADDATAINFILE'mydata.dat'BADFILE'mydata.bad'DISCARDFILE'mydata.dis'APPENDINTOTABLEmy_selective_tableWHEN(01)<>'H'and(01)<>'T'and(30:37)='19991217'(regionCONSTANT'31',service_keyPOSITION(01:11)INTEGEREXTERNAL,call_b_noPOSITION(12:29)CHAR)其實這個已經(jīng)很簡單了，直接在into字句后面加上where條件就行了，所以也不細說了4.3.5小節(jié)中就有篩選記錄的條件調(diào)試及日志問題所在在使用過程中，發(fā)現(xiàn)SQL*Loader有個問題，很是煩惱，就是無法調(diào)試，只能通過不斷的提交請求去看，可能有時候代碼問題很小，錯誤的點也不大，但是也無法調(diào)試。下面我列舉幾個錯誤日志：Copyright(c)1982,2005,Oracle.Allrightsreserved.SQL*Loader-403:引用的列PROJNO不在表CUX_SLQLDR_TEST_TMP2中。程序退出時出現(xiàn)狀態(tài)1在運行并發(fā)請求35083223的SQL*Loader程序時，并發(fā)管理器出現(xiàn)錯誤。有關(guān)詳情，請復(fù)查并發(fā)請求日志文件Copyright(c)1982,2005,Oracle.Allrightsreserved.SQL*Loader-403:引用的列PROJNO不在表CUX_SLQLDR_TEST_TMP2中。程序退出時出現(xiàn)狀態(tài)1在運行并發(fā)請求35083223的SQL*Loader程序時，并發(fā)管理器出現(xiàn)錯誤。有關(guān)詳情，請復(fù)查并發(fā)請求日志文件插入選項對此表INSERT生效列名位置長度中止包裝數(shù)據(jù)類型DATA_SEG1