Skip to Main Content
  • Questions
  • SQL*Loader and tab delimited input data

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Steve.

Asked: August 10, 2000 - 1:58 pm UTC

Last updated: March 15, 2011 - 8:59 am UTC

Version: 8.1.5

Viewed 50K+ times! This question is

You Asked

I have an input file that contains data that is tab delimited containing info like firstname, lastname, city, state, zip. The fields are not enclosed in quotes or anything else. The problem arises when data for a field (like lastname) is missing. The appropriate tab delimiter is in the file, but SQL*Loader seems to want to lump this all into one piece of whitespace and therefore thinks it is missing some data. Is there anyway to specify a tab delimiter to SQL*Loader without it thinking it should check for whitespace instead?

and Tom said...

If you use

LOAD DATA
INFILE *
INTO TABLE DEPT
replace
FIELDS TERMINATED BY X'9'

(DEPTNO, DNAME, LOC)
BEGINDATA
12 SARATOGA
10 ACCOUNTING CLEVELAND
11 ART SALEM
13 FINANCE BOSTON
21 SALES PHILA.
22 SALES ROCHESTER
42 INT'L SAN FRAN

It'll work (note: there are two tabs between 12 and SARATOGA in the above -- it loads 12 into deptno, SARATOGA into LOC)

Rating

  (40 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very helpful.....was really great

Riaz Shahid, October 20, 2001 - 5:26 am UTC

Thats gre8

laLocA, August 13, 2002 - 7:56 am UTC

Thank you very much - I really missed this information about such a common problem in the official reference.



Works great, less filling

Dave Archbell, December 18, 2002 - 5:21 pm UTC

We used to put the control characters in the file.
This method works great. Can you point to the
complete list of codes for character substitution?

Tom Kyte
December 18, 2002 - 6:14 pm UTC

they are just ascii codes?

like carriage return = 0D (decimal 13), line feed = 0A (decimal 10) and so on...

Sagi, December 18, 2002 - 10:35 pm UTC

Hi Tom,

As usual excellent.

In your solution what is ??
X'9'

Thanx in advance.

Regards,
Sagi

Tom Kyte
December 19, 2002 - 7:35 am UTC

tab

error

ashraf, December 19, 2002 - 3:01 am UTC

hi tom when i go to search in your sit many times i get

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Tom Kyte
December 19, 2002 - 7:40 am UTC

so, would you like to share an example with me of a search string that causes this because I myself search this site hundreds of times (because people asking questions refuse to themselves for some reason) and I've never hit that.




error

A reader, December 19, 2002 - 8:02 am UTC

this happen when i click on search link before i submit any query and i always search in your site without any proplem but in the last week this happen round 7-10 times

Tom Kyte
December 19, 2002 - 8:04 am UTC

I cannot reproduce -- do you have exact steps? is it still happening?

Answer to Sagi from India

Leonid Birman, December 19, 2002 - 10:21 am UTC

The X'9' is Hexadecimal Format -> X
and Hex 9 is the ASCII code of the TAB character.


trim trailing blank space and other

Vikas Sharma, March 26, 2003 - 7:16 pm UTC

Hi Tom,

I would like to load the data into a table using sql loader. My data file looks likes this:

|br_cod|br_name |group_|open_dte |
|001001|A.D.A.S East |001 |11/11/98 |
|001004|A.D.A.S National |001 |11/11/98 |
|001007|A.D.A.S South West |001 |11/11/98 |

My problem is
1. I would like to remove trailing white space from all the columns. I have use rtrim like
LOAD DATA
INFILE branches.txt
INTO TABLE v
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( br_cod CHAR(4000) "RTRIM(:br_cod)"
,br_name CHAR(4000) "RTRIM(:br_name)"
,group_ CHAR(4000) "RTRIM(:group_)"
,open_dte CHAR(4000) "RTRIM(:open_dte )"
)

Or there is some other way also.

2. When i insert data into the table the first column gets null values, second col get the values of br_cod third get values of second col br_name...

This is because of the '|' before the first column. HOw can i solve this problem. The one way i think is using filler for column one. Will it work fine. Or kindly suggest me some way of doing it.

Thanks,

Regards,
Vikas sahrma

Tom Kyte
March 26, 2003 - 7:33 pm UTC

1) If you used POSITION instead of delimited, it would trim trailing blanks.

2) using POSITION would solve that as well, or just add:

( X filler
,br_cod CHAR(4000) "RTRIM(:br_cod)"
,br_name


Thanks

Vikas Sharma, March 26, 2003 - 7:39 pm UTC

Hi Tom,

Thanks For the immediate reply this solves my problem.

Regards,

Vikas Sharma

About the numeric or value error in the search field.

Martin, March 27, 2003 - 4:37 am UTC

Hi Tom,

I've noticed a little "feature" with the search field as well every now and then, but it's only if you supply a HUGE number of characters into the search field, and you get the following error :

ERR-1004 Unable to Compute Function computation_point=BEFORE_HEADER computation_type=FUNCTION_BODY
Back

But, then again, as IF you'd ever hit this because we're talking about approx 2000 characters! ;-)

