Page 1 of 2
Oracle Load Problem
Posted: Tue Aug 14, 2007 8:26 pm
by xinhuang66
I got a problem when I try to load data file to oracle by datastage.
If the column length is more than 300, I always get an oracle errors say column lengh exceed maxium length.
And if I change the loading method to upsert, it is OK, I can even insert 4000 length column into oracle.
But when I changed it back to Load method, it alwasy doesn't work.
Anyone can help ? thanks
Posted: Tue Aug 14, 2007 8:29 pm
by ArndW
What is the Oracle DDL column length? Is the problem that you cannot load more than 300 characters into a varchar column or something else?
Posted: Tue Aug 14, 2007 9:02 pm
by xinhuang66
Yes, I tried varchar and longVarchar in datastage.
Posted: Tue Aug 14, 2007 9:29 pm
by ArndW
Umm, I'm still not sure what you mean. What is your definition in Oracle for this column?
Posted: Tue Aug 14, 2007 10:18 pm
by ArndW
xinhuang66 wrote:varchar2(1000), is oracle column definition.
If that is the definition in your table, what is it in the job?
do you use the CIDN1 and CIDN2 columns somewhere in the job (i.e. do you have runtime column propagation issues)?
Posted: Tue Aug 14, 2007 11:00 pm
by xinhuang66
oracle datatype varchar2(1000)
In datastage, I try varchar(1000), and longVarchar(1000), all failed
Posted: Tue Aug 14, 2007 11:26 pm
by xinhuang66
Why there is no length specified in DS generated control file ? and How can I make DS generated a length specified column ??
Thanks
Posted: Wed Aug 15, 2007 6:26 am
by chulett
I have no clue, was just explaining the behaviour you are seeing. As Arnd asked, is this an RCP issue?
Posted: Mon Aug 20, 2007 10:12 pm
by xinhuang66
RCP was disabled in our project ?
Does it cause problems ?
Posted: Mon Aug 20, 2007 10:27 pm
by ArndW
RCP does not cause problems, just inappropriate use of it may cause them. If you shorten the length to 250 in your job, does the control file contain the explicit lengths?
Posted: Mon Aug 20, 2007 11:22 pm
by xinhuang66
No, it doesn't have.
Posted: Mon Aug 20, 2007 11:26 pm
by ArndW
Sounds like there might be a bug when DataStage creates the load file and doesn't inherit the explicit column definitions into this file. You should take it up with your support provider.
Posted: Wed Aug 22, 2007 9:36 pm
by xinhuang66
ArndW wrote:Sounds like there might be a bug when DataStage creates the load file and doesn't inherit the explicit column definitions into this file. You should take it up with your support provider. ...
thanks for your output.
I still couldn't make it work. I use upsert method at this moment. And I raised my questions to IBM guys. They did the test. And come back to me say that it works on their Oracle system.
I don't know whether I need to do some special configuration in Oracle. However, if I test a sqlldr control file (with column length) in my oracle, it works
Posted: Wed Aug 22, 2007 9:49 pm
by chulett
Then you need to work with them to ascertain what is different between your 'Oracle system' and theirs. First thing I'd wonder about would be the specific release of the Oracle client both are using.
Posted: Thu Aug 23, 2007 1:55 am
by Yuan_Edward
Did you set the environment variable APT_ORACLE_LOAD_DELIMITED in your job? Also are there any double quotes in the input data?
What error did you get in the sqlldr log? Can you post the sqlldr log file?
xinhuang66 wrote:ArndW wrote:Sounds like there might be a bug when DataStage creates the load file and doesn't inherit the explicit column definitions into this file. You should take it up with your support provider. ...
thanks for your output.
I still couldn't make it work. I use upsert method at this moment. And I raised my questions to IBM guys. They did the test. And come back to me say that it works on their Oracle system.
I don't know whether I need to do some special configuration in Oracle. However, if I test a sqlldr control file (with column length) in my oracle, it works