Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2304
  • Last Modified:

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?
0
kmcbrearty
Asked:
kmcbrearty
  • 4
  • 3
1 Solution
 
momi_sabagCommented:
ascii is define only for 1-127 so i don't understand what you are trying to get when calling it with 256
0
 
lwadwellCommented:
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
0
 
kmcbreartyAuthor Commented:
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?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
lwadwellCommented:
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.
    ASCII(sf_char(255))
does it return 255 or some other value?

Have you tried ... although it should make no difference:
    CHR(255 USING NCHAR_CS)
0
 
kmcbreartyAuthor Commented:
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.
0
 
lwadwellCommented:
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).
0
 
kmcbreartyAuthor Commented:
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
0
 
lwadwellCommented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now