phoen08
asked on
Replace Oracle hex values
I'm having an issue with hex values being stored in an oracle database. The fields containing the hex values appear to be null whenever I search for null values in the database. The oracle data is being written to an XML file. However, when I try to validate the XML, I receive the following error:
', hexadecimal value 0x0C, is an invalid character
Is there anyway to identify and replace hex characters in oracle? I would like to fix this in my stored procedure before the data is accessed by the .net application.
Thanks
', hexadecimal value 0x0C, is an invalid character
Is there anyway to identify and replace hex characters in oracle? I would like to fix this in my stored procedure before the data is accessed by the .net application.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The replace function slightwv provided works great but I'm afraid I may need to trap all invalid hex characters. I'm thinking about writing a method in .NET to remove all invalid characters from the XML file before they are validated.
I'm not sure why the column containing the hex OC value returns null when I peform a select query. When the column value is written to the XML file it contains the hex values and also valid text
I'm not sure why the column containing the hex OC value returns null when I peform a select query. When the column value is written to the XML file it contains the hex values and also valid text
>>sure why the column containing the hex OC value returns null when I peform a select query.
What makes you think it is retuning 'null'? Displaying a non-printable character on the screen might 'appear' to be null but the database has a value for that column.
>>but I'm afraid I may need to trap all invalid hex characters.
You can tweak the function to replace everything EXCEPT a list of 'valid' chacters but you need to define what is 'valid'.
There are a few other character classes similar to SPACE that might work for you.
What makes you think it is retuning 'null'? Displaying a non-printable character on the screen might 'appear' to be null but the database has a value for that column.
>>but I'm afraid I may need to trap all invalid hex characters.
You can tweak the function to replace everything EXCEPT a list of 'valid' chacters but you need to define what is 'valid'.
There are a few other character classes similar to SPACE that might work for you.
ASKER
>>What makes you think it is retuning 'null'? Displaying a non-printable character on the screen might 'appear' to be null but the database has a value for that column.
I tested the value for null on the oracle side. I know the value is returning null because I used the NVL fuction to replace null with 0. Also used a select query to find the column that "is null".
I tested the value for null on the oracle side. I know the value is returning null because I used the NVL fuction to replace null with 0. Also used a select query to find the column that "is null".
If it is stored as a true 'null' in oracle, then something else is adding the HEX value.
It is impossible for "nvl(column,0)" to return a 0 if the column contains a '0C'. Either impossible or a pretty huge bug in your version of Oracle.
It is impossible for "nvl(column,0)" to return a 0 if the column contains a '0C'. Either impossible or a pretty huge bug in your version of Oracle.
http://en.wikipedia.org/wiki/ASCII#ASCII_control_characters
As slightwv says, "space" includes a form feed character
http://psoug.org/reference/regexp.html