ORA-06502: PL/SQL: numeric or value error: character string buffer too small


Tom Kyte
March 27, 2003 - 7:53 am UTC

but it does catch it quite nicely doesn't it ;)

Mark several Rows in Oracle Forms

Mary, March 27, 2003 - 8:56 am UTC

Hi Tom,

I would like to mark several rows in data block using Shift+ mouse click in Oracle Form. How can I do this?
Please, help me!

Tom Kyte
March 27, 2003 - 10:17 am UTC

that is a list box feature -- it is not a feature of a default block. Sorry.

What about Null characters

Chetan Kashikar, May 01, 2003 - 6:57 am UTC

Can I use X'0' for null delimitters?

Tom Kyte
May 01, 2003 - 12:21 pm UTC

SQL*Loader-350: Syntax error at line 5.
Illegal hexadecimal literal specified for delimiter - not valid characters
FIELDS TERMINATED BY X'0'

doesn't appear so -- i didn't try "really hard" tho. x'0' would be a strange delimiter.

Skips columns when tabs are back-to-back

Matt Davis, February 07, 2006 - 12:46 pm UTC

I tried this and find when there isn't any data between the tabs, it doesn't load null into the appropriate column.

Ex My data
<tab>X<tab><tab>Y<tab>
My table
A varchar2(10), B varchar2(10), C varchar2(10), D varchar2(10)

I assumed I would get
A = null, b=X, C=null, D=Y

I get A=X,B=Y,C=null,D=null

into table EDI_INSTNAME
fields terminated by X'9' optionally enclosed by '"'
trailing NULLCOLS

What am I missing?

Tom Kyte
February 08, 2006 - 1:37 am UTC

you are missing the entire example. create table, entire ctl file - you know, something I can run, observe your results, look to fix...

How to include constants?

Geetha, December 21, 2006 - 6:02 pm UTC

Hi Tom,

This is exactly what I want, in addition I would like to include constants to this load file. Say, ins_process 'LOAD' and id seq.nextval too?

Lets say I have dept structure as this...

create table dept(deptno number, dname varchar2(20), loc varchar2(20), ins_process varchar2(20), id number)
/

When I included that after the actual columns am getting errors. I would like to include (deptno, dname, loc, ins_process 'LOAD', id seq.nextval).

Thanks,
Geetha


If you use

LOAD DATA
INFILE *
INTO TABLE DEPT
replace
FIELDS TERMINATED BY X'9'
(DEPTNO, DNAME, LOC)
BEGINDATA
12 SARATOGA
10 ACCOUNTING CLEVELAND
11 ART SALEM
13 FINANCE BOSTON
21 SALES PHILA.
22 SALES ROCHESTER
42 INT'L SAN FRAN

It'll work (note: there are two tabs between 12 and SARATOGA in the above -- it
loads 12 into deptno, SARATOGA into LOC)


Tom Kyte
December 22, 2006 - 6:17 am UTC

use trailing nullcols and add your two columns to the end of your list.


ops$tkyte%ORA10GR2> !cat t.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ','
trailing nullcols
(DEPTNO, DNAME, LOC,
 id "s.nextval",
 ins_process constant 'LOAD')
BEGINDATA
12,SARATOGA
10,ACCOUNTING,CLEVELAND
11,ART,SALEM
13,FINANCE,BOSTON
21,SALES,PHILA.
22,SALES,ROCHESTER
42,INT'L,SAN,FRAN

ops$tkyte%ORA10GR2> !sqlldr / t

SQL*Loader: Release 10.2.0.2.0 - Production on Fri Dec 22 06:16:07 2006

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

Commit point reached - logical record count 7

ops$tkyte%ORA10GR2> select * from dept;

    DEPTNO DNAME      LOC                  INS_P         ID
---------- ---------- -------------------- ----- ----------
        12 SARATOGA                        LOAD           8
        10 ACCOUNTING CLEVELAND            LOAD           9
        11 ART        SALEM                LOAD          10
        13 FINANCE    BOSTON               LOAD          11
        21 SALES      PHILA.               LOAD          12
        22 SALES      ROCHESTER            LOAD          13
        42 INT'L      SAN                  LOAD          14

7 rows selected.

 

Thanks a bunch...

Geetha, December 22, 2006 - 3:59 pm UTC

Thanks a lot Tom. You are truly wonderful.

Happy holidays!!!

Charset

A reader, December 23, 2006 - 11:05 am UTC

Great advice as always.

Tom, shouldn't the X'9' trick be said to work on single-byte charsets only ? I suppose 9 is not the ascii code of TAB on a multi-byte charset.

