Spaces getting added to the data from Oracle enterprise stag

[Minhajuddin]

Hi all,

I have a job in which I retrieve data using a sparse lookup... Everything is working fine but for the trailing spaces which get added to all the varchars.

e.g. If I retrieve a column emp_name varchar(10) with value 'John'
The oracle stage is retrieving it as 'John '

Is this expected? and if so, Is there an easy fix for this other than using a modify stage or a transformer.

Thanks for the help.

[JoshGeorge]

I don't think this is expected You might want to see this on the database side and make sure that data is stored in this way in the source/lookup itself or in the schema it is defined as char and not varchar.

[ArndW]

It would be expected if you defined the column as CHAR(5) insteado f VARCHAR(5).

[JoshGeorge]

That confirms this is not expected as you have defined the column as VARCHAR(10) and not CHAR(10). Confirm the same on Oracle side.

e.g. If I retrieve a column emp_name varchar(10) with value 'John'
The oracle stage is retrieving it as 'John '

Is this expected?

[chulett]

What exact version of the Oracle client are you using? I've seen things like this where installing a 'better' client version can resolve it.

[kwwilliams]

On your column metadata is the Unicode setting on? I have seen space issues when it is on depending on the source database settings.

[Minhajuddin]

Thanks for all the replies..

I don't have any spaces on the database side..
and the metadata is the same even in the database.
i.e. when I query the database from sqlplus I don't get spaces...
and the oracle client we are using is Oracle 9i. And lastly the unicode setting is not on.

[chulett]

I asked for the exact version - 9.2.0.4? 9.2.0.8? Makes a difference, some are buggier than others.

[Minhajuddin]

Hi Craig,

The exact version of the oracle client is 9.2.0.1.0

Did anybody have the same problem before?

[chulett]

That's pretty darn old. As noted, I've seen similar things. Ask your DBA to install a more recent release, 9.2.0.6 or 9.2.0.8 seem to be stable for us. No guarantees but it just may do the trick.