| Oracle7 Server Utilities User's Guide |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Server Utilities User's Guide | Library |
Product |
Contents |
Index |
The case studies in this chapter illustrate some of the features of SQL*Loader. These case studies start simply and progress in complexity.
The cases are:
Case 1: Loads stream format records in which the fields are delimited by commas and may be enclosed by quotation marks. The data is found at the end of the control file. Case 2: Loads a datafile with fixed-length, fixed-format records Case 3: Loads data from stream format records with delimited fields and sequence numbers. The data is found at the end of the control file. Case 4: Combines multiple physical records into one logical record corresponding to one database row Case 5: Loads data into multiple tables in one run Case 6: Loads data using the direct path load method Case 7: Extracts data from a formatted report
The distribution media for SQL*Loader contains files for each case:
If the sample data for the case study is contained in the control file, then there will be no .DAT file for that case. If there are no special setup steps for a case study, there may be no .SQL file for that case. Starting (setup) and ending (cleanup) scripts are denoted by an S or E after the case number. The table below lists the files associated with each case:
| CASE | .CTL | .DAT | .SQL |
| 1 | x | x | |
| 2 | x | x | |
| 3 | x | x | |
| 4 | x | x | x |
| 5 | x | x | x |
| 6 | x | x | x |
| 7 | x | x | x S, E |
Table 4 - 1. Case Studies and their Related Files
Additional Information: The actual names of the case study files are operating system-dependent. See your Oracle operating system-specific documentation for the exact names.
The case studies are based upon the standard Oracle demonstration database tables EMP and DEPT owned by SCOTT/TIGER. (In some of the case studies, additional columns have been added.)
(empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2))
(deptno NUMBER(2) NOT NULL,
dname VARCHAR2(14),
loc VARCHAR2(13))
The summary at the beginning of each case study contains page number references, directing you to the sections of this guide that discuss the SQL*Loader feature being demonstrated in more detail.
In the control file fragment and log file listing shown for each case study, the numbers that appear to the left are not actually in the file; they are keyed to the numbered notes following the listing. Do not use these numbers when you write your control files.
You should run the SQL scripts ULCASE1.SQL and ULCASE3.SQL through ULCASE7.SQL to prepare and populate the tables. Note that there is no ULCASE2.SQL as Case 2 is handled by ULCASE1.SQL.
Case 1 demonstrates
The control file is ULCASE1.CTL:
1) LOAD DATA
2) INFILE *
3) INTO TABLE dept
4) FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
5) (deptno, dname, loc)
6) BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"
Notes:
1) The LOAD DATA statement is required at the beginning of the control file.
2) INFILE * specifies that the data is found in the control file and not in an external file.
3) The INTO TABLE statement is required to identify the table to be loaded (DEPT) into. By default, SQL*Loader requires the table to be empty before it inserts any records.
4) FIELDS TERMINATED BY specifies that the data is terminated by commas, but may also be enclosed by quotation marks. Datatypes for all fields default to CHAR.
5) Specifies that the names of columns to load are enclosed in parentheses.
6) BEGINDATA specifies the beginning of the data.
To run this example, invoke SQL*Loader with the command:
sqlldr userid=scott/tiger control=ulcase1.ctl log=ulcase1.log
SQL*Loader loads the DEPT table and creates the log file.
Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke SQL*Loader on your operating system, refer to your Oracle operating system-specific documentation.
The following shows a portion of the log file:
Control File: ULCASE1.CTL
Data File: ULCASE1.DAT
Bad File: ULCASE1.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 65336 bytes
Continuation: none specified
Path used: Conventional
Table DEPT, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
--------------- -------- --- ---- ---- ---------
1) DEPTNO FIRST * , O(") CHARACTER
DNAME NEXT * , O(") CHARACTER
2) LOC NEXT * WHT O(") CHARACTER
Table DEPT:
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: 49920 bytes(64 rows)
Space allocated for memory besides bind array: 76000 bytes
Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0
Notes:
1) Position and length for each field are determined for each record, based on delimiters in the input file.
2) WHT signifies that field LOC is terminated by WHITESPACE. The notation O(") signifies optional enclosure by quotation marks.
Case 2 demonstrates
In this case, the field positions and datatypes are specified explicitly.
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 keyword INFILE.
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.
Below 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
Invoke SQL*Loader with a command such as:
sqlldr userid=scott/tiger control=ulcase2.ctl log=ulcase2.log
The EMP records loaded in this example 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).
Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke SQL*Loader on your operating system, refer to your Oracle operating system-specific documentation.
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 65336 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 4352 bytes(64 rows)
Space allocated for memory besides bind array: 37051 bytes
Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0
Case 3 demonstrates
This control file loads the same table as Case 2, but it loads three additional columns (HIREDATE, PROJNO, LOADSEQ). The demonstration table EMP does not have columns PROJNO and LOADSEQ. So if you want to test this control file, add these columns to the EMP table with the command:
ALTER TABLE EMP ADD (PROJNO NUMBER, LOADSEQ NUMBER)
The data is in a different format than in Case 2. Some data is enclosed in quotation marks, some is set off by commas, and the values for DEPTNO and PROJNO are separated by a colon.
1) -- Variable-length, delimited and enclosed data format
LOAD DATA
2) INFILE *
3) APPEND
INTO TABLE emp
4) FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr,
5) hiredate DATE(20) "DD-Month-YYYY",
sal, comm, deptno CHAR TERMINATED BY ':',
projno,
6) loadseq SEQUENCE(MAX,1))
7) BEGINDATA
8) 7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50,, 10:101
7839, "King", "President", , 17-November-1981,5500.00,,10:102
7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00,, 10:102
7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75,, 20:101
7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00,
(same line continued) 300.00, 30:103
7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50,
(same line continued) 1400.00, 3:103
7658, "Chan", "Analyst", 7566, 03-May-1982, 3450,, 20:101
Notes:
1) Comments may appear anywhere in the command lines of the file, but they should not appear in data. They are preceded with a double dash that may appear anywhere on a line.
2) INFILE * specifies that the data is found at the end of the control file.
3) Specifies that the data can be loaded even if the table already contains rows. That is, the table need not be empty.
4) The default terminator for the data fields is a comma, and some fields may be enclosed by double quotation marks (").
5) The data to be loaded into column HIREDATE appears in the format DD-Month-YYYY. The length of the date field is dependent on the mask specified.
6) The SEQUENCE function generates a unique value in the column LOADSEQ. This function finds the current maximum value in column LOADSEQ and adds the increment (1) to it to obtain the value for LOADSEQ for each row inserted.
7) BEGINDATA specifies the end of the control information and the beginning of the data.
8) Although each physical record equals one logical record, the fields vary in length so that some records are longer than others. Note also that several rows have null values for COMM.
Invoke SQL*Loader with a command such as:
sqlldr userid=scott/tiger control=ulcase3.ctl log=ulcase3.log
Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke SQL*Loader on your operating system, see your Oracle operating system-specific documentation.
The following shows a portion of the log file:
Control File: ULCASE3.CTL
Data File: YLCASE3.DAT
Bad File: ULCASE3.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 65336 bytes
Continuation: none specified
Path used: Conventional
Table EMP, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------- -------- --- ---- ---- ----------
EMPNO FIRST * , O(") CHARACTER
ENAME NEXT * , O(") CHARACTER
JOB NEXT * , O(") CHARACTER
MGR NEXT * , O(") CHARACTER
HIREDATE NEXT 20 , O(") DATE DD-Month-YYYY
SAL NEXT * , O(") CHARACTER
COMM NEXT * , O(") CHARACTER
DEPTNO NEXT * : O(") CHARACTER
PROJNO NEXT * , O(") CHARACTER
LOADSEQ SEQUENCE (MAX, 1)
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: 63810 bytes(30 rows)
Space allocated for memory besides bind array: 94391 bytes
Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0
Case 4 demonstrates
The control file is ULCASE4.CTL:
LOAD DATA
INFILE 'ulcase4.dat'
1) DISCARDFILE 'ulcase4.dsc'
2) DISCARDMAX 999
3) REPLACE
4) CONTINUEIF THIS (1) = '*'
INTO TABLE emp
(empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6: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,
deptno POSITION(50:51) INTEGER EXTERNAL,
hiredate POSITION(52:60) INTEGER EXTERNAL)
Notes:
1) DISCARDFILE specifies a discard file named ULCASE4.DSC.
2) DISCARDMAX specifies a maximum of 999 discards allowed before terminating the run (for all practical purposes, this allows all discards).
3) REPLACE specifies that if there is data in the table being loaded, then SQL*Loader should delete that data before loading new data.
4) CONTINUEIF THIS specifies that if an asterisk is found in column 1 of the current record, then the next physical record after that record should be appended to it to from the logical record. Note that column 1 in each physical record should then contain either an asterisk or a non-data value.
The datafile for this case, ULCASE4.DAT, is listed below. Note the asterisks in the first position and, though not visible, a new line indicator is in position 20 (following "MA", "PR", and so on). Note that CLARK's commission is -10, and SQL*Loader loads the value converting it to a negative number.
*7782 CLARK MANAGER 7839 2572.50 -10 2512-NOV-85
*7839 KING PRESIDENT 5500.00 2505-APR-83
*7934 MILLER CLERK 7782 920.00 2508-MAY-80
*7566 JONES MANAGER 7839 3123.75 2517-JUL-85
*7499 ALLEN SALESMAN 7698 1600.00 300.00 25 3-JUN-84
*7654 MARTIN SALESMAN 7698 1312.50 1400.00 2521-DEC-85
*7658 CHAN ANALYST 7566 3450.00 2516-FEB-84
* CHEN ANALYST 7566 3450.00 2516-FEB-84
*7658 CHIN ANALYST 7566 3450.00 2516-FEB-84
The last two records are rejected, given two assumptions. If there is a unique index created on column EMPNO, then the record for CHIN will be rejected because his EMPNO is identical to CHAN's. If EMPNO is defined as NOT NULL, then CHEN's record will be rejected because it has no value for EMPNO.
Invoke SQL*Loader with a command such as:
sqlldr userid=scott/tiger control=ulcase4.ctl log=ulcase4.log
Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke SQL*Loader on your operating system, see your operating Oracle system-specific documentation.
The following is a portion of the log file:
Control File: ULCASE4.CTL
Data File: ULCASE4.DAT
Bad File: ULCASE4.BAD
Discard File: ULCASE4.DSC
(Allow 999 discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65336 bytes
Continuation: 1:1 = 0X2a(character '*'),
in current physical record
Path used: Conventional
Table EMP, loaded from every logical record.
Insert option in effect for this table: REPLACE
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
HIREDATE 52:60 9 CHARACTER
Record 8: Rejected - Error on table EMP, --EMPNO null
ORA-01400: mandatory (NOT NULL) column is missing or NULL during
insert
Record 9: Rejected - Error on table EMP. --EMPNO not unique
ORA-00001: unique constraint (SCOTT.EMPIX) violated
Table EMP:
7 Rows successfully loaded.
2 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: 5120 bytes(64 rows)
Space allocated for memory besides bind array: 40195 bytes
Total logical records skipped: 0
Total logical records read: 9
Total logical records rejected: 2
Total logical records discarded: 0
The bad file, shown below, lists records 8 and 9 for the reasons stated earlier. (The discard file is not created.)
* CHEN ANALYST
7566 3450.00 2516-FEB-84
* CHIN ANALYST
7566 3450.00 2516-FEB-84
Case 5 demonstrates
The control file is ULCASE5.CTL.
-- Loads EMP records from first 23 characters
-- Creates and loads PROJ records for each PROJNO listed
-- for each employee
LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dsc'
1) REPLACE
2) INTO TABLE emp
(empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL)
2) INTO TABLE proj
-- PROJ has two columns, both not null: EMPNO and PROJNO
3) WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
3) projno POSITION(25:27) INTEGER EXTERNAL) -- 1st proj
3) INTO TABLE proj
4) WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
4) projno POSITION(29:31 INTEGER EXTERNAL) -- 2nd proj
2) INTO TABLE proj
5) WHEN projno != ' '
(empno POSITION(1:4) INTEGER EXTERNAL,
5) projno POSITION(33:35) INTEGER EXTERNAL) -- 3rd proj
Notes:
1) REPLACE specifies that if there is data in the tables to be loaded (EMP and PROJ), SQL*loader should delete the data before loading new rows.
2) Multiple INTO clauses load two tables, EMP and PROJ. The same set of records is processed three times, using different combinations of columns each time to load table PROJ.
3) WHEN loads only rows with non-blank project numbers. When PROJNO is defined as columns 25...27, rows are inserted into PROJ only if there is a value in those columns.
4) When PROJNO is defined as columns 29...31, rows are inserted into PROJ only if there is a value in those columns.
5) When PROJNO is defined as columns 33...35, rows are inserted into PROJ only if there is a value in those columns.
The following is datafile for Case 5:
1234 BAKER 10 9999 101 102 103
1234 JOKER 10 9999 777 888 999
2664 YOUNG 20 2893 425 abc 102
5321 OTOOLE 10 9999 321 55 40
2134 FARMER 20 4555 236 456
2414 LITTLE 20 5634 236 456 40
6542 LEE 10 4532 102 321 14
2849 EDDS xx 4555 294 40
4532 PERKINS 10 9999 40
1244 HUNT 11 3452 665 133 456
123 DOOLITTLE 12 9940 132
1453 MACDONALD 25 5532 200
Invoke SQL*Loader with a command such as:
sqlldr userid=scott/tiger control=ulcase5.ctl log=ulcase5.log
Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke SQL*Loader on your operating system, see your Oracle operating system-specific documentation.
The following is a portion of the log file:
Control File: ULCASE5.CTL
Data File: ULCASE5.DAT
Bad File: ULCASE5.BAD
Discard File: ULCASE5.DSC
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65336 bytes
Continuation: none specified
Path used: Conventional
Table EMP, loaded from every logical record.
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
------------- --------- --- ---- ---- ----------
EMPNO 1:4 4 CHARACTER
ENAME 6:15 10 CHARACTER
DEPTNO 17:18 2 CHARACTER
MGR 20:23 4 CHARACTER
Table PROJ, loaded when PROJNO != 0x202020(character ' ')
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
----------- --------- --- ---- ---- ----------
EMPNO 1:4 4 CHARACTER
PROJNO 25:27 3 CHARACTER
Table PROJ, loaded when PROJNO != 0x202020(character ' ')
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
----------- --------- --- ---- ---- ----------
EMPNO 1:4 4 CHARACTER
PROJNO 29:31 3 CHARACTER
Table PROJ, loaded when PROJNO != 0x202020(character ' ')
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
------------ --------- --- ---- ---- ----------
EMPNO 1:4 4 CHARACTER
PROJNO 33:35 3 CHARACTER
1) Record 2: Rejected - Error on table EMP, column DEPTNO.
1) ORA-00001: unique constraint (SCOTT.EMPIX) violated
1) ORA-01722: invalid number
1) Record 8: Rejected - Error on table EMP, column DEPTNO.
1) ORA-01722: invalid number
1) Record 3: Rejected - Error on table PROJ, column PROJNO.
1) ORA-01722: invalid number
Table EMP:
2) 9 Rows successfully loaded.
2) 3 Rows not loaded due to data errors.
2) 0 Rows not loaded because all WHEN clauses were failed.
2) 0 Rows not loaded because all fields were null.
Table PROJ:
3) 7 Rows successfully loaded.
3) 2 Rows not loaded due to data errors.
3) 3 Rows not loaded because all WHEN clauses were failed.
3) 0 Rows not loaded because all fields were null.
Table PROJ:
4) 7 Rows successfully loaded.
4) 3 Rows not loaded due to data errors.
4) 2 Rows not loaded because all WHEN clauses were failed.
4) 0 Rows not loaded because all fields were null.
Table PROJ:
5) 6 Rows successfully loaded.
5) 3 Rows not loaded due to data errors.
5) 3 Rows not loaded because all WHEN clauses were failed.
5) 0 Rows not loaded because all fields were null.
Space allocated for bind array: 5120 bytes (64 rows)
Space allocated for memory besides bind array: 46763 bytes
Total logical records skipped: 0
Total logical records read: 12
Total logical records rejected: 3
Total logical records discarded: 0
Notes:
1) Errors are not encountered in the same order as the physical records due to buffering (array batch). The bad file and discard file contain records in the same order as they appear in the log file.
2) Of the 12 logical records for input, three rows were rejected (rows for JOKER, YOUNG, and EDDS). No data was loaded for any of the rejected records.
3) Nine records met the WHEN clause criteria, and two (JOKER and YOUNG) were rejected due to data errors.
4) Ten records met the WHEN clause criteria, and three (JOKER, YOUNG, and EDDS) were rejected due to data errors.
5) Nine records met the WHEN clause criteria, and three (JOKER, YOUNG, and EDDS) were rejected due to data errors.
These are results of this execution of SQL*Loader:
SQL> SELECT empno, ename, mgr, deptno FROM emp;
EMPNO ENAME MGR DEPTNO
------ ------ ------ ------
1234 BAKER 9999 10
5321 OTOOLE 9999 10
2134 FARMER 4555 20
2414 LITTLE 5634 20
6542 LEE 4532 10
4532 PERKINS 9999 10
1244 HUNT 3452 11
123 DOOLITTLE 9940 12
1453 MACDONALD 5532 25
SQL> SELECT * from PROJ order by EMPNO;
EMPNO PROJNO
------ ------
123 132
1234 101
1234 103
1234 102
1244 665
1244 456
1244 133
1453 200
2134 236
2134 456
2414 236
2414 456
2414 40
4532 40
5321 321
5321 40
5321 55
6542 102
6542 14
6542 321
This case study loads the EMP table using the direct path load method and concurrently builds all indexes. It illustrates the following functions:
Note: Specify the name of the table into which you want to load data; otherwise, you will see LDR-927. Specifying DIRECT=TRUE as a command-line parameter is not an option when loading into a synonym for a table.
In this example, field positions and datatypes are specified explicitly.
The control file is ULCASE6.CTL.
LOAD DATA
INFILE 'ulcase6.dat'
INSERT
INTO TABLE emp
1) SORTED INDEXES (empix)
2) (empno POSITION(01:04) INTEGER EXTERNAL NULLIF empno=BLANKS,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL NULLIF mgr=BLANKS,
sal POSITION(32:39) DECIMAL EXTERNAL NULLIF sal=BLANKS,
comm POSITION(41:48) DECIMAL EXTERNAL NULLIF comm=BLANKS,
deptno POSITION(50:51) INTEGER EXTERNAL NULLIF deptno=BLANKS)
Notes:
1) The SORTED INDEXES clause identifies the indexes on which the data is sorted. This clause indicates that the datafile is sorted on the columns in the EMPIX index. This clause allows SQL*Loader to optimize index creation by eliminating the sort phase for this data when using the direct path load method.
2) The NULLIF...BLANKS
clause specifies that the column should be loaded as NULL if the field
in the datafile consists of all blanks. For more information, refer to
"Loading All-Blank Fields" ![[*]](jump.gif){kind=small}
.
Invoke SQL*Loader with a command such as:
sqlldr scott/tiger ulcase6.ctl log=ulcase6.log direct=true
Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke SQL*Loader on your operating system, see your Oracle operating system-specific documentation.
The following is a portion of the log file:
Control File: ULCASE6.CTL
Data File: ULCASE6.DAT
Bad File: ULCASE6.BAD
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct
Table EMP, loaded from every logical record.
Insert option in effect for this table: REPLACE
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
Column EMPNO is NULL if EMPNO = BLANKS
Column MGR is NULL if MGR = BLANKS
Column SAL is NULL if SAL = BLANKS
Column COMM is NULL if COMM = BLANKS
Column DEPTNO is NULL if DEPTNO = BLANKS
The following index(es) on table EMP were processed:
Index EMPIX was loaded.
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.
Bind array size not used in direct path.
Space allocated for memory besides bind array: 164342 bytes
Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0
In this case study, SQL*Loader's string processing functions extract data from a formatted report. It illustrates the following functions:
Note: This example creates a trigger that uses the last value of unspecified fields.
The following listing of the report shows the data to be loaded:
Today's Newly Hired Employees
Dept Job Manager MgrNo Emp Name EmpNo Salary (Comm)
---- -------- -------- ----- -------- ----- --------- ------
20 Salesman Blake 7698 Shepard 8061 $1,600.00 (3%)
Falstaff 8066 $1,250.00 (5%)
Major 8064 $1,250.00 (14%)
30 Clerk Scott 7788 Conrad 8062 $1,100.00
Ford 7369 DeSilva 8063 $800.00
Manager King 7839 Provo 8065 $2,975.00
In this case, a BEFORE INSERT trigger is required to fill in department number, job name, and manager's number when these fields are not present on a data line. When values are present, they should be saved in a global variable. When values are not present, the global variables are used.
The INSERT trigger and the package defining the global variables is:
CREATE OR REPLACE PACKAGE uldemo7 AS -- Global Package Variables
last_deptno NUMBER(2);
last_job VARCHAR2(9);
last_mgr NUMBER(4);
END uldemo7;
/
CREATE OR REPLACE TRIGGER uldemo7_emp_insert
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
IF :new.deptno IS NOT NULL THEN
uldemo7.last_deptno := :new.deptno; -- save value for later
ELSE
:new.deptno := uldemo7.last_deptno; -- use last valid value
END IF;
IF :new.job IS NOT NULL THEN
uldemo7.last_job := :new.job;
ELSE
:new.job := uldemo7.last_job;
END IF;
IF :new.mgr IS NOT NULL THEN
uldemo7.last_mgr := :new.mgr;
ELSE
:new.mgr := uldemo7.last_mgr;
END IF;
END;
/
Note: The phrase FOR EACH ROW is important. If it was not specified, the INSERT trigger would only fire once for each array of inserts because SQL*Loader uses the array interface.
The control file is ULCASE7.CTL.
LOAD DATA
INFILE 'ULCASE7.DAT'
APPEND
INTO TABLE emp
1) WHEN (57) = '.'
2) TRAILING NULLCOLS
3) (hiredate SYSDATE,
4) deptno POSITION(1:2) INTEGER EXTERNAL(3)
5) NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
6) NULLIF job=BLANKS "UPPER(:job)",
7) mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
8) "TO_NUMBER(:sal,'$99,999.99')",
9) comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%'
":comm * 100"
)
Notes:
1) The decimal point in column 57 (the salary field) identifies a line with data on it. All other lines in the report are discarded.
2) The TRAILING NULLCOLS clause causes SQL*Loader to treat any fields that are missing at the end of a record as null. Because the commission field is not present for every record, this clause says to load a null commission instead of rejecting the record when only six fields are found instead of the expected seven.
3) Employee's hire date is filled in using the current system date.
4) This specification generates a warning message because the specified length does not agree with the length determined by the field's position. The specified length (3) is used.
5) Because the report only shows department number, job, and manager when the value changes, these fields may be blank. This control file causes them to be loaded as null, and an RDBMS insert trigger fills in the last valid value.
6) The SQL string changes the job name to uppercase letters.
7) It is necessary to specify starting position here. If the job field and the manager field were both blank, then the job field's TERMINATED BY BLANKS clause would cause SQL*Loader to scan forward to the employee name field. Without the POSITION clause, the employee name field would be mistakenly interpreted as the manager field.
8) Here, the SQL string translates the field from a formatted character string into a number. The numeric value takes less space and can be printed with a variety of formatting options.
9) In this case, different initial and trailing delimiters pick the numeric value out of a formatted field. The SQL string then converts the value to its stored form.
Invoke SQL*Loader with a command such as:
sqlldr scott/tiger ulcase7.ctl ulcase7.log
The following is a portion of the log file:
1) SQL*Loader-307: Warning: conflicting lengths 2 and 3 specified
for column EMP.DEPTNO.
Control File: ulcase7.ctl
Data File: ulcase7.dat
Bad File: ulcase7.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 65536 bytes
Continuation: none specified
Path used: Conventional
Table EMP, loaded when 57:57 = 0X2e(character '.')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------- ---------- ----- ---- ---- -----------
DEPTNO 1:2 3 CHARACTER
JOB 7:14 8 WHT CHARACTER
MGR 28:31 4 WHT CHARACTER
ENAME 34:41 8 WHT CHARACTER
EMPNO NEXT * WHT CHARACTER
SAL 51 * WHT CHARACTER
COMM NEXT * ( CHARACTER
%
HIREDATE SYSDATE
Column DEPTNO is NULL if DEPTNO = BLANKS
Column JOB is NULL if JOB = BLANKS
Column JOB had SQL string
"UPPER(:job)"
applied to it.
Column MGR is NULL if MGR = BLANKS
Column ENAME had SQL string
"UPPER(:ename)"
applied to it.
Column SAL had SQL string
"TO_NUMBER(:sal,'$99,999.99')"
applied to it.
Column COMM had SQL string
":comm * 100"
applied to it.
2) Record 1: Discarded - failed all WHEN clauses.
Record 2: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Record 5: Discarded - failed all WHEN clauses.
Record 6: Discarded - failed all WHEN clauses.
Record 10: Discarded - failed all WHEN clauses.
Table EMP:
6 Rows successfully loaded.
0 Rows not loaded due to data errors.
2) 7 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 52480 bytes(64 rows)
Space allocated for memory besides bind array: 108185 bytes
Total logical records skipped: 0
Total logical records read: 13
Total logical records rejected: 0
2) Total logical records discarded: 7
Notes:
1) A warning is generated by the difference between the specified length and the length derived from the position specification.
2) The 6 header lines at the top of the report are rejected, as is the blank separator line in the middle.
After running the example, use ULCASE7E.SQL to drop the insert trigger and global-variable package.
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
 Product |
 Contents |
 Index |