Thanks!
Tom Kyte
December 24, 2006 - 9:13 am UTC

could be - sure.

tab delimited input file with OPTIONALLY ENCLOSED clause

kuldeep, January 24, 2007 - 12:13 pm UTC

Hello Tom,

I am facing a proble while loading tab delimited file with null values in columns. I am describing the case as below.

Kindly advise.

SQL> desc tabtest
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRSTNAME VARCHAR2(30)
MIDDLENAME VARCHAR2(30)
LASTNAME VARCHAR2(30)


tabtest.txt (3 fields, tab delimited)
==========================
kuldeep singh rawat
"manoj" "kumar"
vinod kumar
==========================



LOAD DATA
INFILE 'c:\tabtest.txt'
BADFILE 'c:\tabtest.bad'
DISCARDFILE 'c:\tabtest.dsc'
REPLACE
iNTO TABLE tabtest
FIELDS TERMINATED BY X'9'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(firstname,middlename,lastname)




>sqlldr userid=kuldeep/kuldeep control=c:\tabtest.ctl log=c:\tabtest.log

SQL> select * from tabtest;

FIRSTNAME MIDDLENAME LASTNAME
------------------------------ ------------------------------ ------------------------------
kuldeep singh rawat
manoj kumar
vinod kumar


********* When I comments OPTIONALLY ENCLOSED BY clause, then it works *********

LOAD DATA
INFILE 'c:\tabtest.txt'
BADFILE 'c:\tabtest.bad'
DISCARDFILE 'c:\tabtest.dsc'
REPLACE
iNTO TABLE tabtest
FIELDS TERMINATED BY X'9'
--OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(firstname,middlename,lastname)


>sqlldr userid=kuldeep/kuldeep control=c:\tabtest.ctl log=c:\tabtest.log


SQL> select * from tabtest;

FIRSTNAME MIDDLENAME LASTNAME
------------------------------ ------------------------------ ------------------------------
kuldeep singh rawat
"manoj" "kumar"
vinod kumar

sqlloader with tab delimited file

kuleeep, January 27, 2007 - 2:18 am UTC

Hi Tom,

