SQLLOADER direct path load and sequence

Jan 27 posted in Oracle (RSS), 606 views.

SQL> SELECT * FROM V $ VERSION;

BANNER
-------------------------------------------------- --------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL / SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> DESC TEST;

Name is empty? Type
----------------------------------------- -------- -- ---------------------------

EMPNO NOT NULL NUMBER
ENAME VARCHAR2 (20)
SAL NUMBER (7,2)
DEPTNO NUMBER (3)

SQL> SELECT * FROM TEST;

No rows selected

SQL> CREATE SEQUENCE S;

Sequence has been created.

SQL> HOST TYPE TEST.CTL

OPTIONS (DIRECT = TRUE)
UNRECOVERABLE
LOAD DATA
INFILE *
TRUNCATE

INTO TABLE TEST
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(ENAME, SAL, DEPTNO, EMPNO "S. NEXTVAL")
BEGINDATA
ALLEN, 1600,30
JONES, 3123.75,20
MARTIN, 1312.5,30
CHAN, 3450,20
CLARK, 2572.5,10
KING, 5500,10
MILLER, 920,10

SQL> HOST SQLLDR ADMIN / ADMIN CONTROL = TEST.CTL

SQL * Loader: Release 10.2.0.1.0 - Production on Wednesday January 27 13:12:18 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Load completed - logical record count 7.

SQL> SELECT * FROM TEST;

No rows selected

The log file contains the following contents:

Record 1: rejected - Table TEST error.
ORA-00604: recursive SQL Level 1 error
ORA-01400: unable to NULL insert ( "ADMIN". "TEST". "EMPNO")

Records 2: been rejected - the table TEST error.
ORA-00604: recursive SQL Level 1 error
ORA-01400: unable to NULL insert ( "ADMIN". "TEST". "EMPNO")

Record 3: Denied - the table TEST error.
ORA-00604: recursive SQL Level 1 error
ORA-01400: unable to NULL insert ( "ADMIN". "TEST". "EMPNO")

Record 4: Denied - the table TEST error.
ORA-00604: recursive SQL Level 1 error
ORA-01400: unable to NULL insert ( "ADMIN". "TEST". "EMPNO")

Record 5: Denied - the table TEST error.
ORA-00604: recursive SQL Level 1 error
ORA-01400: unable to NULL insert ( "ADMIN". "TEST". "EMPNO")

Record 6: Denied - the table TEST error.
ORA-00604: recursive SQL Level 1 error
ORA-01400: unable to NULL insert ( "ADMIN". "TEST". "EMPNO")

Record 7: Denied - the table TEST error.
ORA-00604: recursive SQL Level 1 error
ORA-01400: unable to NULL insert ( "ADMIN". "TEST". "EMPNO")

Table TEST:
0 rows loaded successfully.
Due to data errors, line 7 is not loaded.
Since all WHEN clauses failed, 0 line is not loaded.
Since all fields are empty, the 0-line is not loaded.

As the direct path load will bypass the SQL engine, so we do not generate the sequence, resulting in a value for the EMPNO column provides empty. The EMPNO is the primary key column, it does not allow empty. Resulting in an error.

Remove the primary key of the table TEST to see.

SQL> ALTER TABLE TEST DROP PRIMARY KEY;

The table has changed.

SQL> DESC TEST;
Name is empty? Type
----------------------------------------- -------- -- ---------------------------

EMPNO NUMBER
ENAME VARCHAR2 (20)
SAL NUMBER (7,2)
DEPTNO NUMBER (3)

SQL> HOST TYPE TEST.CTL

OPTIONS (DIRECT = TRUE)
UNRECOVERABLE
LOAD DATA
INFILE *
TRUNCATE
INTO TABLE TEST
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(ENAME, SAL, DEPTNO, EMPNO "S. NEXTVAL")
BEGINDATA
ALLEN, 1600,30
JONES, 3123.75,20
MARTIN, 1312.5,30
CHAN, 3450,20
CLARK, 2572.5,10
KING, 5500,10
MILLER, 920,10

SQL> HOST SQLLDR ADMIN / ADMIN CONTROL = TEST.CTL

SQL * Loader: Release 10.2.0.1.0 - Production on Wednesday January 27 13:31:46 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Load completed - logical record count 7.

SQL> SELECT * FROM TEST;

EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
ALLEN 1600 30
JONES 3123.75 20
MARTIN 1312.5 30
CHAN 3450 20
CLARK 2572.5 10
KING 5500 10
MILLER 920 10

Have chosen 7 rows.

Remove the NOT NULL constraint can be seen can be loaded successfully, but our EMPNO column is empty. For the DIRECT = TRUE to SQLLDR loading custom sequences can not be used. To use a custom sequence, SQLLDR have to load with conventional path mode. If you must use DIRECT = TRUE way with a sequence of loading data, we need to SQLLDR provide SEQUENCE function.

SQL> SELECT * FROM TEST;

EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
1 ALLEN 1600 30
2 JONES 3123.75 20
3 MARTIN 1312.5 30
4 CHAN 3450 20
5 CLARK 2572.5 10
6 KING 5500 10
7 MILLER 920 10

Have chosen 7 rows.

Table has 7 data. Maximum EMPNO is 7.

SQL> ALTER TABLE TEST ADD PRIMARY KEY (EMPNO);

The table has changed.

SQL> HOST TYPE TEST.CTL

OPTIONS (DIRECT = TRUE)
UNRECOVERABLE
LOAD DATA
INFILE *
APPEND - Here used the APPEND mode
INTO TABLE TEST
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(ENAME, SAL, DEPTNO, EMPNO SEQUENCE (MAX, 1))
BEGINDATA
ALLEN, 1600,30
JONES, 3123.75,20
MARTIN, 1312.5,30
CHAN, 3450,20
CLARK, 2572.5,10
KING, 5500,10
MILLER, 920,10

SQL> HOST SQLLDR ADMIN / ADMIN CONTROL = TEST.CTL

SQL * Loader: Release 10.2.0.1.0 - Production on Wednesday January 27 13:55:01 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Load completed - logical record count 7.

SQL> SELECT * FROM TEST;

EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
1 ALLEN 1600 30
2 JONES 3123.75 20
3 MARTIN 1312.5 30
4 CHAN 3450 20
5 CLARK 2572.5 10
6 KING 5500 10
7 MILLER 920 10
8 ALLEN 1600 30
9 JONES 3123.75 20
10 MARTIN 1312.5 30
11 CHAN 3450 20

EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
12 CLARK 2572.5 10
13 KING 5500 10
14 MILLER 920 10

Has selected 14 rows.

Do not forget the SEQUENCE table TEST with the need to modify. Otherwise, the next will be the only constraint error reported.

SQL> SELECT MAX (EMPNO) FROM TEST;

MAX (EMPNO)
----------
14

SQL> DROP SEQUENCE S;

Sequence has been deleted.

SQL> CREATE SEQUENCE S START WITH 1 INCREMENT BY 1;

Sequence has been created.

Finally, we want to check on our table in the existence of a unique constraint violations. Since the use of direct path load time, if the table has a primary key field, or unique constraints, and the loading of data in violation of unique constraint, then the index set of related SQLLOADER to be invalid, to continue loading. After loading, the index does not automatically set for effective, it needs manual intervention DBA.

SQL> COL CONSTRAINT_NAME FORMAT A20
SQL> COL CONSTRAINT_TYPE FORMAT A20
SQL> COL INDEX_NAME FORMAT A20
SQL> COL INDEX_TYPE FORMAT A20

SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TEST';

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
-------------------- -------------------- --------
SYS_C005400 P ENABLED
ENAME_UNIQUE U ENABLED

Table TEST the ENAME as the only constraint.

SQL> SELECT INDEX_NAME, INDEX_TYPE, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'TEST';

INDEX_NAME INDEX_TYPE STATUS
-------------------- -------------------- --------
SYS_C005400 NORMAL VALID
ENAME_UNIQUE NORMAL UNUSABLE
TEST_DEPTNO_IDX NORMAL VALID

As the load during the ENAME column violated a unique constraint, the associated index is set to be invalid.

The following is part of the contents of the log file content:

Table TEST has dealt with the following indexes:
Index ADMIN.ENAME_UNIQUE can not be used because:
ORA-00001: unique constraint violation (ADMIN.ENAME_UNIQUE)
Index ADMIN.SYS_C005400 has been successfully loaded, with seven keyword index ADMIN.TEST_DEPTNO_IDX has been successfully loaded, with seven keywords

Table TEST:
7 rows loaded successfully.
Due to data errors, 0 line is not loaded.
Since all WHEN clauses failed, 0 line is not loaded.
Since all fields are empty, the 0-line is not loaded.

The following is an application of conventional path load with a sequence of examples of

SQL> HOST TYPE TEST.CTL

LOAD DATA
INFILE *
TRUNCATE
INTO TABLE TEST
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(ENAME, SAL, DEPTNO, EMPNO "S. NEXTVAL")
BEGINDATA
ALLEN, 1600,30
JONES, 3123.75,20
MARTIN, 1312.5,30
CHAN, 3450,20
CLARK, 2572.5,10
KING, 5500,10
MILLER, 920,10

SQL> HOST SQLLDR ADMIN / ADMIN CONTROL = TEST.CTL

SQL * Loader: Release 10.2.0.1.0 - Production on Wednesday January 27 13:48:56 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 7

SQL> SELECT * FROM TEST;

EMPNO ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
1 ALLEN 1600 30
2 JONES 3123.75 20
3 MARTIN 1312.5 30
4 CHAN 3450 20
5 CLARK 2572.5 10
6 KING 5500 10
7 MILLER 920 10

Have chosen 7 rows.