In the last post, we introduced express mode in SQL Loader with an example. In that post, we mentioned that the input data file for express mode should match the column order in which the table is created. In this post, we will see why this is important and what happens when this is not followed. To understand this better, let us create a simple table with some character and number columns as shown below.
PDB1@ORCL> create table column_order
2 (counter number,
3 content varchar2(10)
4 )
5 /
Table created.
PDB1@ORCL> desc column_order
Name Null? Type
----------------------------------------- -------- ----------------------------
COUNTER NUMBER
CONTENT VARCHAR2(10)
PDB1@ORCL>
To test what happens if we swap the input data, let us create a test data file column_order.dat, with the contents as shown below:[oracle@localhost sqlldrtest]$ ls
column_order.dat
[oracle@localhost sqlldrtest]$ more column_order.dat
1,input1
input2,2
3,input3
input4,4
[oracle@localhost sqlldrtest]$
When you run a express mode load operation against this data file, you would expect two records to be loaded and two to be rejected as we have swapped the column order. Let us see how this works. Start SQL Loader and run the command to load the data file as per our earlier post. You should see an output as shown below:[oracle@localhost sqlldrtest]$ sqlldr hr table=column_order
Password:
SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jul 4 22:03:25 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: COLUMN_ORDER
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
Table COLUMN_ORDER:
2 Rows successfully loaded.
Check the log files:
column_order.log
column_order_%p.log_xt
for more information about the load.
[oracle@localhost sqlldrtest]$
And this is exactly that has happened. Two of our records got rejected as expected.This can be seen from the bad file that got generated following the load. The contents are dumped below:[oracle@localhost sqlldrtest]$ more column_order_6218.bad
input2,2
input4,4
[oracle@localhost sqlldrtest]$
The log files generated in this case confirms that the loading expects a number field data in column 1 of the data file and a character entry in column 2. This is shown below:
SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jul 4 22:03:25 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: COLUMN_ORDER
Data File: column_order.dat
Bad File: column_order_%p.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table
Table COLUMN_ORDER, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COUNTER FIRST * , CHARACTER
CONTENT NEXT * , CHARACTER
Generated control file for possible reuse:
OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'column_order'
APPEND
INTO TABLE COLUMN_ORDER
FIELDS TERMINATED BY ","
(
COUNTER,
CONTENT
)
End of generated control file for possible reuse.
enable parallel DML: ALTER SESSION ENABLE PARALLEL DML
creating external table "SYS_SQLLDR_X_EXT_COLUMN_ORDER"
CREATE TABLE "SYS_SQLLDR_X_EXT_COLUMN_ORDER"
(
"COUNTER" NUMBER,
"CONTENT" VARCHAR2(10)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'column_order_%p.bad'
LOGFILE 'column_order_%p.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"COUNTER" CHAR(255),
"CONTENT" CHAR(255)
)
)
location
(
'column_order.dat'
)
)REJECT LIMIT UNLIMITED
executing INSERT statement to load database table COLUMN_ORDER
INSERT /*+ append parallel(auto) */ INTO COLUMN_ORDER
(
COUNTER,
CONTENT
)
SELECT
"COUNTER",
"CONTENT"
FROM "SYS_SQLLDR_X_EXT_COLUMN_ORDER"
dropping external table "SYS_SQLLDR_X_EXT_COLUMN_ORDER"
Table COLUMN_ORDER:
2 Rows successfully loaded.
Run began on Fri Jul 04 22:03:25 2014
Run ended on Fri Jul 04 22:03:49 2014
Elapsed time was: 00:00:23.98
CPU time was: 00:00:00.05
We are now looking for a log file in the process which indicates why our records were rejected. This file has log_xt as extension and indicates what went wrong in this load process. This is shown below:[oracle@localhost sqlldrtest]$ more column_order_6218.log_xt
LOG file opened at 07/04/14 22:03:41
KUP-05004: Warning: Intra source concurrency disabled because parallel select
was not requested.
Field Definitions for table SYS_SQLLDR_X_EXT_COLUMN_ORDER
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Reject rows with all null fields
Fields in Data Source:
COUNTER CHAR (255)
Terminated by ","
Trim whitespace from left and right
CONTENT CHAR (255)
Terminated by ","
Trim whitespace from left and right
KUP-04021: field formatting error for field COUNTER
KUP-04023: field start is after end of record
KUP-04101: record 5 rejected in file /u01/oracle/sqlldrtest/column_order.dat
error processing column COUNTER in row 2 for datafile /u01/oracle/sqlldrtest/col
umn_order.dat
ORA-01722: invalid number
error processing column COUNTER in row 4 for datafile /u01/oracle/sqlldrtest/col
umn_order.dat
ORA-01722: invalid number
This file also shows why two of our records got rejected - we had varchar content to populate against a number field. This explains why column order is important in SQL Loader express mode. See you in a different tutorial next time.
No comments:
Post a Comment