Trouble with special characters - like super-scripts

lebos · Post by **lebos** » Wed Feb 16, 2005 3:14 pm

I have an Oracle database with NLS_LANG = American.America.WE8ISO8859P1 and the same value for NLS_LANG in our dsenv file. In this database there is a table with a VARCHAR2(10) column which contains superscripts as in cubic feet (ft3). (The 3 should be raised). The hex value for the 3 here is x'B3'. Copying this data to a different table in the same instance of the database using a DataStage server job (we did not specify the NLS option when doing the DataStage install) results in all superscripts (and other similar characters) being replaced by a '?'. This does not happen if the copy is accomplished via SQL*Plus using an Insert/Select statement. Results of both ways were observed using SQL selects as I know that View Data does not always show the right thing.

Thus, it is obvious that it is DataStage which is causing this. Others have said (in the DSXchange forum) that they can handle such things as European characters like umlauts (x'DC' for example) without using NLS in DataStage. How can we do this with superscripts?

Thanks for any help.

Larry

ray.wurlod · Post by **ray.wurlod** » Wed Feb 16, 2005 8:03 pm

0xB3 is Char(179) which is not a printable character in ASCII (which is what DataStage uses if NLS is not enabled). It will handle the character OK, but will have difficulty displaying it. It will almost certainly not be able to display it as superscript given that you're on a UNIX server; UNIX-based character sets tend not to know about superscript and subscript.
I suspect this will be true even if NLS is installed and enabled.

lebos · Post by **lebos** » Thu Feb 17, 2005 7:29 am

Ray,

I understand that DataStage will not be able to display this character properly, but it also does not seem to handle it correctly. That is, after the job runs, the data in the target table does not contain the superscript - it has been replaced by a '?'. This can be seen be doing an SQL query (I've done it in both Toad and SQL*Plus). Using the same query against both the source table and the target table (changing the from clause only) shows superscripts in the source and '?' in the target.

Larry

memrinal · Post by **memrinal** » Thu Feb 17, 2005 9:45 pm

Hi Larry,
All the unprintable characters are replaced by "?" only for display purpose. But actually theyare not equal to "?". Say the string you got is "27ft?" where the "?" is the replacment for superscript. Try equating it with "27ft?". The two strings wont match.
There is one more way to check this. Try Doing OConv(yourString,"MCP"). If the "?" in yourString is replaced by "." then that means that the special character is still there but since it is not in ASCII and hence cant be displayed, so it is being displayed as "?"
Mrinal

lebos · Post by **lebos** » Mon Feb 21, 2005 11:32 am

Mrinal,

That may be true most of the time, but in my case the superscripts are actually replaced with '?'. I can tell this by doing a query in either SQL*Plus or within Toad. Either of these display superscripts if they exist and "?" if they exist.

Larry

memrinal · Post by **memrinal** » Tue Feb 22, 2005 11:29 pm

Larry
Try once by taking the same data from Database into a sequential file and doing Oconv(string,"MCP"). From what i have observed "?" should be replaced by ".". Please Let me know what happens in either case.
Mrinal

lebos · Post by **lebos** » Wed Feb 23, 2005 12:09 pm

Mrinal,

I have done what you requested, but did not get what you expected. The "?" remained "?", they did not get converted to ".".

What that means is what I said before - it is not simply that they are only being displayed as "?", DataStage is actually converting the special characters to "?".

Any more suggestions anybody?

Larry

lebos · Post by **lebos** » Thu Mar 03, 2005 12:05 pm

OK, now is the time for owning up to being foolish!

This problem was due to a few people here not being able to see what they were seeing. NLS_LANG was not being set in the dsenv simiply because the export NLS_LANG statement was missing. Adding the export fixed the problem.

We have all scheduled eye exams ASAP. Scary thing is that this has been this way since Aug., 2002!!

Thanks all.

Larry

ray.wurlod · Post by **ray.wurlod** » Thu Mar 03, 2005 2:32 pm

This can be a trap, because dsenv runs in Bourne shell (check out line 1). Anyone used to automatically exported environment variables (like in some of the newer, "cleverer" shells) is likely to forget to include the export commands, even though there are already some there!