Take the 2-minute tour ×

Here is a puzzler. I want to write a procedure that checks the tables for any characters that break XML code. Those can be found in the W3C Recommendation, but that's not important right now. What's important is that:

1) The character 'ç' has an ASCII code 135. That's a fact. However, when I run


I get 50087.

2) When I run


I get pure nothing.

Well apparently, ascii() and chr() handle only values in 0..127. So my question is how to I find the unicode equivalents OR write my own extensions that work with values like 'ç' and 135.

Help will be much appreciated.

P.S. I am using Oracle SQL Developer.

share|improve this question
"The character 'ç' has an ASCII code 135. That's a fact." No it's not a fact. Any ASCII value over 127 is a matter of opinion. Or rather, a matter of which particular extended ASCII set we're using. In some extended sets, ASCII 135 actually renders ‡ No doubt other extended ASCII sets give other characters, –  APC Mar 31 '11 at 20:20
Agreed. Still, 50087 somewhat exceeds power(2, 8) to be an ASCII code. I am going to paraphrase my problem. I want to purge all strings from problem characters that users could have copied from a word document for example. I am doing this to make sure that the generated XML is legal in the end. –  MartinSeven Mar 31 '11 at 20:24
My guess is that I have to use UNICODE. Now that should be standardized, right? And valid XML characters are defined in terms of UNICODE codes. Where are the PL/SQL functions for those? Is there a function that takes –  MartinSeven Mar 31 '11 at 20:31
what does "dump(column, 1016)" output? –  oluies May 24 '11 at 20:12
The 50087 can be explained as follows: ç has Unicode Code Point U+00E7. This, encoded as a byte string in UTF-8 is 0xC3 0xA7. This, interpreted as a decimal number in base 256 is 195 * 256 + 167, which is 50087. –  Roland Illig May 28 '11 at 8:19

3 Answers 3

It's not quite clear what problem you're trying to solve. Convert user input to the right charset or validate that you have valid XML? If the latter, I think converting to the built in XMLType will validate the input as syntactically valid. You can even validate that it follows a given XML schema.

share|improve this answer

If you just want get rid of weird characters, you can use the REGEXP_REPLACE function. For example,

REGEXP_REPLACE(your_value, '[:cntrl:]', '')

would strip out all control (non-printing) characters.

REGEXP_REPLACE is available from Oracle 10g Rel. 2 onwards. The documentation is at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions130.htm

share|improve this answer

the plsql functions to handle arbitrary character sets ( well, as far as the rdbms knows about them ) are located in the packages utl_i18n and utl_raw. for your specific problem i'd suggest a test like the following:

    select <pk_column_of_table_to_check>
         , instr (
              utl_i18n.string_to_raw ( 
                 , 'UTF8'
            , hextoraw ( <hex_rep_in_utf8>  )
      from <table_to_check>

if you want to check against unicode chars whose utf8 representation is not readiliy available to you, use the term

      utl_raw.convert ( hextoraw ( <hex_rep_in_utf16>, 'UTF16', 'UTF8' ) )

as the second argument to instr. do not rely on the absolute positions returned by instr but only on the dichotomy 0/non-0 since you do not compare by character but on byte level.

utf8 and utf16 are 2 different byte-level encodings for the unicode character sets in the sense of named character entities; details can be found on wikipedia and unicode.org

note that the utf8 representation allows byte-level containment tests a la instr by design. also note that utf16 encoding should be readily available as it is the familiar U+<4 hex digits> representation for unicode chars.

the byte-level representation of the incriminated characters should be available from the (xml) standard. otherwise you must have an idea of how the char is named and look it up in the code point database at unicodde.org or aomeweher else. there are also online conversion tools if you only know the charset name but have some text sample in a file on your system, i can look up uris if you need to.

Hope this helps.

ps: after reading your first comment more precisely, i think you might find yourself on a mission impossible: to properly interpret byte sequences from single-byte charset encodings it is indispensable to keep the information about the charset in use. wouldn't that information be lost when the user copies text from the word processor (set to a specific charset [encoding]) into the database (where it will be stored in the database character set) as anly the byte sequence is copied ? you might end up lucky when both ends are set to a unicode flavor or the db charset encoding is utf8 (so some character copying will fail) but once teh data is in the database you'll hav a hard time to recover the original (maybe with dictionary support)

share|improve this answer
By the way: UTF-16 is not a byte-level encoding, but a uint16-level encoding. Therefore the 16 in its name. If you need a byte-level encoding similar to UTF-16, you can choose between UTF-16LE and UTF-16BE. –  Roland Illig May 28 '11 at 8:12

Your Answer


