We discussed a brief introduction to the express mode in SQL Loader in Oracle 12C with an example in one of our earlier posts. In this post, let us discuss some of the FAQs / Tips on data loading in this mode with suitable examples. This is a pretty long post, so you can jump to your section suitably by looking into the summary section below.
How to specify a different input file name in express mode?
How can I use wild cards in input file?
How to load multiple data files to same table in express mode?
Can I use a different file extension other than .dat during load?
Do I get an error with my wild card pattern does not match any file in express mode?
What happens if the number of columns in input file does not match source table?
Can I load invisible columns with Express mode?
How can I use wild cards in input file?
How to load multiple data files to same table in express mode?
Can I use a different file extension other than .dat during load?
Do I get an error with my wild card pattern does not match any file in express mode?
What happens if the number of columns in input file does not match source table?
Can I load invisible columns with Express mode?
How to specify a different input file name in express mode?
You can specify a different data file name using
DATA keyword. An example of such an usage in express mode is show below:***********
Input Table
***********
PDB1@ORCL> desc dat_file
Name Null? Type
----------------------------------------- -------- ----------------------------
COUNTER NUMBER
CONTENT VARCHAR2(10)
**********************************************************
SQL Loader Express Mode Operation with Different Data file
***********************************************************
[oracle@localhost sqlldrtest]$ ls
column_order.dat
[oracle@localhost sqlldrtest]$ sqlldr hr table=dat_file DATA=column_order.dat
Password:
SQL*Loader: Release 12.1.0.1.0 - Production on Sat Jul 5 20:55:19 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: DAT_FILE
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
Table DAT_FILE:
2 Rows successfully loaded.
Check the log files:
dat_file.log
dat_file_%p.log_xt
for more information about the load.
*****************************
Table Data following the load
*****************************
PDB1@ORCL> select * from dat_file
2 /
COUNTER CONTENT
---------- ----------
1 input1
3 input3
How can I use wild cards in input file?
This scenario is applicable if you have multiple input files with some pattern that is common among them, so you can try using wild cards. In the example presented below, we have three data files each with two records. We load them all in one go in express mode using wild card. A separate bad file for each matching input file gets created in this process. Also, we had one record rejected in each of our file as the last line in the file was an empty line.The commands and the generated log file in this case is presented below:
PDB1@ORCL> desc dat_file
Name Null? Type
----------------------------------------- -------- ----------------------------
COUNTER NUMBER
CONTENT VARCHAR2(10)
PDB1@ORCL>
******************************************************
SQL Loader - Load Files with Wild Card in Express Mode
********************************************************
[oracle@localhost sqlldrtest]$ ls
col1.dat col3.dat column.dat
[oracle@localhost sqlldrtest]$ sqlldr hr table=dat_file DATA=col*.dat
Password:
SQL*Loader: Release 12.1.0.1.0 - Production on Sat Jul 5 21:33:15 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: DAT_FILE
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
Table DAT_FILE:
6 Rows successfully loaded.
Check the log files:
dat_file.log
dat_file_%p.log_xt
for more information about the load.
[oracle@localhost sqlldrtest]$
**********************************************************************
Contents of Log File - You can find all matching files being processed
**********************************************************************
LOG file opened at 07/05/14 21:33:20
Location specification col*.dat has a wild card
Matching files are:
col1.dat
col3.dat
column.dat
Field Definitions for table SYS_SQLLDR_X_EXT_DAT_FILE
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
LOG file opened at 07/05/14 21:33:21
KUP-05004: Warning: Intra source concurrency disabled because parallel select was not requested.
Location specification col*.dat has a wild card
Matching files are:
Matching files are:
col1.dat
col3.dat
column.dat
Field Definitions for table SYS_SQLLDR_X_EXT_DAT_FILE
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 3 rejected in file /u01/oracle/sqlldrtest/col1.dat
KUP-04021: field formatting error for field COUNTER
KUP-04023: field start is after end of record
KUP-04101: record 3 rejected in file /u01/oracle/sqlldrtest/col3.dat
KUP-04021: field formatting error for field COUNTER
KUP-04023: field start is after end of record
KUP-04101: record 3 rejected in file /u01/oracle/sqlldrtest/column.dat
How to load multiple data files to same table in express mode?
This one is very easy and straightforward. Just separate file names with a comma in the DATA keyword. What's more, you can use wild card in this setup also. See below for an example usage:
[oracle@localhost sqlldrtest]$ ls
a.dat b.dat c.dat
***********************************
SQL Loader Express mode - Specify Multiple file names in input
No need for dat extension
*************************************
[oracle@localhost sqlldrtest]$ sqlldr hr table=dat_file data=a,b,c
Password:
SQL*Loader: Release 12.1.0.1.0 - Production on Sat Jul 5 22:03:15 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: DAT_FILE
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
Table DAT_FILE:
6 Rows successfully loaded.
Check the log files:
dat_file.log
dat_file_%p.log_xt
for more information about the load.
[oracle@localhost sqlldrtest]$
Can I use a different file extension other than .dat during load?
If you do not specify a file extension, the default is assumed as .dat. However, you can specify a different extension if you have a requirement like that. I tried *.csv for example, and it worked. You must specify the extension where it is different to a *.dat - if you don't specify one you get an error during execution. See below for an example:
****************************
we are loading three CSV files in express mode.
Specify file names with right extension.
******************************
[oracle@localhost sqlldrtest]$ ls
a.csv b.csv c.csv
[oracle@localhost sqlldrtest]$ sqlldr hr table=dat_file data=a.csv,b.csv,c.csv
Password:
SQL*Loader: Release 12.1.0.1.0 - Production on Sat Jul 5 22:09:53 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: DAT_FILE
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
Table DAT_FILE:
6 Rows successfully loaded.
Check the log files:
dat_file.log
dat_file_%p.log_xt
for more information about the load.
******************************************
You get an error if you try to load a CSV file without specifying an extension.
This is because, SQL Loader looks for a file with *.dat extension by default.
It raises an error when such a file cannnot be found.
You have to fix the file extension to resolve this.
*****************************************
[oracle@localhost sqlldrtest]$ sqlldr hr table=dat_file data=a,b,c
Password:
SQL*Loader: Release 12.1.0.1.0 - Production on Sat Jul 5 22:10:10 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: DAT_FILE
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-807: error loading table
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file a.dat in SYS_SQLLDR_XT_TMPDIR_00000 not found
Table DAT_FILE:
0 Rows successfully loaded.
Check the log files:
dat_file.log
dat_file_%p.log_xt
for more information about the load.
Do I get an error with my wild card pattern does not match any file in express mode?
Tried doing this with a pattern of *.txt and the following errors were observed in express mode. You want to make sure your input pattern is correct and matches the files you are trying to load:
[oracle@localhost sqlldrtest]$ sqlldr hr table=dat_file data=*.txt
Password:
SQL*Loader-537: Warning: no files found for data parameter wildcard specification (*.txt).
SQL*Loader-539: No files found for any data parameter specification in express mode.
What happens if the number of columns in input file does not match source table?
This is an interesting scenario. To simulate this, I had a table with two columns and I created a .dat file with three columns, each line. When I triggered SQL Loader in express mode following that, the observation was that the third column was skipped and the remaining two columns were loaded into the file. The logs in this case are provided below:
****************************
Input table with only two columns
********************************
PDB1@ORCL> desc dat_file
Name Null? Type
----------------------------------------- -------- ----------------------------
COUNTER NUMBER
CONTENT VARCHAR2(10)
PDB1@ORCL> truncate table dat_file
2 /
Table truncated.
*************************************
Input DAT file contents - note that there are three columns in the input file
******************************************
[oracle@localhost sqlldrtest]$ more a.dat
1,input1,aa
3,input3,bb
************************
Test run of SQL Loader in Express mode with results
***************************
[oracle@localhost sqlldrtest]$ sqlldr hr table=dat_file data=a
Password:
SQL*Loader: Release 12.1.0.1.0 - Production on Sat Jul 5 22:26:04 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: DAT_FILE
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
Table DAT_FILE:
2 Rows successfully loaded.
Check the log files:
dat_file.log
dat_file_%p.log_xt
for more information about the load.
[oracle@localhost sqlldrtest]$
**********************
Contents of the file following the load.
Note that only the first two columns are loaded.
The third column in the input file got ignored.
*****************************
PDB1@ORCL> select * from dat_file
2 /
COUNTER CONTENT
---------- ----------
1 input1
3 input3
PDB1@ORCL>
******************************
Control file + external table file script.
Only two columns from the file are being considered.
********************************************
How to load data into invisible columns in Express mode?
Refer to our post on invisible columns, that discusses how to load invisible columns using SQL Loader.
how to load file from oracle client using sqlloader?
ReplyDelete