In the above question, when I include OPTIONALLY ENCLOSED clause in my control file it does ignore null values and load data in incorrect fileds of table but when I exclude OPTIONALLY ENCLOSED '"' statement it does loads data with null values but now my problem is that I get double quote (") in my table columns data.

How can I overcome this problem?

Thanks and regards,

same doubt

madhu, December 01, 2008 - 12:20 pm UTC

I have the same question as above.

Kindly please answer the above quetsion for tab delimited data load.

tab delimited control file

madhu, December 01, 2008 - 5:16 pm UTC

The script for loaidng tab delimited can be written as follows: It works good for me

-- Loads data even though few fileds are null values.
-- make sure the tempraray table columns data types length match with the raw data file
-- NULLIF is used to let the field be null if a particular field has a null value
-- x'09' is used for tab delimiting
-- below are few sample coloumns 
-- a temprary table should be created in oracle with appropriate data types and length matching the tab delimted text field.

LOAD DATA
INFILE './xyz.txt'
BADFILE './xyz.BAD'
DISCARDFILE './xyz.DSC' DISCARDMAX 50
INSERT INTO TABLE TEMP
FIELDS TERMINATED BY x'09'
TRAILING NULLCOLS
( Column1     NULLIF (Column1="NULL"),
  Column2     NULLIF (Column2="NULL"),
  Column3     NULLIF(Column3="NULL"),  
  Column4     NULLIF (Column4="NULL"),
  Column5     "MM/DD/YY"  NULLIF(Column5="NULL"),
  Column6     NULLIF(Column6="NULL"),
 )


-- SQLLOAD COMMAND : sqlldr control=TEMPCONTROLFILE.ctl

A reader, April 21, 2009 - 4:15 pm UTC

I assume that NULLIF can be used in a scenario where you have control over modifying control file for individual columns but what if control file is dynamically generated and the column list(dynamically generated by some other process) is passed to control file as parameter. In this scenario, how to handle the column that can be often null(in data file)? What option needs to be specified in control file that inserts a null into the that column rather than shifting remaining columns( following that column) and hence disrupting other column's data load?
Thanks in advance
Tom Kyte
April 21, 2009 - 5:20 pm UTC

I guess the only answer would be to have the thing that dynamically generates the controlfile - you know - generate a proper and appropriate controlfile?


If you give us an example to study, maybe we can suggest something (but frankly, if the generated control file loads the data wrong - then - the generated control file is wrong - which means "go back to process generating controlfile"

If you have delimited data - you do not need nullif
if you have positional data - you do not need nullif

nullif is used to conditionally set a field to NULL "IF" some condition is true. It doesn't have anything to do with data that is null in the input file

Reading the Values across the Segments in SQL Loader control file..

Rajeshwaran, Jeyabal, April 22, 2009 - 10:26 am UTC

Hi Tom,

I have a scenario to load up the data to the Oracle 10g Database with SQL Loader Approch. Here it is below.
 CREATE TABLE T
 (
   segment_identifier VARCHAR2(3),
   Contract_nbr       VARCHAR2(5),
   Segment_Value      VARCHAR2(4000)
 );  

 SELECT segment_identifier,nvl(Contract_NBR,0) as Contract_NBR
 FROM T 
 ORDER BY segment_identifier;

 SEGMENT_IDENTIFIER CONTRACT_NBR
 AAA    0
 BBB    0
 CCC    0
 CCC    0
 CCC    0
 CCC    0
 YYY    0
 ZZZ    0
    The Control file created for this approch is below.

 LOAD DATA
 INFILE *
 TRUNCATE
 INTO TABLE T 
 WHEN segment_identifier ='BBB' TRAILING NULLCOLS
 (
  segment_identifier POSITION(1:3),
  Contract_nbr    POSITION(1:1) " PKG_PACK1.PROCESS_BBB(:Contract_nbr) ", 
  Segment_Value    POSITION(1:1024)
 )
 INTO TABLE T 
 WHEN segment_identifier ='CCC' TRAILING NULLCOLS
 (
  segment_identifier POSITION(1:3),
  Contract_nbr    POSITION(1:1) " pkg_pack1.GET_BBB ", 
  Segment_Value    POSITION(1:1024)
 )
 INTO TABLE T 
 WHEN segment_identifier !='CCC' AND segment_identifier !='BBB' TRAILING NULLCOLS
 (
  segment_identifier POSITION(1:3),
  Contract_nbr    FILLER, 
  Segment_Value    POSITION(1:1024)
 )

  The data file is below.
 AAASH9561000000152320090421TEST  
 BBB0000001R5863           
 CCC0000001   10755151     
 CCC0000002   10755155     
 CCC0000003   10755166     
 CCC0000004   10755168     
 YYY0000001R58630000004    
 ZZZSH956100000015230000001

  The package used in the control file is available here.
 CREATE OR REPLACE PACKAGE PKG_PACK1 AS
 
  G_VALUE VARCHAR2(50);

  FUNCTION PROCESS_BBB(V_VALUE VARCHAR2)  RETURN VARCHAR2;
  FUNCTION GET_BBB  RETURN VARCHAR2;

 END PKG_PACK1;
 /
 CREATE OR REPLACE PACKAGE BODY PKG_PACK1 AS

  FUNCTION PROCESS_BBB(v_value VARCHAR2)  RETURN VARCHAR2 AS
  BEGIN
  PKG_PACK1.g_value := SUBSTR(v_value,11,15);
  RETURN PKG_PACK1.g_value;      
  END PROCESS_BBB;

  FUNCTION GET_BBB RETURN VARCHAR2 AS
  BEGIN
  RETURN PKG_PACK1.g_value;
  END;

 END;  
 /


QUESTIONS
1) I need to takeup the values between the position 11 to 15, Present in the BBB data segment and need to place it in the Column " Contract_nbr ",
so i created the pacakge PKG_PACK1
a) So when reading the BBB data segment this will call the function PKG_PACK1.PROCESS_BBB
return the Substring in the position 11 to 15 and assigns the Global Variable PKG_PACK1.g_value
b) When reading the CCC data Segment the value in the Global package variable will be substituted in the Column " Contract_nbr "
of the subsequent CCC Segments.
2) Can you please clarify me why the above approch specified in the Question 1 doesn't works for me.
3) Can you please how this can be achieved?

Thanks,
Rajesh.
Tom Kyte
April 24, 2009 - 3:41 pm UTC

I will strongly encourage you NOT TO go down this path. Do you realize how "by magic and trickery" this all looks?

And - it doesn't necessarily work, it doesn't ever have to work. You assume that the rows are loaded one by one sequentially - they don't have to be - we might buffer up enough to warrant an array insert into T when BBB is true, then do a bunch of CCC, then ......

do not do this, if it works by accident in test, it might not in real life. It'll be non-maintainable (and since it already doesn't work - you haven't lost anything)

please do a bit of code and process this input file - load up bind arrays and insert them in bulk.


keep it simple. You can certainly use plsql to do it.

A reader, April 22, 2009 - 10:59 am UTC

Let me try to explain the scenario 
I am converting a pre-existing process(run against a different database) to run against Oracle.The loading process needs to be changed to use Sqlloader utility instead and is called within a shell script. The shell script is doing other stuff alongwith loading.Primarily, the things of interest to sqlldr are

The shell script 
 1. generates the tablename at runtime 
 2. generates the column list at runtime by extracting names from input file
 3. generates the data file in required format(tab-delimited) from input files.

