OraFAQ Forum: SQL & PL/SQL » Problem with CONVERT function to transform ASCII to EBCDIC

Home » SQL & PL/SQL » SQL & PL/SQL » Problem with CONVERT function to transform ASCII to EBCDIC

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with CONVERT function to transform ASCII to EBCDIC [message #176301]

Wed, 07 June 2006 14:10

BrendaSt
Messages: 8
Registered: June 2006
Location: Albany, NY

Junior Member

The Problem is trying to convert Oracle database column values (stored as NUMBER) that are ASCII to EBCDIC output.

I am hoping someone can point me in the right direction to figure this out...

I read about using the PL/SQL function called CONVERT to translate a source character set into a destination char set.

IE.

CONVERT(stringin, dest_set [,source_set])

I tried using:

CONVERT(total_check_amount, 'WE8EBCDIC500', 'WE8ISO8859P1')
and
CONVERT(total_check_amount, 'WE8EBCDIC500', 'US7ASCII')
and
CONVERT(total_check_amount, 'WE8EBCDIC500')

This is being used in a SQLPlus query:

select CONVERT(total_check_amount, 'WE8EBCDIC500','WE8ISO8859P1')
from xbc_dms_payment_header;

from a SQL Plus local session. Each attempt creates binary garbage. Is there something special needed to use this FUNCTION that was not explained in the how-to I found? WE8EBCDIC500 should be a normal EBCDIC characterset, and WE8ISO8859P1 is the characterset on our Oracle database (found on NLS_DATABASE_PARAMETERS table).

I also tried this from a SQL session at the server level. I have also been searching for an online reference Oracle Server SQL Reference, but have not found it yet.

Any help you can give is extremely appreciated.

Thanks,
Brenda Streed

Report message to a moderator

Re: Problem with CONVERT function to transform ASCII to EBCDIC [message #176312 is a reply to message #176301]

Wed, 07 June 2006 18:07

andrew again
Messages: 2577
Registered: March 2000

Senior Member

Remember that if you're on a PC, then it doesn't understand EBCDIC, so you need to look at the bytes to make sure the conversion occurs correctly - you can't rely on what you see on your screen. My database is UTF8, so I use that in my example. I included the � character in my code and it seems to translate to 175 in EBCDIC, but I don't see it on the chart (see URLs below). Maybe I need a better chart...

SQL> set linesize 100
SQL> select value
  2    from v$nls_parameters
  3    where parameter = 'NLS_CHARACTERSET';

VALUE
----------------------------------------------------------------
AL32UTF8

SQL> 
SQL> create table ebcdic_tst(col1 varchar2(10));

Table created.

SQL> insert into ebcdic_tst values ('ABC123�');

1 row created.

SQL> column dmp_utf8 format a40
SQL> column dmp_ebcdic format a40
SQL> select col1,
  2     dump(col1) dmp_utf8,
  3     dump(convert (col1, 'WE8EBCDIC500', 'AL32UTF8')) dmp_ebcdic
  4  from ebcdic_tst;

COL1       DMP_UTF8                                 DMP_EBCDIC
---------- ---------------------------------------- ----------------------------------------
ABC123�    Typ=1 Len=8: 65,66,67,49,50,51,194,174   Typ=1 Len=7: 193,194,195,241,242,243,175

-- EBCDIC chart http://www.legacyj.com/cobol/ebcdic.html
-- UTF8 http://www.fileformat.info/info/unicode/char/00ae/index.htm
--   (� takes 2 bytes in UTF8  194,174 = c2,ae)

Report message to a moderator

Re: Problem with CONVERT function to transform ASCII to EBCDIC [message #176501 is a reply to message #176312]

Thu, 08 June 2006 09:34

BrendaSt
Messages: 8
Registered: June 2006
Location: Albany, NY

Junior Member

Thank you for the answer...I was fairly sure that it had to do with being connected to a PC..I will try this out.

Thanks,
Brenda

Report message to a moderator

Previous Topic:	Nested Sum Query
Next Topic:	Missing left paraenthesis error?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 06 07:45:00 CDT 2025