Unwanted Trailing spaces

wasimus · Post by **wasimus** » Thu Jun 17, 2010 9:59 am

Hi,

I am getting a wierd problem here. In one of our job (which ran successfully on DEV and QA) all of a sudden Datastage started inserting extra spaces in one of the columns. The part of SQL which is brining back extra spaces ias follows :

, CASE When (o2.ORG_DATE_CLOSED IS NULL or o2.ORG_DATE_CLOSED > SYSDATE
and not exists (select 0 from orgcloee where orgcloee.org_lvl_child = orgmstee.org_lvl_child
and sysdate between orgcloee.org_closed_fr_date and orgcloee.org_closed_to_date)) THEN 'A'
Else 'I'
END as U_STOREACTIVESTATUS

The column Length is defined as Varchar(1) but still getting extra spaces at the end. The DB is Oracle 10g.

I dont want to change the code now by putting in trim in Prod as there are so many jobs.

Thanks

basiltarun · Post by **basiltarun** » Thu Jun 17, 2010 10:03 am

I guess this has been discussed before.

chulett · Post by **chulett** » Thu Jun 17, 2010 10:05 am

Not sure how you get extra anything in a one character field, can you be more specific as to exactly what it is you are seeing?

Is the version of your Oracle client different in production?

wasimus · Post by **wasimus** » Thu Jun 17, 2010 10:13 am

chulett wrote:Not sure how you get extra anything in a one character field, can you be more specific as to exactly what it is you are seeing?

Is the version of your Oracle client different in production?

This is what I am seeing. I cut and paste the actual value from DS View into notepad, applied single quotes for your view, and putting it here :

'A '

The Oracle client is same as DEV and QA in Production and this problem is happening in prod. only which is a separate machine.

Thanks for your help

anbu · Post by **anbu** » Thu Jun 17, 2010 10:17 am

Do you see spaces in your target table? What is the length defined in this table?

wasimus · Post by **wasimus** » Thu Jun 17, 2010 10:22 am

anbu wrote:Do you see spaces in your target table? What is the length defined in this table?

I am getting warnings as below when inserting into Target table and job getting aborted after 50 warns.

[DataStage][SQL Client][ODBC][IBM(DataDirect OEM)][ODBC Oracle Wire Protocol driver][Oracle]ORA-12899: value too large for column "IGPMGR"."INTUPS_LOC"."U_STOREACTIVESTATUS"

chulett · Post by **chulett** » Thu Jun 17, 2010 10:51 am

The only time I've ever seen anything remotely like this was when a slightly different (aka 'buggy') Oracle client version was installed in one environment, hence the question.