In this scenario, where everything is generated at runtime, I have to generate the control file at runtime too by passing required parameters. So I developed a stored procedure that creates the control file and parameter file at runtime.This procedure is called before sqlloader call and then newly created parameter file is used in sqlloader command. The required input parameters passed to procedure from shell are table name,list of columns, output location.

Sample of generated control is as follows 
(Note tablename "tbl" and column list "col1,col2,col3,col4,col5" are input parameters; Input Data file name is mentioned in parameter file so not needed here)

load data 
insert into table tbl
fields terminated by '\t'
optionally enclosed by '"' 
trailing nullcols 
(col1,col2,col3,col4,col5
)

This control file is working fine for data that doesnot have a all-null column but it needs some additonal section/option to handle all-null columns in data.
As for the data file, it is tab delimited file (Note: I already tried using X'09' instead of "\t" in "terminated by" clause, it isn't helping). 

Sample of data file (Note that column3 is all-null column)
col1 col2 col3 col4 col5
REC1 REC1  M value1
REC2 REC2  M value2
REC3 REC3  M value3
REC4 REC4  M value4 

With this type of data(column3 is all-null) in input file, columns 4 and 5 get shifted and loaded into column3 and column 4 respectively. Removing "optionally enclosed by '"' " in control file helps but as a undesirable side effect, it loads data with quotes (") and that is not acceptable.
Changing the generation of data file might help, but that requires changing the existing process of generating files(and dependencies), so working on lines of handling this in SQL loader instead (for now). Any help is appreciated.
Thanks in advance.


Tom Kyte
April 24, 2009 - 3:46 pm UTC

when you have terminated by a white space AND optionally enclosed by - the behaviour of sqlldr is to EAT WHITESPACE - and that cannot be changed. NULLIF aside (it had nothing to do with anything) - this is the way sqlldr works - if you use whitespace (tab, space) terminated by AND optionally enclosed by - then a series of tabs will be considered as one field.


You will not get sqlldr to consider \t\t to be a pair of fields if you use optionally enclosed by.


can you remove the optionally enclosed by and use trim( '"' from :col ) as the sql function - would that work for you?


Reading the Values across the Segments in SQL Loader control file..

Rajeshwaran, Jeyabal, April 24, 2009 - 3:53 pm UTC

Tom,
I am not clear with this
" please do a bit of code and process this input file - load up bind arrays and insert them in bulk "

what does this load up bind arrays?
how to proces them bulk in SQL Loader? can you please explain with a Short Sql / Plsql code to demostrate this?


Tom Kyte
April 27, 2009 - 12:33 pm UTC

I said to write a bit of code, do not use sqlldr, you need to procedurally PROCESS this data.

A reader, April 26, 2009 - 8:58 pm UTC

Hi Tom,

I am running this script sqlldr_exp.sql to extract a table. I am running this on a 10g database on linux OS.But i get the below error.
LOAD DATA
INFILE *
INTO TABLE IM_Headlines
REPLACE
FIELDS TERMINATED BY '|'
(
)
BEGINDATA
from    IM_Headlines
*
ERROR at line 2:
ORA-00936: missing expressio

I couldn't run it from command prompt using UNIX script. So I ran the sql script by logging into sqlplus. I ran it as:
SQL>@sqlldr_exp.sql <table_name>

Can you please help me where I am going wrong?


Thanks,
Ramya









Tom Kyte
April 27, 2009 - 1:57 pm UTC

http://asktom.oracle.com/tkyte/flat/index.html


grab that - it has the command line one I use on linux all of the time.

Reading the Values across the Segments in SQL Loader control file

Rajeshwaran, Jeyabal, April 27, 2009 - 9:26 am UTC

Tom,

Please ignore my previous one,I have used the CONTINUEIF Option to logically group my 'CCC' Data segments and did my control file like the below.
LOAD DATA
INFILE *     
TRUNCATE
CONTINUEIF THIS PRESERVE (1:3) ='CCC' 
INTO TABLE T 
WHEN (1:3) = 'CCC' TRAILING NULLCOLS
(
 segment_identifier POSITION(1:3),
 Contract_nbr       FILLER ,    
 Segment_Value      POSITION(1:1024)
)
INTO TABLE T 
WHEN (1:3) != 'CCC' TRAILING NULLCOLS
(
 segment_identifier POSITION(1:3),
 Contract_nbr       POSITION(1) " CASE :segment_identifier WHEN 'BBB' THEN SUBSTR(:Contract_nbr,11,5) ELSE NULL END ",    
 Segment_Value      POSITION(1:1024)
)
BEGINDATA
AAASH9561000000152320090421TEST  
BBB0000001R5863           
CCC0000001   10755151     
CCC0000002   10755155     
CCC0000003   10755166     
CCC0000004   10755168     
YYY0000001R58630000004    
ZZZSH956100000015230000001


Sample Output
=============
SEGMENT_IDENTIFIER CONTRACT_NBR SEGMENT_VALUE
------------------ ------------ ------------- 
CCC      NULL CCC0000001   10755151     CCC0000002   10755155     CCC0000003   10755166     CCC0000004   10755168     YYY0000001R58630000004
AAA      NULL AAASH9561000000152320090421TEST
BBB      NULL BBB0000001R5863
ZZZ      NULL ZZZSH956100000015230000001

Questions
1) Can you please help me how to substitute the value position(11:15) value " R5863 " from the BBB Segment to the 'CCC' Segment
2) Also I want my 'YYY' segment to be a new record into the Table. How can this be achived Tom (Currently it is combined with 'CCC' segment)?

