The CASE1 of Sql*loader

The recent project involves importing the data from the text file into the Oracle database, so we studied the next Sql*loader, the information provided by the official document is not very rich, the statements appearing in many cases can not find the source in the official document. But the case that it offers itself highlights the power of the Sql*loader function. Given that the Oracle 11g software itself does not carry these cases, it is troublesome to download Oracle Database 11g Release 2 Examples specifically to the official website. In this, these cases are shared, but also convenient for later study, learn from.

Due to the official documentation has not been completed, the hand is also the Oracle sql*loader:the definitive Guide. So the case goes first, the theory is behind.

These 11 cases: Http://pan.baidu.com/s/1o6Hl57G

A total of 11 cases, the case basically consists of three parts, control files, SQL scripts, data files. Some have no data files, which are provided directly in the control file.

CASE1

1. SQL scripts

[email protected] ulcase]$ cat Ulcase1.sql

SetTermoutifDrop TableEMP;Drop TableDept;Create TableEMP (empno Number(4) not NULL, enameChar(Ten), JobChar(9), Mgr Number(4), HireDate date, Sal Number(7,2), Comm Number(7,2), Deptno Number(2));Create TableDept (Deptno Number(2), DnameChar( -), locChar( -) ) ;Exit

2. Control files

[email protected] ulcase]$ cat Ulcase1.ctl

--NAME--ulcase1.ctl-sql*loader case Study 1:loading variable-length Data----DESCRIPTION--This case study demonstrates the following:----A Simple control file identifying one table and three columns--To is loaded.----including data to being loaded from the control file itself, so--there is no separate datafile.----Loading data in stream format, with both types of delimited--fields:terminated and enclosed.----NOTES about the This CONTROL FILE--The LOAD DATA statement is required at the beginning of the--control file.----INFILE * Specifies that the data was found in the control file--An external file.----The Into table statement are required to identify , the table to--Be loaded (dept) into. By default, Sql*loader requires the--table to is empty before it inserts any records.----TERMINATED by specifies , the data is TERMINATED by--commas, but may also is enclosed by quotation marks. Datatypes--For any fields, default to CHAR.----The names of columns to load is enclosed in parentheses.--If No datatype or length is specified and the field is delimited--with enclosed by or with TERMINATED by and then the default--datatype is CHAR and the default length is 255. If Enclosed by--or TERMINATED by isn't specified, then the default type is CHAR--and the default length is 1.----Begindata Specifies the beginning of the data.--LOADDatainfile* into TABLEDeptfields TERMINATED by ','Optionally enclosed by '"'(DEPTNO, Dname, LOC) Begindata A, the "SARATOGA"Ten, "ACCOUNTING", CLEVELAND One, "ART", SALEM -, FINANCE, "BOSTON" +, "SALES", Phila. A, "SALES", ROCHESTER the,"INT'L "," SAN FRAN "

Results after execution:

[Email protected] ulcase]$ sqlplus scott/tiger @ulcase1. sql

[Email protected] ulcase]$ Sqlldr Userid=scott/tiger control=ulcase1.ctl

Sql> Select *  fromDept; DEPTNO dname LOC---------- -------------- -------------     ASARATOGATenACCOUNTING CLEVELAND OneART SALEM -FINANCE BOSTON +SALES Phila.  ASALES ROCHESTER the INT'L SAN FRAN7 rows selected.

The CASE1 of Sql*loader