Oracle Load Problem

xinhuang66 · Post by **xinhuang66** » Tue Aug 14, 2007 8:26 pm

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

ArndW · Post by **ArndW** » Tue Aug 14, 2007 8:29 pm

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?

xinhuang66 · Post by **xinhuang66** » Tue Aug 14, 2007 9:02 pm

Yes, I tried varchar and longVarchar in datastage.

ArndW · Post by **ArndW** » Tue Aug 14, 2007 9:29 pm

Umm, I'm still not sure what you mean. What is your definition in Oracle for this column?

ArndW · Post by **ArndW** » Tue Aug 14, 2007 10:18 pm

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

xinhuang66 · Post by **xinhuang66** » Tue Aug 14, 2007 11:00 pm

oracle datatype varchar2(1000)

In datastage, I try varchar(1000), and longVarchar(1000), all failed

xinhuang66 · Post by **xinhuang66** » Tue Aug 14, 2007 11:26 pm

Why there is no length specified in DS generated control file ? and How can I make DS generated a length specified column ??

Thanks

chulett · Post by **chulett** » Wed Aug 15, 2007 6:26 am

I have no clue, was just explaining the behaviour you are seeing. As Arnd asked, is this an RCP issue?

xinhuang66 · Post by **xinhuang66** » Mon Aug 20, 2007 10:12 pm

RCP was disabled in our project ?

Does it cause problems ?

ArndW · Post by **ArndW** » Mon Aug 20, 2007 10:27 pm

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?

xinhuang66 · Post by **xinhuang66** » Mon Aug 20, 2007 11:22 pm

No, it doesn't have.

ArndW · Post by **ArndW** » Mon Aug 20, 2007 11:26 pm

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.

xinhuang66 · Post by **xinhuang66** » Wed Aug 22, 2007 9:36 pm

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

chulett · Post by **chulett** » Wed Aug 22, 2007 9:49 pm

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.

Yuan_Edward · Post by **Yuan_Edward** » Thu Aug 23, 2007 1:55 am

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