sql loader issue

jatayl · Post by **jatayl** » Mon Mar 31, 2008 12:53 pm

Okay, I have two issues, and I think we've resolved one, but I wished to share to see if anyone else came across this.

First, Oracle client is 10g, Oracle Database is 9.2.0.5. This is a problem according to Oracle: "the client version must be the same or earlier than the server version, and both must be 9i or later for direct path load. Therefore,direct path load cannot be used to load data from 10g client into 9i database. In addition, a direct path load from 9.2 client into 9.0.1 database is not supported."

My error messages: Oracle_Enterprise_168,1: SQL*Loader-925: Error while uldlpim: OCIStmtExecute
Oracle_Enterprise_168,1: ORA-00904: "MESSAGE_NUM": invalid identifier

Appears there is an additional field.
That explains some, but.....

Next issue, my data actually loads into the table. hmmmm. Somehow Oracle is sending a bad return code back to DataStage or something.

Long story even longer.....Good data, bad return code, job aborts.

Suggestions? My dba said that the BladeLogic people had the same problem with return codes. I don't know who they are or what they do, but apparently, they have issues also.

Thanks,
Jason

chulett · Post by **chulett** » Mon Mar 31, 2008 1:31 pm

Not sure what kind of suggestion you are looking for, other than a confirmation that yes indeed you need to take a 'least common denominator' approach with client versus database software versions.

We have several instances where a 9i client talks successfully with a 10g database, but I would never consider using a higher client than your target database - with any application, not just DataStage. That's just asking for trouble. IMHO.

jatayl · Post by **jatayl** » Mon Mar 31, 2008 1:51 pm

Thanks chulett. I think we can probably just use the 9.2.0.5 libraries instead of 10g on the client side. That should help. I still don't understand why i'm getting data loaded to the table successfully.
It's all good.

Jason

chulett · Post by **chulett** » Mon Mar 31, 2008 1:55 pm

jatayl wrote:I think we can probably just use the 9.2.0.5 libraries instead of 10g on the client side.

That's exactly what we do.

jatayl · Post by **jatayl** » Mon Mar 31, 2008 2:10 pm

chulett wrote:
jatayl wrote:I think we can probably just use the 9.2.0.5 libraries instead of 10g on the client side.
That's exactly what we do. ...

Actually, I just found out that our DataStage Support and Oracle DBA's do not want both libraries on the server at the same time. I guess we just won't have the Load functionality until our Database is upgraded to 10g. Thanks again.

Jason

chulett · Post by **chulett** » Mon Mar 31, 2008 4:12 pm

Wow... I wonder why they would care? We have multiple clients installed on our DataStage server, heck most database servers here have multiple client versions. One is of ETL and the others are for all of the other knuckleheads on the box with Special Needs. [shrug]

Oh well.