File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes SQL Loader problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "SQL Loader problem" Watch "SQL Loader problem" New topic
Author

SQL Loader problem

shashanka jena
Greenhorn

Joined: Aug 23, 2006
Posts: 10
Hi,
I am stuck up with the below mentioned error since from copule of days any help in this regard is really great.
I am trying to load data to oracle database using Oracle sql loader.
I have to load data to two different tables using one control file, for first table all the datas are null and in the control file itself I am providing these data and primary key for this first table is loaded by calling a sequence. For this no problem, real problem starts when I am trying to load data to the second table by giving a csv file.
For second table for the first column itself it is not loading data from csv file because it is giving error as data size is more then max size in table. I have double checked the data size is less then the declared size also I am trimming the data in the control file.

More over when I am trying to load data for only second table using a control file by giving the same csv file it is working fine and successfully loading the data to the respective table.
Sample code of my control file :
OPTIONS(SKIP=1)
LOAD DATA
APPEND
INTO TABLE BATCH
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(
BATCH_ID "BATCH_ID_SEQ.nextval",
BATCH_DATE "null",
BATCH_TYPE ,
------
------
-------
)
INTO TABLE REPO
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(
REPO_ID "LI_MARGIN.CITI_REPO_ID_SEQ.nextval",
BATCH_ID "LI_MARGIN.MARGIN_BATCH_ID_SEQ.currval"
CLIENT "TRIM(:CLIENT_MNEMONIC)",
ACCOUNT_NO "TO_NUMBER(:ACCOUNT_NO)",
TRANS_TYPE "TRIM(:TRANS_TYPE)",
------
------
-------

)

For table repo I am providing the csv file from cmd
My SQL Loader command : CONTROL=C:\Repo.ctl DATA=C:\repo.csv log=C:\log.txt userid=***/*****

Erro I am getting : For Repo table for CLIENT column it saying the actual value is more then the max size available.
Note : For CLIENT field the size in table is 8char where as data in csv is 7char , also I am trimming.

Thanks in Advance,
Shashanka



Thanks and Regards<br />shashanka Jena
Agador Paloi
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
I would check your data , you probably have one sneaking in longer than you think.
or do something like this in your control statement:

CLIENT substr( trim(:CLIENT), 0, 8)


shashanka jena
Greenhorn

Joined: Aug 23, 2006
Posts: 10
Thanks Agador,
After following your suggestion my ealier error has gone but now another error is coming in different column and the error is as below :

Record 1: Rejected - Error on table REPO, column MATURITY_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected

I double checked the data type of maturity_date which is a date and also I checked in my csv file all data for maturity_date is date type.

Could you please suggest me.

Thanks,
Shashanka
Agador Paloi
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
you or your users need to decide what you do with an invalid date field
Vamsi Mohan
Ranch Hand

Joined: Jun 20, 2009
Posts: 80
May be blan characters are included in your data. Please double check the length, by using string length function. You will get the exact size of the string or data. This is definetely because of the blank characters.

There is one more possible, if you are trying to insert international languages data(other than english).


VAMSI MOHAN.V
(SCJP5.0, IBM Rational Solution Designer, IBM Rational Architect, IBM RUP Designer)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Loader problem
 
Similar Threads
SQLLoader NULLS in Tab Delimited file
NOT null constraint in SQL loader URGENT
parallel insertion in oracle
Load the data in Oracle Database
Can you use JPA to import a csv file?