Can you please help how to achieve the above requirment with a Sample Code?

Thanks,
Rajesh.
Tom Kyte
April 27, 2009 - 2:24 pm UTC

I do not believe you want to use sqlldr. If you have a varying number of 'segments', you'll have big time trouble. And that single record - would be inserted once - but you want two rows.

Reading the Values across the Segments in SQL Loader control file

Rajeshwaran, Jeyabal, April 28, 2009 - 1:13 am UTC

Tom,
Its okay, let the YYY Segment be clubed with CCC Segment.
Can you show me how the value of the Position (11:15) in the BBB Segment can be taken and Substitued in the " CONTRACT_NBR " Column of the CCC Segment.

Perviously i have learnt from your site how to get the particular position of the value for the delimited string using DELIMITED Package provided by you.
Can you show me how it is possible to get the values across the Delimited Segments ? is that possible Tom? if yes can you please show me how?

Thanks,
Rajesh.

A reader, April 30, 2009 - 2:31 pm UTC

Thanks for your suggestion of using trim but following this approach needs me to change the dynamic string generated in shell and adding this clause to each field of dynamic list;my column list gets really huge so instead I replaced nulls with dashes in input( after some dependency analysis) and updating back to null after loading.

Touched on 0D and 0A

Alex K..., May 18, 2009 - 4:22 pm UTC

You touched on this past the main comment/solutions.

like carriage return = 0D (decimal 13), line feed = 0A (decimal 10)

Our data is comma delimited, which is similar. Please feel free to move this, if it is in another post.

Didn't see an answer... We are attempting to extract for the first time a file from the web using sql loader and getting back a generic response of "field is too long". In trying to eliminate the possiblities. We created a file/row in DB Surfer and then did a save as to create out debug .csv file. The problem fields in question have to do with the date (is what we seem to be looking at) and possibly the CR and LF.

Here is our sample in that we are trying to use;
Load data
infile '${SQL_DAT_DIR}/${Unix_ABC_DAT}'
badfile '${SQL_BAD_DIR}/${Unix_ABC_BAD}'
discardfile '${SQL_DSC_DIR}/${Unix_ABC_DSC}'
into table A_B_C
append
(
web_id_index       integer external, 
transaction_id     char,
record_type        char,
FIRST_NAME         char nullif               FIRST_NAME=blanks,
MIDLE_INIT         char nullif                 MID_INIT=blanks,
LAST_NAME          char nullif                LAST_NAME=blanks,
DATE_OF_BIRTH      date 'YYYYMMDD' nullif DATE_OF_BIRTH=blanks,
...
OVERRIDE_DATE      date 'YYYYMMDD' nullif OVERRIDE_DATE=blanks,
OVERRIDE_USERID    char nullif         OVERRIDE_USERID=blanks,
LAST_UPD_USERID    char,
LAST_UPD_DATE      date 'YYYYMMDD'
)


Having read about not needing nullif... I am sure we can remove that. But would that be part of the problem?

Do we need to address the 0D and 0A to get past the error?

I hope we are only seeing 2 or 3e problems here.

Thank you in advance...
Tom Kyte
May 23, 2009 - 11:23 am UTC

without seeing your data, I'm not sure at all how I can comment.

do you have an end of line marker on all rows - if not, you need one. And the end of line marker is OS dependent, that is all

since you seem to be on Unix and are running sqlldr on unix, that would just be 0A

Re: Touched on 0D and 0A ...follow up

Alex K., May 21, 2009 - 1:57 pm UTC

We resolved the issue. It had to do with where the data file was being pulled from.

And the 0D and 0A were needed as it provides record separation.

One other thing we found was SQL loader seems to have a restriction of 255 characters per field. We did have fields defined as 500.

Thank you anyway
Tom Kyte
May 23, 2009 - 1:19 pm UTC

a default size of char(255) is what sqlldr has. A DEFAULT, not a limit.


a char(500),
b char(500),
c char(1000000)

would be a valid control file too - with the defaults overridden.

