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