Oracle8 Utilities Release 8.0 A58244-01 |
Library | Product | Contents | Index

# 4
SQL*Loader Case Studies

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:

- The Case Studies
- Case Study Files
- Tables Used in the Case Studies
- References and Notes
- Running the Case Study SQL Scripts
- Case 1: Loading Variable-Length Data
- Case 2: Loading Fixed-Format Fields
- Case 3: Loading a Delimited, Free-Format File
- Case 4: Loading Combined Physical Records
- Case 5: Loading Data into Multiple Tables
- Case 6: Loading Using the Direct Path Load Method
- Case 7: Extracting Data from a Formatted Report
- Case 8: Loading a Fixed Record Length Format File

## The Case Studies

This chapter contains the following case studies:

##### Case 1: Loading Variable-Length Data

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: Loading Fixed-Format Fields:

Loads a datafile with fixed-length fields, stream-format records, all records the same length.

##### Case 3: Loading a Delimited, Free-Format File

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: Loading Combined Physical Records

Combines multiple physical records into one logical record corresponding to one database row

##### Case 5: Loading Data into Multiple Tables

Loads data into multiple tables in one run

##### Case 6: Loading Using the Direct Path Load Method

Loads data using the direct path load method

##### Case 7: Extracting Data from a Formatted Report

Extracts data from a formatted report

##### Case 8: Loading a Fixed Record Length Format File

Loads a datafile using fixed record length and explicitly defined field positions and datatypes.

## Case Study Files

The distribution media for SQL*Loader contains files for each case:

- control files (for example, ULCASE1.CTL)
- data files (for example, ULCASE2.DAT)
- setup files (for example, ULCASE3.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 4-1 lists the files associated with each case:

##### Table 4-1 Case Studies and Their Related Files

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
8 | x | x | x

  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.

## Tables Used in the Case Studies

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.)

### Contents of Table EMP

    (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))

### Contents of Table DEPT

    (deptno                    NUMBER(2) NOT NULL,
      dname                      VARCHAR2(14),
      loc                          VARCHAR2(13))

## References and Notes

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.

## Running the Case Study SQL Scripts

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: Loading Variable-Length Data

Case 1 demonstrates

- A simple control file identifying one table and three columns to be loaded. See "Including Data in the Control File with BEGINDATA" on page 5-15.
- Including data to be loaded from the control file itself, so there is no separate datafile. See "Including Data in the Control File with BEGINDATA" on page 5-15.
- Loading data in stream format, with both types of delimited fields - terminated and enclosed. See "Delimited Fields" on page 5-64.

### Control File

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. Since no datatype is specified, the default is a character of length 255.
6. BEGINDATA specifies the beginning of the data.

### Invoking SQL*Loader

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.

### Log File

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: Loading Fixed-Format Fields

Case 2 demonstrates

- A separate datafile. See "Identifying Datafiles" on page 5-16.
- Data conversions. See "Datatype Conversions" on page 5-50.

In this case, the field positions and datatypes are specified explicitly.

### Control File

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.

### Datafile

Below are a few sample data lines from the file ULCASE2.DAT. Blank fields are set to null automatically.

    782  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

### Invoking SQL*Loader

Invoke SQL*Loader with a command such as:

    sqlldr userid=scott/tiger control=ulcase2.ctl log=ulcase2.log

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.

### Log File

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: Loading a Delimited, Free-Format File

Case 3 demonstrates

- Loading data (enclosed and terminated) in stream format. See "Delimited Fields" on page 5-64.
- Loading dates using the datatype DATE. See "DATE" on page 5-58.
- Using SEQUENCE numbers to generate unique keys for loaded data. See "Setting a Column to a Unique Sequence Number" on page 5-48.
- Using APPEND to indicate that the table need not be empty before inserting new records. See "Loading into Empty and Non-Empty Tables" on page 5-25.
- Using Comments in the control file set off by double dashes. See "Comments" on page 5-11.
- Overriding general specifications with declarations for individual fields. See "Specifying Field Conditions" on page 5-37.

### Control File

This control file loads the same table as in 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)       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 a maximum of 20. If a length is not specified, the length is a maximum of 20. If a length is not specified, then the length depends on the length of the date mask.
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.

### Invoking SQL*Loader

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.

### Log File

The following shows a portion of the log file:

    Control File:      ULCASE3.CTL
    Data File:         ULCASE3.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: Loading Combined Physical Records

Case 4 demonstrates:

- Combining multiple physical records to form one logical record with CONTINUEIF; see "Assembling Logical Records from Physical Records" on page 5-29.
- Inserting negative numbers.
- Indicating with REPLACE that the table should be emptied before the new data is inserted; see "Loading into Empty and Non-Empty Tables" on page 5-25.
- Specifying a discard file in the control file using DISCARDFILE; see "Specifying the Discard File" on page 5-21.
- Specifying a maximum number of discards using DISCARDMAX; see "Specifying the Discard File" on page 5-21.
- Rejecting records due to duplicate values in a unique index or due to invalid data values; see "Rejected Records" on page 5-20.

### Control 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:

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.

### Data File

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

#### Rejected Records

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.

### Invoking SQL*Loader

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.

### Log File

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

### Bad File

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: Loading Data into Multiple Tables

Case 5 demonstrates