A reader, May 14, 2010 - 4:16 am UTC

i have this ascii delimiter § in the file. tried to use the syntax similar to what you have specified for tab. not working for me. please advise.

OPTIONS (SKIP=1)
LOAD DATA
INFILE 'Bal.dat'
APPEND INTO TABLE BAL_TRIAL
FIELDS TERMINATED BY x'A7'
(
TITLE CHAR,
NAME CHAR,
)

CONCATENATE in SQL* Loader

Rajeshwaran, Jeyabal, June 16, 2010 - 10:23 am UTC

Tom:

Ours is a large application and we use SQL Loader to import the data ( fixed format ) into our DB.. Atleast there will be 500,000 records per file...

my question is :

I've a record like this :

HDR0000400005
ABC8001
DEF8002
GHI8003
ABC8004
DEF8005
GHI8006
....
....
..
TRL0009990008787

I need to elminate the Header record that starts with 'HDR'
and concatenate the immediate 3 records for each iteration. that is ABC,DEF,GHI needs to be appended and inserted and then the next set

I need the output like this
ABC8001DEF8002GHI8003
ABC8004DEF8005GHI8006

Just referred Product manuals http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#sthref785
and did a simple control file like below, but i am not able to achieve that, Can you please help/guide me?

CREATE TABLE T
(
  x number ,
  y Varchar2(4000),
  z date
);  


LOAD DATA
INFILE *
TRUNCATE 
CONCATENATE 3
INTO TABLE T 
(
 x RECNUM,
 y position(1:4000),
 z " sysdate "
)

BEGINDATA
HDR0000400005
ABC8001
DEF8002
GHI8003
ABC8004
DEF8005
GHI8006
TRL0009990008787


D:\>sqlldr userid=rajesh/test control=d:\ctl.txt skip=1

rajesh@10gR2> select y from t;

 Y                                        
---------------------------------------- 
 GHI8003ABC8004DEF8005                    
 GHI8006TRL0009990008787   


Tom Kyte
June 22, 2010 - 11:39 am UTC

the concatenate is applied before the skip - what platform are you on?

CONCATENATE in SQL* Loader

Rajeshwaran, Jeyabal, June 23, 2010 - 12:43 am UTC

scott@10gR2> 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

scott@10gR2> select * from product_component_version;

PRODUCT                        VERSION                        STATUS
------------------------------ ------------------------------ ----------------
NLSRTL                         10.2.0.1.0                     Production
Oracle Database 10g Enterprise 10.2.0.1.0                     Prod
 Edition

PL/SQL                         10.2.0.1.0                     Production
TNS for 32-bit Windows:        10.2.0.1.0                     Production

Tom Kyte
June 24, 2010 - 6:32 am UTC

windows, then I have no solution for loading (with unix this would be easy, we could put the file minus the first and last record into a named pipe and load it.

On windows, which I do not work on, it would not be so nice.


Would you be willing to use an external table, I can do that in a platform independent fashion.

Tim Klement, August 20, 2010 - 9:44 am UTC

Hi Tom,

i want to load a datafile in my table with sql-loader. The data file looks like this (it´s a csv-file):

12345 | 04948 |hugo | hunter
12585 |048 | tim | ruhland

So there are a lot of newline characters and tabs in the fields and i would like to remove them.

My controlfile looks like this:

OPTIONS (SKIP=2)
LOAD DATA
INFILE 'export.csv'
INTO TABLE export
fields terminated by '|'
trailing nullcols
(kd_number integer external "TRIM(:kd_number)",
adr_number integer external "TRIM(:adr_number)",
name,
lastname,)

The error is ORA-01722 invalid number.

What is missing?

Thanks,

Regards,
Tim Klement

Tom Kyte
August 25, 2010 - 12:31 pm UTC

I didn't see any newline characters in your example at all????


but don't use integer external, just use char as the type - you have strings in your input file, not "integers"


You need no trimming whatsoever, numbers do not have leading and trailing blanks - therefore, upon loading - those strings with leading/trailing blanks (which will be converted to numbers) will "lose" those naturally. All you need do is this:

ops$tkyte%ORA10GR2> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 KD_NUMBER                                         NUMBER
 ADR_NUMBER                                        NUMBER
 NAME                                              VARCHAR2(30)
 LASTNAME                                          VARCHAR2(30)

ops$tkyte%ORA10GR2> !sqlldr / t

SQL*Loader: Release 10.2.0.4.0 - Production on Wed Aug 25 13:29:51 2010

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

Commit point reached - logical record count 2

ops$tkyte%ORA10GR2> !cat t.ctl
LOAD DATA
INFILE *
INTO TABLE t
REPLACE
FIELDS TERMINATED BY '|'
(
kd_number
,adr_number
,name
,lastname
)
BEGINDATA
12345 | 04948   |hugo   |  hunter
12585 |048  | tim  | ruhland

ops$tkyte%ORA10GR2> select * from t;

ops$tkyte%ORA10GR2> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 KD_NUMBER                                         NUMBER
 ADR_NUMBER                                        NUMBER
 NAME                                              VARCHAR2(30)
 LASTNAME                                          VARCHAR2(30)

ops$tkyte%ORA10GR2> !sqlldr / t

SQL*Loader: Release 10.2.0.4.0 - Production on Wed Aug 25 13:29:51 2010

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

Commit point reached - logical record count 2

ops$tkyte%ORA10GR2> !cat t.ctl
LOAD DATA
INFILE *
INTO TABLE t
REPLACE
FIELDS TERMINATED BY '|'
(
kd_number
,adr_number
,name
,lastname
)
BEGINDATA
12345 | 04948   |hugo   |  hunter
12585 |048  | tim  | ruhland

ops$tkyte%ORA10GR2> select * from t;

 KD_NUMBER ADR_NUMBER NAME                           LASTNAME
---------- ---------- ------------------------------ ------------------------------
     12345       4948 hugo                             hunter
     12585         48  tim                            ruhland






NULLIF

A reader, March 15, 2011 - 5:02 am UTC

Hi Tom,

I have a table which I load through SQL Loader. I have to implement a NULLIF in the SQL Loader, but it is not working.

OPTIONS(SKIP = 1)
LOAD DATA

INFILE 'demo02.dat'
TRUNCATE
INTO TABLE emp

(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)  INTEGER EXTERNAL,

 comm POSITION(41:48)  DECIMAL EXTERNAL,

 deptno POSITION(50:51)  INTEGER EXTERNAL,
 HIREDATE POSITION(52:61) DATE "yyyy-mm-dd" NULLIF (HIREDATE = '2003-03-03'), --"decode(:HIREDATE,NULL,'2011-03-05',:HIREDATE)",
 projno POSITION(62:63) INTEGER EXTERNAL)


Data is:
HEADER 123
7781 CLARK      MANAGER   7838 2572550           101995-12-1212
7839XKING       PRESIDENT      5500600           101995-12-1242
7934 MILLER     CLERK     7782  920600           101993-11-0142
7566 JONES      MANAGER   7839 3123375           201998-12-1842
7499 ALLEN      SALESMAN  7698 1600300   300.01  301995-12-1242
7654 MARTIN     SALESMAN  7698 1312050  1400.00  30          42
7658 CHAN       ANALYST   7566 3450400           201990-12-1242


Table:
CREATE TABLE EMP
(
EMPNO NUMBER,
ENAME VARCHAR2(10),
JOB VARCHAR2(10),
MGR NUMBER,
HIREDATE DATE,
SAL NUMBER,
COMM NUMBER,
DEPTNO NUMBER(2),
PROJNO NUMBER(4))

I can implement NULLIF in two other ways:

1. Define a DEFAULT for the table column
2. Use the DECODE function(see I have commented the part)

But I want to use it through NULLIF.. Is it possible.
Tom Kyte
March 15, 2011 - 8:38 am UTC

You have no data that matches your nullif condition???? how can you tell if it doesn't work??????

Data

Abhisek, March 15, 2011 - 8:41 am UTC

Hi Tom,

Isn't the floowing data applicable for NULLIF condition, I thought I kept HIRE_DATE for this row blank:

7654 MARTIN SALESMAN 7698 1312050 1400.00 30 42

In case it is not, my apologies to you. Could you then make a sample row and explain.
Tom Kyte
March 15, 2011 - 8:59 am UTC

you said:

HIREDATE POSITION(52:61) DATE "yyyy-mm-dd" NULLIF (HIREDATE = '2003-03-03'),

hiredate isn't equal to 2003-03-03 ever.

Nullif is used to set a field to NULL when the condition is satisfied.
http://docs.oracle.com/docs/cd/E11882_01/server.112/e16536/ldr_field_list.htm#SUTIL1202


Your decode, which I just know looked at, is setting hiredate to a default value if it is NULL. NULLIF sets things to NULL when they match a given value - quite the opposite of your goal.

decode or nvl are the right/easiest approach for this.

Thanks for the explanation

Abhisek, March 15, 2011 - 9:06 am UTC


please tom i need your help

fahad, March 12, 2012 - 3:06 am UTC

please i need to put condition in sqlloader to compare between
csv and the table in sql develober
i need to upload date from csv and when i insert on row from sql developer mark that (inactive)

some rows some time has extra field

thunderstorm151, January 20, 2015 - 2:20 pm UTC

Some time while loading data from txt file some rows have an extra field that is visible as shift in fields under wrong column.

Works Like a charm

Rajesh Varma, June 06, 2018 - 1:35 pm UTC

Thank you. I was facing exactly the same problem. The solution provided worked like a charm!