Need help regarding CONVERT function in Oracle 9i. Basic Task is to pick up data from database tables throuh cursor and then write it to file using UTL_FILE. But before writing the data, it needs to be transferred to EBCDIC format. So that the file written is not of normal ascii text data but the EBCDIC data format.
I have tried using CONVERT Function. But probably it does not give the correct translations on SQL PLUS Screen for accented characters like A, I, E etc. For all the accented variations it shows onlyplain simple character A or E or I ( and not A with 2 dots on head, A with one tilde on head, a with 1 dot on head and stuff like that)
I have found one chart from where I can crosscheck that the values being displayed are the correct replacement in the new/destination char set for the given char in the convert function. Sending that chart as well herewith.
select convert('Ö', 'US7ASCII','WE8EBCDIC500' ) from dual;
select convert('O','WE8EBCDIC500','US7ASCII' ) from dual;
Ö - Ascii
O - EBCDIC version of ascii Ö
select convert('å', 'US7ASCII','WE8EBCDIC500' ) from dual;
select convert('V','WE8EBCDIC500','US7ASCII' ) from dual;
å - Ascii
V - EBCDIC version of ascii å
select convert('V', 'US7ASCII','WE8EBCDIC500' ) from dual;
select convert('î','WE8EBCDIC500','US7ASCII' ) from dual;
The correct version in EBCDIC is ='î'
But oracle does snot have replace char for =î hence displays normal i for the above char.
I have been using UTL_FILE for quite a while now. And there seems to be no problem for writing to file. The problem is in the CONVERT function. Probably the problem is not in even CONVERT function because if i just use it in nested manner and exchange places of dest char set with source char set in inner functin, it surely gives me the orignal value that i had passed it..meaning lets take char 'I' (upper case i) - then first time using convert, i shd get only the converted value of I to ebcdic - that is something like 'n' with a tilde on the head.(If someone wants to see the chart the site is -> http://www.simotime.com/asc2ebc1.htm) But if i use once again convert in here, it does give me back 'I' that was the orignal character i passed to the function. I am not able to understand whether the problem is with SQL PLUS screen that it is not able to display the char vlaue in the dest char set or whether it is the char set itself that does not have any equivalent replace ment char for the char in source charset. Or is it soemthing to do with the settings on my pc - that is using SQL PLUS. I think internally CONVERT is probably able to transform the chars correctly. But not able to DISPLAY on the screen. I have added the following 3 parameters in Registry of my PC
SQLPLUS_FONT = Lucida Console
SQLPLUS_FONT_CHARSET = HANGEUL
SQLPLUS_FONT_SIZE = 14
Also the value of codepage in my pc's registry for the following key is = 1252
Pls suggest me how to proceed further for this problem.My Database Params are ->
20 rows selected.
Thank you very much in advance for the help.