Very helpful.....was really great
October 20, 2001 - 5am Central time zone
Reviewer: Riaz Shahid from Islamabad,Pakistan
Thats gre8
August 13, 2002 - 7am Central time zone
Reviewer: laLocA from Austria
Thank you very much - I really missed this information about such a common problem in the official
reference.
Works great, less filling
December 18, 2002 - 5pm Central time zone
Reviewer: Dave Archbell from La Jolla, CA USA
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?
Followup December 18, 2002 - 6pm Central time zone:
they are just ascii codes?
like carriage return = 0D (decimal 13), line feed = 0A (decimal 10) and so on...
December 18, 2002 - 10pm Central time zone
Reviewer: Sagi from India
Hi Tom,
As usual excellent.
In your solution what is ??
X'9'
Thanx in advance.
Regards,
Sagi
Followup December 19, 2002 - 7am Central time zone:
tab
error
December 19, 2002 - 3am Central time zone
Reviewer: ashraf from kuwait
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
Followup December 19, 2002 - 7am Central time zone:
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
December 19, 2002 - 8am Central time zone
Reviewer: A reader
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
Followup December 19, 2002 - 8am Central time zone:
I cannot reproduce -- do you have exact steps? is it still happening?
Answer to Sagi from India
December 19, 2002 - 10am Central time zone
Reviewer: Leonid Birman from Atlanta, GA USA
The X'9' is Hexadecimal Format -> X
and Hex 9 is the ASCII code of the TAB character.
trim trailing blank space and other
March 26, 2003 - 7pm Central time zone
Reviewer: Vikas Sharma from Delhi India
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
Followup March 26, 2003 - 7pm Central time zone:
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
March 26, 2003 - 7pm Central time zone
Reviewer: Vikas Sharma from Delhi India
Hi Tom,
Thanks For the immediate reply this solves my problem.
Regards,
Vikas Sharma
About the numeric or value error in the search field.
March 27, 2003 - 4am Central time zone
Reviewer: Martin from UK
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
Followup March 27, 2003 - 7am Central time zone:
but it does catch it quite nicely doesn't it ;)
Mark several Rows in Oracle Forms
March 27, 2003 - 8am Central time zone
Reviewer: Mary
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!
Followup March 27, 2003 - 10am Central time zone:
that is a list box feature -- it is not a feature of a default block. Sorry.
What about Null characters
May 1, 2003 - 6am Central time zone
Reviewer: Chetan Kashikar from Auckland,New Zealand
Can I use X'0' for null delimitters?
Followup May 1, 2003 - 12pm Central time zone:
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
February 7, 2006 - 12pm Central time zone
Reviewer: Matt Davis from San Antonio, TX USA
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?
Followup February 8, 2006 - 1am Central time zone:
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?
December 21, 2006 - 6pm Central time zone
Reviewer: Geetha from Seattle, Washington.
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)
Followup December 22, 2006 - 6am Central time zone:
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...
December 22, 2006 - 3pm Central time zone
Reviewer: Geetha from Seattle, WA
Thanks a lot Tom. You are truly wonderful.
Happy holidays!!!
Charset
December 23, 2006 - 11am Central time zone
Reviewer: A reader from Earth
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!
Followup December 24, 2006 - 9am Central time zone:
could be - sure.
tab delimited input file with OPTIONALLY ENCLOSED clause
January 24, 2007 - 12pm Central time zone
Reviewer: kuldeep from India
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
January 27, 2007 - 2am Central time zone
Reviewer: kuleeep from india
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
December 1, 2008 - 12pm Central time zone
Reviewer: madhu from usa
I have the same question as above.
Kindly please answer the above quetsion for tab delimited data load.
tab delimited control file
December 1, 2008 - 5pm Central time zone
Reviewer: madhu from usa
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
April 21, 2009 - 4pm Central time zone
Reviewer: A reader
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
Followup April 21, 2009 - 5pm Central time zone:
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..
April 22, 2009 - 10am Central time zone
Reviewer: Rajeshwaran, Jeyabal
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.
Followup April 24, 2009 - 3pm Central time zone:
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.
April 22, 2009 - 10am Central time zone
Reviewer: A reader
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.
Followup April 24, 2009 - 3pm Central time zone:
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..
April 24, 2009 - 3pm Central time zone
Reviewer: Rajeshwaran, Jeyabal
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?
Followup April 27, 2009 - 12pm Central time zone:
I said to write a bit of code, do not use sqlldr, you need to procedurally PROCESS this data.
April 26, 2009 - 8pm Central time zone
Reviewer: A reader
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
April 27, 2009 - 9am Central time zone
Reviewer: Rajeshwaran, Jeyabal
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.
Followup April 27, 2009 - 2pm Central time zone:
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
April 28, 2009 - 1am Central time zone
Reviewer: Rajeshwaran, Jeyabal
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.
April 30, 2009 - 2pm Central time zone
Reviewer: A reader
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
May 18, 2009 - 4pm Central time zone
Reviewer: Alex K... from Topeka, KS
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...
Followup May 23, 2009 - 11am Central time zone:
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
May 21, 2009 - 1pm Central time zone
Reviewer: Alex K. from Topeka, KS
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
Followup May 23, 2009 - 1pm Central time zone:
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.
|