Oracle7 Server Utilities User's Guide | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
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
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 |
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.
(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))
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.
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.
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.
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.
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
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.
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
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.
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.
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
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.
*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
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.
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
* CHEN ANALYST
7566 3450.00 2516-FEB-84
* CHIN ANALYST
7566 3450.00 2516-FEB-84
-- 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.
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
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.
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.
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
In this example, field positions and datatypes are specified explicitly.
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" .
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.
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
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
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.
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.
sqlldr scott/tiger ulcase7.ctl ulcase7.log
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.
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |