SQL & PL/SQL

Is there a good way to get the ASCII values for an entire clob? I know I can use the ascii() function on a single character, but if I try it on a string it seems to only return the value of the first character. For example,

select ascii('hello') from dual;


will return the single value 104 since that is the decimal value of 'h' in ASCII. What I really want is what the String object does in Java when you call getBytes(). So something like this:

String s = "hello";
byte[] ascii = s.getBytes();


After running that second line the array ascii would hold [104, 101, 108, 108, 111].



I have a client that is getting some weird characters when reports are generated and I have a feeling it's a DB encoding issue (they use WE8MSWIN1252 instead of UTF8), and/or they copied/pasted the string from somewhere and it pasted in some unprintable characters (Excel is notorious for doing this) that get rendered incorrectly when the report is generated. My goal is to give them a select statement that gets the ASCII bytes and then run the same statement against my copy of their database. Then I can just use a diff utility on the two outputs to see if/where the bytes differ.

So I have a clob in my table that has say 20,000 characters in it. Can I run some select statement in oracle that will give me the entire string of ASCII bytes for that clob? I tried using the dump() function but that doesn't seem to work. I get the following error:

ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action: