One: The characteristics of SQL Loader Oracle have a lot of tools that can be used for data migration, backup, and recovery. But each tool has its own characteristics. For example, EXP and IMP can be exported and exported to data in the database, which is a good database backup and recovery tool, so it is mainly used in the hot backup and recovery of the database. There is a fast speed, simple and fast, and some shortcomings, such as the export between different versions of the database, the process of importing, there will always be such a problem, this may be the compatibility of Oracle's own products. Sexual question. The SQL Loader tool does not have this problem, which can put some data stored in the text format into the Oracle database, is a very convenient and universal tool for data migration between different databases. Disadvantages are slower, and additional data such as blob is a bit troublesome. Two: SQL Loader Help
C: /> SQLLDR
SQL * Loader: Release 9.2.0.1.0 - Production on Saturday October 9 14:48:12 2004
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Usage: SqlLDR Keyword = Value [, Keyword = Value, ...]
Effective keyword:
Userid - Oracle Username / Password Control - Control File Name Log - Log File Name Bad - Bad File Name Data - Data File Name Discard - Discard File Namediscardmax - Number of Discards To Allow - Skip - - Number of Logical Records To Skip (Default 0) Load - Number of Logical Records To Load (All Default) Errors - Number of Errors To Allow (Default 50) Rows - Number of Rows in Convenctional Path Bind Array or Between Direct Path Data Saves (Default: General Path 64, All Direct Path) BINDSIZE - SIZE OF Conventional Path Bind Array In Bytes (Default 256000) Silent - Suppress Messages During Run (Header, Feedback, Errors, Discards, Partitions Direct - use direct path (default FALSE) parfile - parameter file: name of file that contains parameter specifications parallel - do parallel load (default FALSE) file - File to allocate extents fromskip_unusable_indexes - disallow / allow unusable indexes or index partitions (default False) SKIP_IN dex_maintenance - do not maintain indexes, mark affected indexes as unusable (default FALSE) readsize - Size of Read buffer (default 1048576) external_table - use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (default NOT_USED) columnarrayrows - Number of rows for direct path column array (default 5000) streamsize - Size of direct path stream buffer in bytes (default 256000) multithreading - use multithreading in direct path resumable - enable or disable resumable for current session (default FALSE) resumable_name - - TEXT STRING TO HELP IDENTIFY Resumable StatementResumable_timeout - Wait Time (In Seconds) for Resumability (Default 7200) Date_Cache - Size (in Entries) of Date Conversion Cache (default 1000)
Please Note: The command line parameter can be specified by the location or keyword. The former is the 'sqlloadscott / tiger foo'; one example of the latter case is 'sqlldr control = fooouserid = scott / tiger'. The time specified by the specified parameter must be premature than the parameter specified by the keyword. For example, 'SqlLDR Scott / Tiger Control = Foo logfile = log', but does not allow 'sqlldr scott / tiger control = foo log', even if the parameter 'log' is positioned correctly. C: />
Three: SQL Loader Examples a) SQLLoader exports Excel data to Oracle1. Creating SQL * Loader Entering the files required to enter data, Save to C: /, with Notepad Edit: Control File: Input.ctl, the content is as follows: loading Data --1, control file identifier infile 'test.txt' --2, the data file to be entered is named Test.txt append Into Table Test --3, adding recorded records in Test Test Fields Terminated by X'09 '- -4, field termination in X'09 ', is a Tab (ID, username, password, sj) ----- defining column corresponding order A, INSERT, for the default mode, starting in data loading When the table is empty B, append, add new records C, Replace, delete old records, replace the newly loaded record D, TRUNCATE, using the SQL * loader command to implement data in the DOS window C: / > SQLLDR Userid = System / Manager Control = INPUT.CTL The default log file name is: Input.log Default bad log file is: Input.bad 2. There is also a way to save the Excel file as a CSV (comma separated) (*. CSV), the control file is changed to separate the load data infile 'd: /car.csv' append infile 'd: /car_temp fields terminated by "," (Phoneno, VIP_CAR)
b) import data directly in the control file
1. Contents Contents Test.ctl - The Format for Executing this file with sql loader is: - SQLLDR Control =
09/16/2004 16:31:32 "; NULL14;" SE ";" Sweden "; 3; 22;" 09/16/2004 16:31:32 "; NULL15;" IE ";" Ireland "; 3 ; 23; "09/16/2004 16:31:32"; NULL16; "NL"; "Netherlands"; 3; 24; "09/16/2004 16:31:32"; NULL17; "DK"; " "09/16/2004 16:31:32"; NULL18; "Br"; "BRAZIL"; 5; 85; "09/30/2004 11:25:43"; null19; " Kr ";" Korea, republic of "; 1; 88;" 09/30/2004 11:25:43 "; null20;" NZ ";" new Zealand "; 6; 89;" 09/30/2004 11: 25:43 "; null21;" be ";" be "; 3; 79;" 09/30/2004 11:25:43 "; NULL22;" AT ";" austria "; 3; 78;" 09/30 / 2004 11:25:43 "; NULL23;" NO ";" norway "; 3; 82;" 09/30/2004 11:25:43 "; NULL24;" Lu ";" luxembourg "; 3; 81; "09/30/2004 11:25:43"; NULL25; "PT"; "Portugal"; 3; 83; "09/30/2004 11:25:43"; NULL26; "GR"; "Greece"; 3; 80; "09/30/2004 11:25:43"; NULL27; "IL"; "Israel"; 1; 86; "09/30/2004 11:25:43"; NULL28; "CH"; "Switzerland"; 3; 84; "09/30/2004 11:25:43"; NULL29; "A1"; "Anonymous Proxy"; 0; 0; "09/30/2004 11:25:43"; NULL30 "A2"; "Satellite Provider"; 0; 0; "09/30/2004 11:25:43"; NULL31; "AD"; "Andorra"; 3; 0; "09/30/2004 11:25 : 43 "; NULL32;" AE ";" United Arab Emirates "; 1; 0;" 09/30/2004 11:25:43 "NULL33;" AF ";" afghanistan "; 1; 0;" 09/30/2004 11:25:43 "; NULL34;" AG ";" Antigua and Barbuda "; 7; 0;" 09/30 / 2004 11:25:43 "; NULL35;" Ai ";" Anguilla "; 7; 0;" 09/30/2004 11:25:43 "; NULL36;" Al ";" albania "; 3; 0;"
09/30/2004 11:25:43 "; NULL37;" AM ";" armenia "; 3; 0;" 09/30/2004 11:25:43 "; null38;" an ";" Netherlands Antilles "; 3; 0; "09/30/2004 11:25:43"; NULL39; "AO"; "Angola"; 2; 0; "09/30/2004 11:25:43"; NULL40; "AP"; "Asia / Pacific Region"; 2; 0; "09/30/2004 11:25:43"; NULL41; "AQ"; "Antarctica"; 8; 0; "09/30/2004 11:25:43" NULL42; "Ar"; "argentina"; 5; 0; "09/30/2004 11:25:43"; NULL43; "AS"; "American samoa"; 6; 0; "09/30/2004 11 : 25: 43 "; NULL44;" AW ";" aruba "; 5; 0;" 09/30/2004 11:25:43 "; NULL45;" AZ ";" azerbaijan "; 1; 0;" 09 / 30/2004 11:25:43 "; null46;" ba ";" bosnia and herzegovina "; 3; 0;" 09/30/2004 11:25:43 "; NULL47;" BB ";" Barbados "; 5 0; "09/30/2004 11:25:43"; NULL48; "BD"; "Bangladesh"; 1; 0; "09/30/2004 11:25:43"; NULL49; "BF"; Burkina Faso "; 2; 0;" 09/30/2004 11:25:43 "; NULL50;" BG ";" bulgaria "; 3; 0;" 09/30/2004 11:25:43 "; NULL51; "BH"; "bahrain"; 1; 0; "09/30/2004 11:25:43"; NULL52; "Bi"; "Burundi"; 0; 09/30/2004 11:25:43 "NULL53;" BJ ";" benin "; 2; 0;" 09/30/2004 11:25:43 "; NULL54;" BM ";" bermuda "; 4; 0; 09/30/2004 11 : 25: 43 "; NULL55;" BN ";" brunei darussalam "; 1; 0;" 09/30/2004 11: 25: 4 3 "; null56;" bo ";" bolivia "; 5; 0;" 09/30/2004 11:25:43 "; NULL57;" BS ";" bahamas "; 7; 0;" 09/30/2004 11:25:43 "; NULL58;" BT ";" bhutan "; 1; 0;" 09/30/2004 11:25:43 "; null59;" BV ";" bouvet island "; 5; 0;"
09/30/2004 11:25:43 "; NULL60;" BOTSWANA "; 2; 0;" 09/30/2004 11:25:43 "; Null61;" BELARUS "; 3 ; 09/30/2004 11:25:43 "; NULL2, execute the import command c: /> sqlldr userid = system / manager control = test.ctlc) complex format import --------- -------------------------------------------------- -------------------------------
SQL * Loader is an Oracle database imported a tool for external data. It is similar to DB2's LOAD tool, but there is more choices, which supports changing load modes, optional loading and multi-table loading. How to use SQL * Loader Tools We can import data with Oracle's SQLLDR tools. For example: SqlLDR scott / tiger control = loader.ctl control file (loader.ctl) will load an external data file (including separator). Loader.ctl is as follows: Load Data Infile 'c: /data/mydata.csv' Into Table EMP FIELDS TERMINATED BY "," Optionally Enclosed By '"(Empno, Empname, Sal, Deptno) MyData.csv is as follows: 10001," Scott Tiger ", 1000, 40 10002," Frank Naude ", 500, 20 below is one Specifies the sample control file of the recording length. "*" Represents the same name as the data file, that is, using the Begindata paragraph to identify the data. Load Data Infile * Replace Into Table Departments (DEPT POSITION (02:05) Char (4), DeptName Position (08:27) CHAR (20)) Begindata COSC Computer Science ENGL ENGLISH LITERATURE MATH MATHEMATIS POLITERATION MATH MATHEMATICS POLITICAL SCIENCE Unloader does not provide tools to export data to a file. However, we can use SQL * Plus SELECT And Format data to output to a file: set echo off newpage 0 Space 0 PageSize 0 feed Off Head Off Trimspool On Spool ORADATA.TXT SELECT COL1 || ',' || Col2 || ',' || col3 from Tab1 Where COL2 = 'XYZ'; Spool Off Addition, can also be used using UTL_FILE PL / SQL Package: Rem Remember to update INITSID.ORA, UTL_FILE_DIR = 'c: / oradata' parameter declare fp Utl_file.file_type; begin fp: = UTL_FILE.FOPEN (' C: / ORADATA ',' Tab1.txt ',' W '); UTL_FILE.PUTF (FP,'% S,% S / N ',' TextField ', 55); UTL_FILE.FCLOSE (FP); END; / Of course, you can also use third-party tools, such as SqlWays, Toad for Quest, etc.
Loading specified length or variable length records, such as: LOAD DATA INFILE * INTO TABLE load_delimited_data FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY ' "' TRAILING NULLCOLS (data1, data2) BEGINDATA 11111, AAAAAAAAAA 22222," A, B, C, D, "The following is an example of importing fixed position (fixed length) data: Load Data Infile * Into Table Load_positional_data (Data1 Position (1: 5), Data2 Position (6:15)) Begindata 1111Aaaaaaaa 25222bbbbbbbb jumps the data line: You can use" Skip N "Keywords to specify how many rows of data can be skipped when imported. Such as: Load Data INFILE * INTO TABLE LOAD_POSITIONAL_DATA SKIP 5 (Data1 Position (1: 5), Data2 Position (6:15)) Begindata 1111AAAAAAAAA 22222Bbbbbbbb import data Modifying data: You can modify the data when importing data to the database.
Note that this is only suitable for conventional imports, not suitable for Direct import mode. Such as: Load Data Infile * Into Table Modified_data (R_NO "MY_DB_SEQUENCE.NEXTVAL", Region Constant '31', Time_Loaded "To_Char (sysdate, 'hh24: mi' ) ", Data1 Position (1: 5)": DATA1 / 100 ", DATA2 POSITION (6:15)" Upper (: DATA2) ", DATA3 POSITION (16:22)" To_date (: Data3, 'YYMMDD') " ) BEGINDATA 11111AAAAAAAAAA991201 22222BBBBBBBBBB990112 LOAD DATA INFILE 'mail_orders.txt' BADFILE 'bad_orders.txt' APPEND INTO TABLE mailing_list FIELDS TERMINATED BY "," (addr, city, state, zipcode, mailing_addr "decode (: mailing_addr, null,: addr,: Mailing_addr) ", mailing_city" decode (: mailing_city, null,: city,: mailing_city) ", mailing_state) import data into multiple tables: Load Data Infile * Replace INTO TABLE EMP WHEN Empno! = '' (Empno Position 1: 4) Integer External, Ename Position (6:15) Char, Deptno Position (17:18) CHAR, MGR Position (20:23) Integer External Into Table Proj When Projno! = '(Projno Position (25: 27) Integer External, Empno Position (1: 4) Integer External Import Selected Record: Such an example: (01) Representing the first word The characters, (30:37) representatives between 30 to 37: load data infile 'mydata.dat' badfile 'mydata.bad' discardfile 'mydata.dis' append invo Table my_selective_table by (01) <> 'h' and (01) <> 't' and (30:37) = '19991217' (Region constant '31', service_key position (01:11) Integer External, Call_b_no position (12:29) char) Skip when Field: You can separate the data with post (x: y). You can implement the Filler field in Oracle8i.
