![[*]](jump.gif)
.
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.