ORACLE Function to Get Ascii Character from Code

Posted on 2012-09-17
Last Modified: 2012-09-21
I need to create a function for SQL Server and Oracle that I can call to get an ASCII character from a character code.  My issue, and the reason I am creating the function, is that SQL Server and Oracle have functions with different names for this.  My application needs to be able to switch from one database to the other without having to worry about changing the syntax to match the database.  The company chooses to do this through the use of database specific SQL function calls.

I have the SQL Server version working fine but I am having issues with the Oracle version.  The function that I have is:

	ais_char_code 					int
	RETURN nchr(ais_char_code);

Open in new window

If I run the following sql statement I get the correct character from nchr but not my function call.

select nchr(255), sf_char(255)
from DUAL

Open in new window

I have tried changing the return data type to NCHAR, CHAR, VARCHAR2, and NVARCHAR2 and it doesn't seem to matter.  My function continues to return a black diamond with a white question mark.  

Can someone please point me in the right direction?
Question by:kmcbrearty
    LVL 37

    Expert Comment

    ascii is define only for 1-127 so i don't understand what you are trying to get when calling it with 256
    LVL 25

    Expert Comment

    I have added it to my database and it seems to work fine for me (y umlaut).  I would suggest using NCHAR however - but that is not the problem.
    As nchr works - it seems odd, very odd to me.
    screen shot
    LVL 3

    Author Comment

    It seems odd to me as well and it seems like this should not be a problem but I have reproduced this problem on two database servers.  Is there maybe a configuration setting somewhere that is causing the issue?
    LVL 25

    Accepted Solution

    I was thinking environment ... but then I would expect that to affect the nchr() too maybe.

    What do you get if you change the value back to the ascii number? e.g.
    does it return 255 or some other value?

    Have you tried ... although it should make no difference:
        CHR(255 USING NCHAR_CS)
    LVL 3

    Author Comment

    ASCII(sf_char(255)) returns 65533.
    CHR(255 USING NCHAR_CS) works as expected.

    I now have a working solution but you stated that my previous attempt worked on your system.  I would like to understand this issue a little bit better and was wondering if you had any thoughts on what the issue might be.  If not I will go ahead and accept your previous response.
    LVL 25

    Expert Comment

    I am not totally sure what is causing this.  Yes it worked for me.  From what I have managed to gather from reading a few things here and there on the web that it could be environmental ... and even session based and client dependant - based on the NLS_LANG setting of the connection.  I have semi replicated your result by using chr(128), which is above the 127 in standard ascii - this returned the black diamond with the ? mark.  Seems like it might be the default go to for 'I give up' - but I am just guessing.

    For the record, I am using Oracle 11g and my NLS_LANGUAGE is AMERICAN (SELECT * FROM NLS_SESSION_PARAMETERS).
    LVL 3

    Author Comment

    I am using Oracle 11g as well.  I also agree that the problem seems to be related to my environment and was also looking at the NLS_LANGUAGE settings. The results of the query that you provided were:

    PARAMETER                      VALUE                                    
    ------------------------------ ----------------------------------------
    NLS_LANGUAGE                   AMERICAN                                
    NLS_TERRITORY                  AMERICA                                  
    NLS_CURRENCY                   $                                        
    NLS_ISO_CURRENCY               AMERICA                                  
    NLS_NUMERIC_CHARACTERS         .,                                      
    NLS_CALENDAR                   GREGORIAN                                
    NLS_DATE_FORMAT                DD-MON-RR                                
    NLS_DATE_LANGUAGE              AMERICAN                                
    NLS_SORT                       BINARY                                  
    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_DUAL_CURRENCY              $                                        
    NLS_COMP                       BINARY                                  
    NLS_LENGTH_SEMANTICS           BYTE                                    
    LVL 25

    Expert Comment

    Your NLS params are the same as mine ... the defaults.

    I changed the function to return char instead of nchar and surprisingly it still displayed fine ... but the ascii of the result was not.
    I changed it back to nchar ... attached are the characters from nchr and the function in excel (well, approx 16k worth) - I hope this works.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now