- Loading multiple tables. See "Loading Data into Multiple Tables" on page 5-45.
- Using SQL*Loader to break down repeating groups in a flat file and load the data into normalized tables - one file record may generate multiple database rows
- Deriving multiple logical records from each physical record. See "Using Multiple INTO TABLE Statements" on page 5-43.
- Using a WHEN clause. See "Choosing which Rows to Load" on page 5-34.
- Loading the same field (EMPNO) into multiple tables.

### Control File

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.

### Data File

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

### Invoking SQL*Loader

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.

### Log File

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.

### Loaded Tables

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       ALBERT          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

## Case 6: Loading Using the Direct Path Load Method

This case study loads the EMP table using the direct path load method and concurrently builds all indexes. It illustrates the following functions:

- Use of the direct path load method to load and index data. See Chapter 8, "SQL*Loader: Conventional and Direct Path Loads".
- How to specify the indexes for which the data is pre-sorted. See "Pre-sorting Data for Faster Indexing" on page 8-16.
- Loading all-blank numeric fields as null. See "Loading All-Blank Fields" on page 5-72.
- The NULLIF clause. See "NULLIF Keyword" on page 5-71.

  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.

### Control File

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 indexes:presorting data:case studythe 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" on page 5-72.

### Invoking SQL*Loader

Run the script ULCASE6.SQL as SCOTT/TIGER then enter the following at the command line:

    sqlldr scott/tiger ulcase6.ctl direct=true log=ulcase6.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.

### Log File

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

## Case 7: Extracting Data from a Formatted Report

In this case study, SQL*Loader's string processing functions extract data from a formatted report. It illustrates the following functions:

- Using SQL*Loader with an INSERT trigger (see the chapter on database triggers in Oracle8 Application Developer's Guide
- Use of the SQL string to manipulate data; see "Applying SQL Operators to Fields" on page 5-78.
- Different initial and trailing delimiters; see "Specifying Delimiters" on page 5-60.
- Use of SYSDATE; see "Setting a Column to the Current Date" on page 5-47.
- Use of the TRAILING NULLCOLS clause; see "TRAILING NULLCOLS" on page 5-36.
- Ambiguous field length warnings; see "Conflicting Native Datatype Field Lengths" on page 5-57 and "Conflicting Character Datatype Field Lengths" on page 5-63.

Note: This example creates a trigger that uses the last value of unspecified fields.

### Data File

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

### Insert Trigger

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.

### Control File

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.

### Invoking SQL*Loader

Invoke SQL*Loader with a command such as:

    sqlldr scott/tiger ulcase7.ctl ulcase7.log

### Log File

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.

### Dropping the Insert Trigger and the Global-Variable Package

After running the example, use ULCASE7E.SQL to drop the insert trigger and global-variable package.

## Case 8: Loading a Fixed Record Length Format File

Case 8 demonstrates

- Loading using the fixed record length option.
- Using the "SORTED INDEX" clause.
- Partitioning of data. See Oracle8 Concepts for more information on partitioned data concepts.
- Explicitly defined field positions and datatypes.

### Control File

The control file is ULCASE8.CTL. It loads the lineitem table with fixed length records, partitioning the data according to shipdate. It also uses the "SORTED INDEXES" clause for direct path performance.

          LOAD DATA
    1)      INFILE `ulcase8.dat.dat' "fix 129"
          BADFILE `ulcase8.dat.bad'
          TRUNCATE
          INTO TABLE lineitem
    2,3)  SORTED INDEXES  (l_ship_idx) PARTITION ship_q1
    4)      (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:

1. Specifies that each record in the datafile is of fixed length (129 characters in this example). See "Input Data Formats" on page 3-6.
2. SORTED INDEXES allows SQL*Loader to optimize index creation by eliminating the sort phase for this data when using the direct path load method. See "Index Options" on page 5-36.
3. Use of the PARTITION keyword to specify the loading of a specific partition. In this example, only rows that meet the criteria for a first quarter ship date are loaded with all other rows being rejected.
4. Identifies the column name and location of the data in the datafile to be loaded into each column.

### Table Creation

In order to partition the data the lineitem table is created using four (4) 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
    )

### Input Data File

The datafile for this case, ULCASE8.DAT, is listed below. Each record is 129 characters in length. Note that five(5) 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 0lRywgniP7

### Invoking SQL*Loader

Invoke SQL*Loader with a command such as:

    sqlldr sqlldr/test control=ulcase8.ctl data=ulcase8.dat

Additional Information: The command "sqlldr" is a UNIX-specific invocation. To invoke SQL*Loader, see the Oracle operating system-specific documentation.

### Log File

The following shows a portion of the log file:

    Control File:      ULCASE8.CTL
        Data File:         ULCASE8.DAT
        Bad File:        ULCASE8.BAD
        Discard File:    none specified
      (Allow all discards)
    Number to load:    ALL
    Number to skip:    0
    Errors allowed:    50
    Bind array:        Test mode - (O/S dependent) default bindsize
    Continuation:      none specified
    Path used:         Conventional
    Table  LINEITEM, partition ship_q1, loaded from every logical record.
    Insert option in effect for this table: TRUNCATE

          Column Name                  Position   Len  Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- --------------
    L_ORDERKEY                            1:6     6           CHARACTER
    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.

    Total logical records skipped:          0
    Total logical records read:            10
    Total logical records rejected:         7
    Total logical records discarded:        0

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index