asked on

ORACLE Function to Get Ascii Character from Code

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:

CREATE OR REPLACE FUNCTION sf_char 
(
	ais_char_code 					int
)
RETURN VARCHAR2
AS
BEGIN
	RETURN nchr(ais_char_code);
END;

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?

momi_sabag

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

Lee Wadwell

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.

kmcbrearty

ASKER

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?

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kmcbrearty

ASKER

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.

Lee Wadwell

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).

kmcbrearty

ASKER

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_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

Lee Wadwell

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.
Q-27868136.xlsx