Oracle ASCII Character Set - .Net Extensions

Question

I am trying to extract data out an Oracle table. The column is defined as char(40) and contains extended ASCII values. Currently in SQL*Plus, the extended characters are displaying as an "upside-down" question mark. I need to be able to extract the ASCII characters that have a decimal value > 128 and display in the extended or 8-bit encoding schema.

-- Current Output

select myfield from Mytable;

b@¿pmxJ¿¿¿.¿¿¿P9¿¿¿¿¿+¿¿w)¿@Y@B¿¿¿

-- Decimal Values

Typ=96 Len=40:
98,64,189,112,109,120,74,156,27,231,167,46,224,28,130,183,80,57,202,232,150,239,144,43,169,1,186,119,41,254,64,89,64,12,19,8,66,194,136,210

-- Database Characterset

SQL> select * from NLS_DATABASE_PARAMETERS;

PARAMETER VALUE

------------------------------ ----------------------------------------

NLS_CSMIG_SCHEMA_VERSION 5

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET US7ASCII

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE AMERICAN

NLS_SORT BINARY

PARAMETER VALUE

------------------------------ ----------------------------------------

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY $

NLS_COMP BINARY

NLS_LENGTH_SEMANTICS BYTE

NLS_NCHAR_CONV_EXCP FALSE

NLS_NCHAR_CHARACTERSET UTF8

NLS_RDBMS_VERSION 10.2.0.3.0

I do not have a lot of experience with Oracle. Any help would be greatly appreciated!

Thanks, Brennan

---

I was able to extract the data in decimal format and process it in the .Net function without issue. The SQL statement is a bit messing. I am trying to write a function to assist in the query. I am having problems creating the function.

current SQL statement:

select DECRYPTDATA(
    ASCII(SUBSTR(ACTIONCD,1,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,2,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,3,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,4,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,5,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,6,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,7,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,8,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,9,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,10,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,11,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,12,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,13,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,14,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,15,1)) || ',' ||
    ASCII(SUBSTR(ACTIONCD,16,1)), '1'  )
        from ORDERACTIONS where KEYNUM = 1

I would like to make a quick function to return this string.

Below is the function that will not compile. Any suggestions?

create or replace function ASCIITODEC(p_actioncd VARCHAR2)
return VARCHAR2 is
begin

    return ASCII(SUBSTR(p_actioncd,1,1))  || ',' ||
           ASCII(SUBSTR(p_actioncd,2,1))  || ',' ||
           ASCII(SUBSTR(p_actioncd,3,1))  || ',' ||
           ASCII(SUBSTR(p_actioncd,4,1))  || ',' ||
           ASCII(SUBSTR(p_actioncd,5,1))  || ',' ||
           ASCII(SUBSTR(p_actioncd,6,1))  || ',' ||
           ASCII(SUBSTR(p_actioncd,7,1))  || ',' ||
           ASCII(SUBSTR(p_actioncd,8,1))  || ',' ||
           ASCII(SUBSTR(p_actioncd,9,1))  || ',' ||
           ASCII(SUBSTR(p_actioncd,10,1)) || ',' ||
           ASCII(SUBSTR(p_actioncd,11,1)) || ',' ||
           ASCII(SUBSTR(p_actioncd,12,1)) || ',' ||
           ASCII(SUBSTR(p_actioncd,13,1)) || ',' ||
           ASCII(SUBSTR(p_actioncd,14,1)) || ',' ||
           ASCII(SUBSTR(p_actioncd,15,1)) || ',' ||
           ASCII(SUBSTR(p_actioncd,16,1)) )

end ASCIITODEC;
/

Thanks, Brennan

Answer 1

your NLS_CHARACTERSET is defined as:

NLS_CHARACTERSET US7ASCII 

Therefore, oracle will only guarantee that standard ASCII characters can be stored You have to either

• change your database character set to something which allows extended characters, e.g.: UTF-8

OR

• change the field from a CHAR to a NCHAR. NCHAR columns will pick up NLS_NCHAR CHARACTERSET, which i can see is set to UTF-8 .

Also, are you sure you want to use a CHAR instead of a VARCHAR? CHAR fields are padded to ALWAYS be 40 characters long. NVARCHAR are fields UP TO 40 characters long.

All that being said, if you did manage to stuff non-ascii characters into a US7ASCII fields, it is sometimes possible to convert the database charset into something like WE8ISO8859P1. See

http://download.oracle.com/docs/cd/B10501_01/server.920/a96529/ch2.htm#101203

Answer 2

You've got a problem. The database character set (NLS_CHARACTERSET) is US7ASCII. That means that the database only supports 7-bit ASCII characters in CHAR columns. The database doesn't support using any of the characters above 128 on whatever Windows code page you're referring to (my guess is Windows-1252, but there are many possibilities).

How much data do you have in this database? Is the database being used by other applications? Do those applications appear to work correctly?

Ideally, you would be able to recreate the database with a proper character set (WE8MSWIN1252 or AL32UTF8 most likely), reload the data, and life would be good. If you have existing applications that appear to work, though, that would indicate that you have applications that depend on bypassing the character set conversion code in the Oracle client which opens a whole other source of pain.

Answer 3

There is nothing wrong except onc extra paranthesis at end.

Can try this funcion too.

create or replace function str_ascii(pin in char) return char is ret_val varchar2(100); ch char(1); begin begin for n in 1 .. length(pin) loop if ret_val is not null then ret_val := ret_val ||', '; end if; ch := substr(pin,n,1); ret_val := ret_val || ascii(ch); end loop; end; return ret_val; end;

Aziz