Control File for Case Study 2

The control file is ulcase2.ctl.

1)   LOAD DATA
2)   INFILE 'ulcase2.dat'
3)   INTO TABLE emp
4)   (empno         POSITION(01:04)   INTEGER EXTERNAL,
       ename          POSITION(06:15)   CHAR,
       job            POSITION(17:25)   CHAR,
       mgr            POSITION(27:30)   INTEGER EXTERNAL,
       sal            POSITION(32:39)   DECIMAL EXTERNAL,
       comm           POSITION(41:48)   DECIMAL EXTERNAL,
5)   deptno         POSITION(50:51)   INTEGER EXTERNAL)

Notes:

1. The LOAD DATA statement is required at the beginning of the control file.

2. The name of the file containing data follows the INFILE parameter.

3. The INTO TABLE statement is required to identify the table to be loaded into.

4. Lines 4 and 5 identify a column name and the location of the data in the datafile to be loaded into that column. empno, ename, job, and so on are 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.

5. Note that the set of column specifications is enclosed in parentheses.

Datafile for Case Study 2

The following are a few sample data lines from the file ulcase2.dat. Blank fields are set to null automatically.

7782 CLARK      MANAGER   7839  2572.50          10 
7839 KING       PRESIDENT       5500.00          10 
7934 MILLER     CLERK     7782   920.00          10 
7566 JONES      MANAGER   7839  3123.75          20 
7499 ALLEN      SALESMAN  7698  1600.00   300.00 30 
7654 MARTIN     SALESMAN  7698  1312.50  1400.00 30 
7658 CHAN       ANALYST   7566  3450.00          20 
7654 MARTIN     SALESMAN  7698  1312.50  1400.00 30

Running Case Study 2

Take the following steps to run the case study. If you have already run case study 1, you can skip to Step 3 because the ulcase1.sql script. handles both case 1 and case 2.

1. Start SQL*Plus as scott/tiger by entering the following at the system prompt:

   sqlplus scott/tiger
   
   

   The SQL prompt is displayed.

2. At the SQL prompt, execute the SQL script. for this case study, as follows:

   SQL> @ulcase1
   
   

   This prepares and populates tables for the case study and then returns you to the system prompt.

3. At the system prompt, invoke SQL*Loader and run the case study, as follows:

   sqlldr USERID=scott/tiger CONTROL=ulcase2.ctl LOG=ulcase2.log
   
   

   SQL*Loader loads the table, creates the log file, and returns you to the system prompt. You can check the log file to see the results of running the case study.

   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 are enabled for the emp table).

Log File for Case Study 2

The following shows a portion of the log file:

Control File:   ulcase2.ctl
Data File:      ulcase2.dat
  Bad File:     ulcase2.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table EMP, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO                                 1:4     4           CHARACTER            
ENAME                                6:15    10           CHARACTER            
JOB                                 17:25     9           CHARACTER            
MGR                                 27:30     4           CHARACTER            
SAL                                 32:39     8           CHARACTER            
COMM                                41:48     8           CHARACTER            
DEPTNO                              50:51     2           CHARACTER            


Table EMP:
  7 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                   3840 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             7
Total logical records rejected:         0
Total logical records discarded:        0
.
.
.
Elapsed time was:     00:00:00.81
CPU time was:         00:00:00.15