The CASE2 of Sql*loader

CASE2

1. Control files

[email protected] ulcase]$ cat Ulcase2.ctl

--NAME--ulcase2.ctl-sql*loader case Study 2:loading fixed-format Files----DESCRIPTION--This control file demonstrates the following:--Use of a separate data file.----Data conversions.----To RUN the case STUDY:--1. Before executing this control file, log in to Sql*plus as--Scott/tiger. Enter @ulcase1 to execute the SQL script for--This case study. This prepares and populates tables and--Then returns the system prompt. It is the same script--used to prepare in case study 1, so if you have already--Run Case Study 1, your can skip this step.----2. At the system prompt, invoke the case study as follows:--sqlldr userid=scott/tiger control=ulcase2.ctl log=ulcase2.log----NOTES about the This CONTROL FILE--The LOAD DATA statement is required at the beginning of the--control file.----The name of the file containing data follows the INFILE parameter.----The Into table statement are required to identify , the table to--Be loaded into.----empno, ename, job, and so on is names of columns in table EMP.--the Datatypes (INTEGER EXTERNAL, CHAR, DECIMAL EXTERNAL) identify--The datatype of data fields in the file, not of corresponding--columns in the EMP table.----Note that the set of column specifications are enclosed in--parentheses.----Records loaded In this example from the EMP table contain--Department numbers. Unless the Dept table is loaded first,--referential integrity checking rejects these records (if--referential integrity constraints is enabled for the EMP table).--LOADDatainfile'Ulcase2.dat' into TABLEEMP (EMPNO POSITION ( on:Geneva)INTEGEREXTERNAL, ename POSITION ( .: the)CHAR, JOB POSITION ( -: -)CHAR, MGR POSITION ( -: -)INTEGEREXTERNAL, SAL POSITION ( +: the)DECIMALEXTERNAL, COMM POSITION ( A: -)DECIMALEXTERNAL, DEPTNO POSITION ( -:Wuyi)INTEGEREXTERNAL)

2. Data files

[email protected] ulcase]$ cat Ulcase2.dat

7782CLARK MANAGER7839  2572.50          Ten7839KING President5500.00          Ten7934MILLER Clerk7782   920.00          Ten7566JONES MANAGER7839  3123.75           -7499ALLEN salesman7698  1600.00   300.00  -7654MARTIN salesman7698  1312.50  1400.00  -7658CHAN ANALYST7566  3450.00           -

Results after execution:

Sql> Select *  fromEMP; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO----- ---------- --------- ---------- --------- --------- ---------- ---------- 7782CLARK MANAGER7839                2572.5              Ten 7839KING President5500                Ten 7934MILLER Clerk7782                920                 Ten 7566JONES MANAGER7839                3123.75              - 7499ALLEN salesman7698                 the       -        - 7654MARTIN salesman7698                1312.5    1400       - 7658CHAN ANALYST7566                3450207Rows selected.

The CASE2 of Sql*loader