Release 2 (9.2)
Part Number A96652-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Utilities Release 2 (9.2) Part Number A96652-01 |
|
The case studies in this chapter illustrate some of the features of SQL*Loader. These case studies start simply and progress in complexity.
This chapter contains the following sections:
This chapter contains the following case studies:
CLOB column called resume to the table emp, uses a FILLER field (res_file), and loads multiple LOBFILEs into the emp table.VARRAY. Loads an order table that has a reference to the customer table and the order items in a VARRAY.The distribution media for SQL*Loader contains files for each case:
ulcase5.ctl)ulcase5.dat)ulcase5.sql)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.
Table 10-1 lists the files associated with each case.
| Case | .ctl |
.dat |
.sql |
|---|---|---|---|
|
1 |
Yes |
No |
Yes |
|
2 |
Yes |
Yes |
No |
|
3 |
Yes |
No |
Yes |
|
4 |
Yes |
Yes |
Yes |
|
5 |
Yes |
Yes |
Yes |
|
6 |
Yes |
Yes |
Yes |
|
7 |
Yes |
Yes |
Yes (S, E) |
|
8 |
Yes |
Yes |
Yes |
|
9 |
Yes |
Yes |
Yes |
|
10 |
Yes |
No |
Yes |
|
11 |
Yes |
Yes |
Yes |
|
Note: 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 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))
To check the results of a load, start SQL*Plus and perform a select operation from the table that was loaded in the case study. This is done, as follows:
scott/tiger by entering the following at the system prompt:
sqlplus scott/tiger
The SQL prompt is displayed.
SELECT statement to select all rows from the table that the case study loaded. For example, if the table emp was loaded, enter:
SQL> SELECT * FROM emp;
The contents of each row in the emp table will be displayed.
The summary at the beginning of each case study directs you to the sections of this guide that discuss the SQL*Loader feature being demonstrated.
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.
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:
LOAD DATA statement is required at the beginning of the control file.INFILE * specifies that the data is found in the control file and not in an external file.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.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.CHAR with a maximum length of 255.BEGINDATA specifies the beginning of the data.Take the following steps to run the case study.
scott/tiger by entering the following at the system prompt:
sqlplus scott/tiger
The SQL prompt is displayed.
SQL> @ulcase1
This prepares and populates tables for the case study and then returns you to the system prompt.
sqlldr USERID=scott/tiger CONTROL=ulcase1.ctl LOG=ulcase1.log
SQL*Loader loads the dept 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.
The following shows a portion of the log file:
Control File: ulcase1.ctl Data File: ulcase1.ctl 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 256000 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 * , 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: 49536 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 Run began on Wed Feb 27 14:10:13 2002 Run ended on Wed Feb 27 14:10:14 2002 Elapsed time was: 00:00:01.53 CPU time was: 00:00:00.20
Notes:
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:
LOAD DATA statement is required at the beginning of the control file.INFILE parameter.INTO TABLE statement is required to identify the table to be loaded into.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.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
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.
scott/tiger by entering the following at the system prompt:
sqlplus scott/tiger
The SQL prompt is displayed.
SQL> @ulcase1
This prepares and populates tables for the case study and then returns you to the system prompt.
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).
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 Run began on Wed Feb 27 14:17:39 2002 Run ended on Wed Feb 27 14:17:39 2002 Elapsed time was: 00:00:00.81 CPU time was: 00:00:00.15
Case 3 demonstrates:
DATE. See Datetime and Interval Datatypes.SEQUENCE numbers to generate unique keys for loaded data. See Setting a Column to a Unique Sequence Number.APPEND to indicate that the table need not be empty before inserting new records. See Table-Specific Loading Method.This control file loads the same table as in case 2, but it loads three additional columns (hiredate, projno, and loadseq). The demonstration table emp does not have columns projno and loadseq. 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:
INFILE * specifies that the data is found at the end of the control file.APPEND specifies that the data can be loaded even if the table already contains rows. That is, the table need not be empty.hiredate appears in the format DD-Month-YYYY. The length of the date field is specified to have a maximum of 20. The maximum length is in bytes, with default byte-length semantics. If character-length semantics were used instead, the length would be in characters. If a length is not specified, then the length depends on the length of the date mask.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.BEGINDATA specifies the end of the control information and the beginning of the data.comm.Take the following steps to run the case study.
scott/tiger by entering the following at the system prompt:
sqlplus scott/tiger
The SQL prompt is displayed.
SQL> @ulcase3
This prepares and populates tables for the case study and then returns you to the system prompt.
sqlldr USERID=scott/tiger CONTROL=ulcase3.ctl LOG=ulcase3.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.
The following shows a portion of the log file:
Control File: ulcase3.ctl Data File: ulcase3.ctl 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 256000 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: 134976 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 Run began on Wed Feb 27 14:25:29 2002 Run ended on Wed Feb 27 14:25:30 2002 Elapsed time was: 00:00:00.81 CPU time was: 00:00:00.15
Case 4 demonstrates:
CONTINUEIF; see Using CONTINUEIF to Assemble Logical Records.REPLACE that the table should be emptied before the new data is inserted; see Table-Specific Loading Method.DISCARDFILE; see Specifying the Discard File.DISCARDMAX; see Specifying the Discard File.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:
DISCARDFILE specifies a discard file named ulcase4.dsc.DISCARDMAX specifies a maximum of 999 discards allowed before terminating the run (for all practical purposes, this allows all discards).REPLACE specifies that if there is data in the table being loaded, then SQL*Loader should delete that data before loading new data.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 from the logical record. Note that column 1 in each physical record should then contain either an asterisk or a nondata value.The datafile for this case, ulcase4.dat, looks as follows. Asterisks are in the first position and, though not visible, a newline character is in position 20. 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 25 12-NOV-85 *7839 KING PRESIDENT 5500.00 25 05-APR-83 *7934 MILLER CLERK 7782 920.00 25 08-MAY-80 *7566 JONES MANAGER 7839 3123.75 25 17-JUL-85 *7499 ALLEN SALESMAN 7698 1600.00 300.00 25 3-JUN-84 *7654 MARTIN SALESMAN 7698 1312.50 1400.00 25 21-DEC-85 *7658 CHAN ANALYST 7566 3450.00 25 16-FEB-84 * CHEN ANALYST 7566 3450.00 25 16-FEB-84 *7658 CHIN ANALYST 7566 3450.00 25 16-FEB-84
The last two records are rejected, given two assumptions. If a unique index is 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.
Take the following steps to run the case study.
scott/tiger by entering the following at the system prompt:
sqlplus scott/tiger
The SQL prompt is displayed.
SQL> @ulcase4
This prepares and populates tables for the case study and then returns you to the system prompt.
sqlldr USERID=scott/tiger CONTROL=ulcase4.ctl LOG=ulcase4.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.
The following is a portion of the log file:
Control File: ulcase4.ctl Data File: ulcase4.dat Bad File: ulcase4.bad Discard File: ulcase4.dis (Allow 999 discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 256000 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. ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO") Record 9: Rejected - Error on table EMP. 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: 4608 bytes(64 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 9 Total logical records rejected: 2 Total logical records discarded: 0 Run began on Wed Feb 27 14:28:53 2002 Run ended on Wed Feb 27 14:28:54 2002 Elapsed time was: 00:00:00.91 CPU time was: 00:00:00.13
The bad file, shown in the following display, lists records 8 and 9 for the reasons stated earlier. (The discard file is not created.)
* CHEN ANALYST 7566 3450.00 25 16-FEB-84 *7658 CHIN ANALYST 7566 3450.00 25 16-FEB-84
Case 5 demonstrates:
WHEN clause. See Loading Records Based on a Condition.empno) into multiple tables.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
2) 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:
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.INTO TABLE 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.WHEN loads only rows with nonblank project numbers. When projno is defined as columns 25...27, rows are inserted into proj only if there is a value in those columns.projno is defined as columns 29...31, rows are inserted into proj only if there is a value in those columns.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
Take the following steps to run the case study.
scott/tiger by entering the following at the system prompt:
sqlplus scott/tiger
The SQL prompt is displayed.
SQL> @ulcase5
This prepares and populates tables for the case study and then returns you to the system prompt.
sqlldr USERID=scott/tiger CONTROL=ulcase5.ctl LOG=ulcase5.log
SQL*Loader loads the tables, 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.
The following is a portion of the log file:
Control File: ulcase5.ctl Data File: ulcase5.dat Bad File: ulcase5.bad Discard File: ulcase5.dis (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: 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. 1) ORA-00001: unique constraint (SCOTT.EMPIX) violated 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: 4096 bytes(64 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 12 Total logical records rejected: 3 Total logical records discarded: 0 Run began on Wed Feb 27 14:34:33 2002 Run ended on Wed Feb 27 14:34:34 2002 Elapsed time was: 00:00:01.00 CPU time was: 00:00:00.22
Notes:
joker, young, and edds). No data was loaded for any of the rejected records.WHEN clause criteria, two (joker and young) were rejected due to data errors.WHEN clause criteria, three (joker, young, and edds) were rejected due to data errors.WHEN clause criteria, three (joker, young, and edds) were rejected due to data errors.The following are sample SQL queries and their results:
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:
NULLIF clause. See Using the WHEN, NULLIF, and DEFAULTIF Clauses.NULL. See Loading All-Blank Fields.In this example, field positions and datatypes are specified explicitly.
The control file is ulcase6.ctl.
LOAD DATA INFILE 'ulcase6.dat' REPLACE 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:
SORTED INDEXES statement identifies the indexes on which the data is sorted. This statement indicates that the datafile is sorted on the columns in the empix index. It allows SQL*Loader to optimize index creation by eliminating the sort phase for this data when using the direct path load method.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 Using the WHEN, NULLIF, and DEFAULTIF Clauses.7499 ALLEN SALESMAN 7698 1600.00 300.00 30 7566 JONES MANAGER 7839 3123.75 20 7654 MARTIN SALESMAN 7698 1312.50 1400.00 30 7658 CHAN ANALYST 7566 3450.00 20 7782 CLARK MANAGER 7839 2572.50 10 7839 KING PRESIDENT 5500.00 10 7934 MILLER CLERK 7782 920.00 10
Take the following steps to run the case study.
scott/tiger by entering the following at the system prompt:
sqlplus scott/tiger
The SQL prompt is displayed.
SQL> @ulcase6
This prepares and populates tables for the case study and then returns you to the system prompt.
DIRECT=true.Otherwise, conventional path is used as the default, which will result in failure of the case study.
sqlldr USERID=scott/tiger CONTROL=ulcase6.ctl LOG=ulcase6.log DIRECT=true
SQL*Loader loads the emp table using the direct path load method, 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.
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 NULL if MGR = BLANKS SAL 32:39 8 CHARACTER NULL if SAL = BLANKS COMM 41:48 8 CHARACTER NULL if COMM = BLANKS DEPTNO 50:51 2 CHARACTER NULL if EMPNO = BLANKS The following index(es) on table EMP were processed: index SCOTT.EMPIX loaded successfully with 7 keys 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. Column array rows : 5000 Stream buffer bytes: 256000 Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 7 Total logical records rejected: 0 Total logical records discarded: 0 Total stream buffers loaded by SQL*Loader main thread: 2 Total stream buffers loaded by SQL*Loader load thread: 0 Run began on Wed Feb 27 13:21:29 2002 Run ended on Wed Feb 27 13:21:32 2002 Elapsed time was: 00:00:02.96 CPU time was: 00:00:00.22
In this case study, SQL*Loader string-processing functions extract data from a formatted report. This example creates a trigger that uses the last value of unspecified fields. This case illustrates the following:
INSERT trigger. See Oracle9i Application Developer's Guide - Fundamentals for more information on database triggers.SYSDATE; see Setting a Column to the Current Date.TRAILING NULLCOLS clause; see TRAILING NULLCOLS Clause.In this case study, a BEFORE INSERT trigger is required to fill in the 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 global variables package are created when you execute the ulcase7s.sql script.
The package defining the global variables looks as follows:
CREATE OR REPLACE PACKAGE uldemo7 AS -- Global Package Variables last_deptno NUMBER(2); last_job VARCHAR2(9); last_mgr NUMBER(4); END uldemo7; /
The definition of the INSERT trigger looks as follows:
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 |
Be sure to execute the ulcase7e.sql script to drop the INSERT trigger and the global variables package before continuing with the rest of the case studies. See Running Case Study 7.
The control file is ulcase7.ctl.
LOAD DATA INFILE 'ulcase7.dat' DISCARDFILE 'ulcase7.dis' 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:
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 seven fields are found instead of the expected eight.TERMINATED BY WHITESPACE 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.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/Commission ---- -------- -------- ----- -------- ----- ----------------- 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
Take the following steps to run the case study.
scott/tiger by entering the following at the system prompt:
sqlplus scott/tiger
The SQL prompt is displayed.
SQL> @ulcase7s
This prepares and populates tables for the case study and then returns you to the system prompt.
sqlldr USERID=scott/tiger CONTROL=ulcase7.ctl LOG=ulcase7.log
SQL*Loader extracts data from the report, 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.
ulcase7e.sql script as follows:
SQL> @ulcase7e
The following is a portion of the log file:
1) SQL*Loader-307: Warning: conflicting lengths 2 and 3 specified for column DEPTNO table EMP Control File: ulcase7.ctl Data File: ulcase7.dat Bad File: ulcase7.bad Discard File: ulcase7.dis (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 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 ------------------------------ ---------- ----- ---- ---- --------------------- HIREDATE SYSDATE DEPTNO 1:2 3 CHARACTER NULL if DEPTNO = BLANKS JOB 7:14 8 WHT CHARACTER NULL if JOB = BLANKS SQL string for column : "UPPER(:job)" MGR 28:31 4 WHT CHARACTER NULL if MGR = BLANKS ENAME 34:41 8 WHT CHARACTER SQL string for column : "UPPER(:ename)" EMPNO NEXT * WHT CHARACTER SAL 51 * WHT CHARACTER SQL string for column : "TO_NUMBER(:sal,'$99,999.99')" COMM NEXT * ( CHARACTER % SQL string for column : ":comm * 100" 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: 51584 bytes(64 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 13 Total logical records rejected: 0 2) Total logical records discarded: 7 Run began on Wed Feb 27 14:54:03 2002 Run ended on Wed Feb 27 14:54:04 2002 Elapsed time was: 00:00:00.99 CPU time was: 00:00:00.21
Notes:
Case 8 demonstrates:
The control file is ulcase8.ctl. It loads the lineitem table with fixed-length records, partitioning the data according to shipment date.
LOAD DATA 1) INFILE 'ulcase8.dat' "fix 129" BADFILE 'ulcase8.bad' TRUNCATE INTO TABLE lineitem PARTITION (ship_q1) 2) (l_orderkey position (1:6) char, l_partkey position (7:11) char, l_suppkey position (12:15) char, l_linenumber position (16:16) char, l_quantity position (17:18) char, l_extendedprice position (19:26) char, l_discount position (27:29) char, l_tax position (30:32) char, l_returnflag position (33:33) char, l_linestatus position (34:34) char, l_shipdate position (35:43) char, l_commitdate position (44:52) char, l_receiptdate position (53:61) char, l_shipinstruct position (62:78) char, l_shipmode position (79:85) char, l_comment position (86:128) char)
Notes:
In order to partition the data, the lineitem table is created using four partitions according to the shipment date:
create table lineitem (l_orderkey number, l_partkey number, l_suppkey number, l_linenumber number, l_quantity number, l_extendedprice number, l_discount number, l_tax number, l_returnflag char, l_linestatus char, l_shipdate date, l_commitdate date, l_receiptdate date, l_shipinstruct char(17), l_shipmode char(7), l_comment char(43)) partition by range (l_shipdate) ( partition ship_q1 values less than (TO_DATE('01-APR-1996', 'DD-MON-YYYY')) tablespace p01, partition ship_q2 values less than (TO_DATE('01-JUL-1996', 'DD-MON-YYYY')) tablespace p02, partition ship_q3 values less than (TO_DATE('01-OCT-1996', 'DD-MON-YYYY')) tablespace p03, partition ship_q4 values less than (TO_DATE('01-JAN-1997', 'DD-MON-YYYY')) tablespace p04 )
The datafile for this case, ulcase8.dat, looks as follows. Each record is 128 bytes in length. Five blanks precede each record in the file.
1 151978511724386.60 7.04.0NO09-SEP-6412-FEB-9622-MAR-96DELIVER IN PERSONTRUCK iPBw4mMm7w7kQ zNPL i261OPP 1 2731 73223658958.28.09.06NO12-FEB-9628-FEB-9620-APR-96TAKE BACK RETURN MAIL 5wM04SNyl0AnghCP2nx lAi 1 3370 3713 810210.96 .1.02NO29-MAR-9605-MAR-9631-JAN-96TAKE BACK RETURN REG AIRSQC2C 5PNCy4mM 1 5214 46542831197.88.09.06NO21-APR-9630-MAR-9616-MAY-96NONE AIR Om0L65CSAwSj5k6k 1 6564 6763246897.92.07.02NO30-MAY-9607-FEB-9603-FEB-96DELIVER IN PERSONMAIL CB0SnyOL PQ32B70wB75k 6Aw10m0wh 1 7403 160524 31329.6 .1.04NO30-JUN-9614-MAR-9601 APR-96NONE FOB C2gOQj OB6RLk1BS15 igN 2 8819 82012441659.44 0.08NO05-AUG-9609-FEB-9711-MAR-97COLLECT COD AIR O52M70MRgRNnmm476mNm 3 9451 721230 41113.5.05.01AF05-SEP-9629-DEC-9318-FEB-94TAKE BACK RETURN FOB 6wQnO0Llg6y 3 9717 1834440788.44.07.03RF09-NOV-9623-DEC-9315-FEB-94TAKE BACK RETURN SHIP LhiA7wygz0k4g4zRhMLBAM 3 9844 1955 6 8066.64.04.01RF28-DEC-9615-DEC-9314-FEB-94TAKE BACK RETURN REG AIR6nmBmjQkgiCyzCQBkxPPOx5j4hB 0lRywgniP1297
Take the following steps to run the case study.
scott/tiger by entering the following at the system prompt:
sqlplus scott/tiger
The SQL prompt is displayed.
SQL> @ulcase8
This prepares and populates tables for the case study and then returns you to the system prompt.
sqlldr USERID=scott/tiger CONTROL=ulcase8.ctl LOG=ulcase8.log
SQL*Loader partitions and loads the data, 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.
The following shows a portion of the log file:
Control File: ulcase8.ctl Data File: ulcase8.dat File processing option string: "fix 129" Bad File: ulcase8.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 LINEITEM, partition SHIP_Q1, loaded from every logical record. Insert option in effect for this partition: TRUNCATE Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- L_ORDERKEY 1:6 6 CHARACTER L_PARTKEY 7:11 5 CHARACTER L_SUPPKEY 12:15 4 CHARACTER L_LINENUMBER 16:16 1 CHARACTER L_QUANTITY 17:18 2 CHARACTER L_EXTENDEDPRICE 19:26 8 CHARACTER L_DISCOUNT 27:29 3 CHARACTER L_TAX 30:32 3 CHARACTER L_RETURNFLAG 33:33 1 CHARACTER L_LINESTATUS 34:34 1 CHARACTER L_SHIPDATE 35:43 9 CHARACTER L_COMMITDATE 44:52 9 CHARACTER L_RECEIPTDATE 53:61 9 CHARACTER L_SHIPINSTRUCT 62:78 17 CHARACTER L_SHIPMODE 79:85 7 CHARACTER L_COMMENT 86:128 43 CHARACTER Record 4: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Record 5: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Record 6: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Record 7: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Record 8: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Record 9: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Record 10: Rejected - Error on table LINEITEM, partition SHIP_Q1. ORA-14401: inserted partition key is outside specified partition Table LINEITEM, partition SHIP_Q1: 3 Rows successfully loaded. 7 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: 11008 bytes(64 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 10 Total logical records rejected: 7 Total logical records discarded: 0 Run began on Wed Feb 27 15:02:28 2002 Run ended on Wed Feb 27 15:02:29 2002 Elapsed time was: 00:00:01.37 CPU time was: 00:00:00.20
Case 9 demonstrates:
CLOB column called resume to the table empres_file)emp tableThe control file is ulcase9.ctl. It loads new records into emp, including a resume for each employee. Each resume is contained in a separate file.
LOAD DATA INFILE * INTO TABLE emp REPLACE FIELDS TERMINATED BY ',' ( empno INTEGER EXTERNAL, ename CHAR, job CHAR, mgr INTEGER EXTERNAL, sal DECIMAL EXTERNAL, comm DECIMAL EXTERNAL, deptno INTEGER EXTERNAL, 1) res_file FILLER CHAR, 2) "RESUME" LOBFILE (res_file) TERMINATED BY EOF NULLIF res_file = 'NONE' ) BEGINDATA 7782,CLARK,MANAGER,7839,2572.50,,10,ulcase91.dat 7839,KING,PRESIDENT,,5500.00,,10,ulcase92.dat 7934,MILLER,CLERK,7782,920.00,,10,ulcase93.dat 7566,JONES,MANAGER,7839,3123.75,,20,ulcase94.dat 7499,ALLEN,SALESMAN,7698,1600.00,300.00,30,ulcase95.dat 7654,MARTIN,SALESMAN,7698,1312.50,1400.00,30,ulcase96.dat 7658,CHAN,ANALYST,7566,3450.00,,20,NONE
Notes:
resume column is loaded as a CLOB. The LOBFILE function specifies the field name in which the name of the file that contains data for the LOB field is provided. See Loading LOB Data from LOBFILEs for more information.>>ulcase91.dat<< Resume for Mary Clark Career Objective: Manage a sales team with consistent record-breaking performance. Education: BA Business University of Iowa 1992 Experience: 1992-1994 - Sales Support at MicroSales Inc. Won "Best Sales Support" award in 1993 and 1994 1994-Present - Sales Manager at MicroSales Inc. Most sales in mid-South division for 2 years >>ulcase92.dat<< Resume for Monica King Career Objective: President of large computer services company Education: BA English Literature Bennington, 1985 Experience: 1985-1986 - Mailroom at New World Services 1986-1987 - Secretary for sales management at New World Services 1988-1989 - Sales support at New World Services 1990-1992 - Salesman at New World Services 1993-1994 - Sales Manager at New World Services 1995 - Vice President of Sales and Marketing at New World Services 1996-Present - President of New World Services >>ulcase93.dat<< Resume for Dan Miller Career Objective: Work as a sales support specialist for a services company Education: Plainview High School, 1996 Experience: 1996 - Present: Mail room clerk at New World Services >>ulcase94.dat<< Resume for Alyson Jones Career Objective: Work in senior sales management for a vibrant and growing company Education: BA Philosophy Howard Univerity 1993 Experience: 1993 - Sales Support for New World Services 1994-1995 - Salesman for New World Services. Led in US sales in both 1994 and 1995. 1996 - present - Sales Manager New World Services. My sales team has beat its quota by at least 15% each year. >>ulcase95.dat<< Resume for David Allen Career Objective: Senior Sales man for agressive Services company Education: BS Business Administration, Weber State 1994 Experience: 1993-1994 - Sales Support New World Services 1994-present - Salesman at New World Service. Won sales award for exceeding sales quota by over 20% in 1995, 1996. >>ulcase96.dat<< Resume for Tom Martin Career Objective: Salesman for a computing service company Education: 1988 - BA Mathematics, University of the North Experience: 1988-1992 Sales Support, New World Services 1993-present Salesman New World Services
Take the following steps to run the case study.
scott/tiger by entering the following at the system prompt:
sqlplus scott/tiger
The SQL prompt is displayed.
SQL> @ulcase9
This prepares and populates tables for the case study and then returns you to the system prompt.
sqlldr USERID=scott/tiger CONTROL=ulcase9.ctl LOG=ulcase9.log
SQL*Loader loads the emp 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.
The following shows a portion of the log file:
Control File: ulcase9.ctl Data File: ulcase9.ctl Bad File: ulcase9.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: REPLACE Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- EMPNO FIRST * , CHARACTER ENAME NEXT * , CHARACTER JOB NEXT * , CHARACTER MGR NEXT * , CHARACTER SAL NEXT * , CHARACTER COMM NEXT * , CHARACTER DEPTNO NEXT * , CHARACTER RES_FILE NEXT * , CHARACTER (FILLER FIELD) "RESUME" DERIVED * EOF CHARACTER Dynamic LOBFILE. Filename in field RES_FILE NULL if RES_FILE = 0X4e4f4e45(character 'NONE') 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: 132096 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 Run began on Wed Feb 27 15:06:49 2002 Run ended on Wed Feb 27 15:06:50 2002 Elapsed time was: 00:00:01.01 CPU time was: 00:00:00.20
Case 10 demonstrates:
VARRAY.VARRAY.
|
Note: Case study 10 requires that the |
LOAD DATA INFILE * CONTINUEIF THIS (1) = '*' INTO TABLE customers REPLACE FIELDS TERMINATED BY "," ( CUST_NO CHAR, NAME CHAR, ADDR CHAR ) INTO TABLE orders REPLACE FIELDS TERMINATED BY "," ( order_no CHAR, 1) cust_no FILLER CHAR, 2) cust REF (CONSTANT 'CUSTOMERS', cust_no), 1) item_list_count FILLER CHAR, 3) item_list VARRAY COUNT (item_list_count) ( 4) item_list COLUMN OBJECT ( 5) item CHAR, cnt CHAR, price CHAR ) ) ) 6) BEGINDATA *00001,Spacely Sprockets,15 Space Way, *00101,00001,2, *Sprocket clips, 10000, .01, *Sprocket cleaner, 10, 14.00 *00002,Cogswell Cogs,12 Cogswell Lane, *00100,00002,4, *one quarter inch cogs,1000,.02, *one half inch cog, 150, .04, *one inch cog, 75, .10, *Custom coffee mugs, 10, 2.50
Notes:
FILLER field. The FILLER field is assigned values from the data field to which it is mapped. See Specifying Filler Fields for more information.REF field. See Loading REF Columns for more information.item_list is stored in a VARRAY.item_list identifies the datatype of each element of the VARRAY. Here, the datatype is a COLUMN OBJECT.VARRAY. The list is enclosed in parentheses. See Loading Column Objects for more information.BEGINDATA parameter.Take the following steps to run the case study.
scott/tiger by entering the following at the system prompt:
sqlplus scott/tiger
The SQL prompt is displayed.
SQL> @ulcase10
This prepares and populates tables for the case study and then returns you to the system prompt.
sqlldr USERID=scott/tiger CONTROL=ulcase10.ctl LOG=ulcase10.log
SQL*Loader loads the data, 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.
The following shows a portion of the log file:
Control File: ulcase10.ctl Data File: ulcase10.ctl Bad File: ulcase10.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: 1:1 = 0X2a(character '*'), in current physical record Path used: Conventional Table CUSTOMERS, loaded from every logical record. Insert option in effect for this table: REPLACE Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- CUST_NO FIRST * , CHARACTER NAME NEXT * , CHARACTER ADDR NEXT * , CHARACTER Table ORDERS, loaded from every logical record. Insert option in effect for this table: REPLACE Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- ORDER_NO NEXT * , CHARACTER CUST_NO NEXT * , CHARACTER (FILLER FIELD) CUST DERIVED REF Arguments are: CONSTANT 'CUSTOMERS' CUST_NO ITEM_LIST_COUNT NEXT * , CHARACTER (FILLER FIELD) ITEM_LIST DERIVED * VARRAY Count for VARRAY ITEM_LIST_COUNT *** Fields in ITEM_LIST ITEM_LIST DERIVED * COLUMN OBJECT *** Fields in ITEM_LIST.ITEM_LIST ITEM FIRST * , CHARACTER CNT NEXT * , CHARACTER PRICE NEXT * , CHARACTER *** End of fields in ITEM_LIST.ITEM_LIST *** End of fields in ITEM_LIST Table CUSTOMERS: 2 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. Table ORDERS: 2 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: 149120 bytes(64 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 2 Total logical records rejected: 0 Total logical records discarded: 0 Run began on Wed Feb 27 14:05:29 2002 Run ended on Wed Feb 27 14:05:31 2002 Elapsed time was: 00:00:02.07 CPU time was: 00:00:00.20
In this case study, SQL*Loader loads data from a datafile in a Unicode character set. This case study parallels case study 3, except that it uses the character set UTF16 and a maximum length is specified for the empno and deptno fields. The data must be in a separate datafile because the CHARACTERSET keyword is specified. This case study demonstrates the following:
The control file is ulcase11.ctl.
LOAD DATA 1) CHARACTERSET UTF16 2) BYTEORDER LITTLE INFILE ulcase11.dat REPLACE INTO TABLE emp 3) FIELDS TERMINATED BY X'002c' OPTIONALLY ENCLOSED BY X'0022' 4) (empno INTEGER EXTERNAL (5), ename, job, mgr, hiredate DATE(20) "DD-Month-YYYY", sal, comm, 5) deptno CHAR(5) TERMINATED BY ":", projno, loadseq SEQUENCE(MAX,1) )
Notes:
CHARACTERSET keyword is UTF16. SQL*Loader will convert the data from the UTF16 character set to the datafile character set. This line also tells SQL*Loader to use character-length semantics for the load.BYTEORDER LITTLE tells SQL*Loader that the data in the datafile is in little endian byte order. SQL*Loader checks the byte order of the system on which it is running to determine if any byte-swapping is necessary. In this example, all the character data in UTF16 is byte-order dependent.TERMINATED BY and OPTIONALLY ENCLOSED BY clauses both specify hexadecimal strings. The X'002c' is the encoding for a comma (,) in UTF-16 big endian format. The X'0022' is the encoding for a double quotation mark (") in big endian format. Because the datafile is in little endian format, SQL*Loader swaps the bytes before checking for a match.
If these clauses were specified as character strings instead of hexadecimal strings, SQL*Loader would convert the strings to the datafile character set (UTF16) and byte-swap as needed before checking for a match.
empno, hiredate, and deptno fields is interpreted as characters, not bytes.TERMINATED BY clause for the deptno field is specified using the character string ":". SQL*Loader converts the string to the datafile character set (UTF16) and byte-swaps as needed before checking for a match.
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, 300.00, 30:103 7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50, 1400.00, 30:103 7658, "Chan", "Analyst", 7566, 03-May-1982, 3450,, 20:101
Take the following steps to run the case study.
scott/tiger by entering the following at the system prompt:
sqlplus scott/tiger
The SQL prompt is displayed.
SQL> @ulcase11
This prepares the table emp for the case study and then returns you to the system prompt.
sqlldr USERID=scott/tiger CONTROL=ulcase11.ctl LOG=ulcase11.log
SQL*Loader loads the table emp, 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.
The following shows a portion of the log file for case study 11:
Control File: ulcase11.ctl Character Set utf16 specified for all input. 1) Using character length semantics. 2) Byteorder little endian specified. Processing datafile as little endian. 3) SQL*Loader running on a big endian platform. Swapping bytes where needed. Data File: ulcase11.dat Bad File: ulcase11.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: REPLACE Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- 4) EMPNO FIRST 10 , O(") CHARACTER ENAME NEXT * , O(") CHARACTER JOB NEXT * , O(") CHARACTER MGR NEXT * , O(") CHARACTER 4) HIREDATE NEXT 40 , O(") DATE DD-Month-YYYY SAL NEXT * , O(") CHARACTER COMM NEXT * , O(") CHARACTER DEPTNO NEXT 10 : O(") CHARACTER 4) 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: 104768 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 Run began on Wed Feb 27 16:33:47 2002 Run ended on Wed Feb 27 16:33:49 2002 Elapsed time was: 00:00:01.74 CPU time was: 00:00:00.20
Notes:
BYTEORDER LITTLE was specified in the control file. This tells SQL*Loader that the byte order for the UTF16 character data in the datafile is little endian.len heading are in bytes even though character-length semantics were used. However, the maximum lengths are adjusted based on the maximum size, in bytes, of a character in UTF16. All characters in UTF16 are 2 bytes. Therefore, the sizes given for empno and projno (5) are multiplied by 2, resulting in a maximum size of 10 bytes.
Similarly, the hiredate maximum size (20) is multiplied by 2, resulting in a maximum size of 40 bytes.
To see the results of this execution of SQL*Loader, execute the following query at the SQL prompt:
SQL> SELECT * FROM emp;
The results of the query look as follows (the formatting may be slightly different on your display):
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO PROJNO LOADSEQ ------ ------ --------- ----- --------- -------- ----- ------- ------ -------- 7782 Clark Manager 7839 09-JUN-81 2572.50 10 101 1 7839 King President 17-NOV-81 5500.00 10 102 2 7934 Miller Clerk 7782 23-JAN-82 920.00 10 102 3 7566 Jones Manager 7839 02-APR-81 3123.75 20 101 4 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO PROJNO LOADSEQ ------ ------ --------- ----- --------- -------- ----- ------- ------ -------- 7499 Allen Salesman 7698 20-FEB-81 1600.00 300 30 103 5 7654 Martin Salesman 7698 28-SEP-81 1312.50 1400 30 103 6 7658 Chan Analyst 7566 03-MAY-82 3450.00 20 101 7 7 rows selected.
The output for the table is displayed in the character set US7ASCII, which is the normal default character set when the NLS_LANG parameter is not defined. SQL*Loader converts the output from the database character set, which normally defaults to WE8DEC, to the character set specified for your session by the NLS_LANG parameter.
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|