SQL & PL/SQL

Hey all,

Here is the problem I am trying to solve:
I have a BPEL interface to another system where they send an xml message to me and I parse the message and perform whatever transaction is in the message. The message is sent as a CLOB to my internal database procedure. My first problem was that the message contained characters that aren't in my database's character set. When the function xmltype.createxml(p_xml) is called, it throws an "invalid character" error because of these characters.

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00216: invalid character 151 (0x97)

I had the idea of creating a translation table to convert these special characters to characters that are in my character set. Now the problem comes when trying to get the data out of the CLOB. If I use the DBMS_LOB.SUBSTR() function, the special characters are replaced with a question mark because they are compared against the character set before they are saved in the VARCHAR2 variable. I have come up with an idea to convert the clob to a blob and extract the characters one by one using the DBMS_LOB.READ() function then convert them to characters I can understand. When the new blob is constructed, I will convert it back to a clob and continue with processing the message.

Now the problem is that when I use the DBMS_LOB.CONVERTTOBLOB() procedure, the character set is applied to the clob before it is translated to a blob. Is there a way to get the binary data from the CLOB before it is impacted by the database's character set?

Thanks!

Jonathan
Oracle 10.2.0.4.0