Problem reading US7ASCII character set from an Oracle RDB7

sohasaid · Post by **sohasaid** » Fri Dec 18, 2009 10:03 am

Hi,

This is urgent!

Givens:
- Oracle RDB 7 database, holds US7ASCII character set (Contains Arabic data)
- We need to migrate this data to Oracle 10G database using datastage ETL.
- Oracle SQLPlus from the client on Windows reads this data as rubbish one, and datastage do the same thing each time i try to read the Arabic fields it rends rubbish data, but of course it can read the English one correctly.

Facts:
Although i knew that DataSatge don't require NLS conversion to read 7bit data, but it still could not read it...

I have tried lots of nls conversions with no use!.
The only case that we can read Arabic correctly from this database, is that when we use a PLSQL application created previously to convert this 7bit (US7ASCII) characters to 8bit (US8ASCII) and view it from an oracle form application.

So we have stuck in our migration project, as we can not read arabic correctly from the DataStage ETL, and of course we can not load it that way in the destination database.

Q1: is there a way that we could plug in this PLSQL application to the datastage server, in order to do the same thing and interpret those arabic characters and read them correctly into our ETL and then load it right?

Q2: is there a way that we could read the US7ASCII characters directly from datastage using any type of NLS customization or handling?

Q3: could this problem be related to the nls_lang setting on the Oracle Client installed on the Datastage server itslef?

Your help would be very highly appreciated.
Thank you

ray.wurlod · Post by **ray.wurlod** » Fri Dec 18, 2009 3:04 pm

US7ASCII character set can not contain Arabic characters. You are seeing this when using sqlplus.

You need to specify a DataStage character map (and Oracle NLS_LANG setting) that does handle Arabic characters. Until you have done that, your other questions are largely moot.

From the point of view of the Oracle database server, DataStage is just another client. That's why the Oracle client must be correctly configured on the DataStage server machine.

sohasaid · Post by **sohasaid** » Sat Dec 19, 2009 5:47 am

Thank you for your reply

the issue is that i have actually tried setting the NLS_LANG on the windows client to read Arabic, as i believe it's a client setting issue as you said.

My plan was to try reading Arabic correctly from the windows client, and which then in turn should prove that the datastage client needs also a NLS_LANG correct setting as well.

but, till now i failed to read arabic from my sqlplus windows client!

chulett · Post by **chulett** » Sat Dec 19, 2009 8:26 am

I would work with my DBA, if I were you, ask their help in getting things setup properly for sqlplus.

Your biggest issue, IMHO, is the fact that you think you have Arabic data in your US7ASCII-based Oracle database. As noted, it cannot.