Solved

Replace Oracle hex values

Posted on 2011-03-09
6
1,572 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:phoen08
  • 3
  • 2
6 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35089171
Apppear to be null and are null are different.  Null is 'nothing'.  My guess is it appears to be white space.

When selecting it out of the database try:

Regexp_replace(column,'[[:space:]]',' ')

I'm not sure what the hex value 0C is but if it is considered 'space' it should work.

If not, you might need to add to the replace call.
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 35094520
0x0C is form feed (\f)

http://en.wikipedia.org/wiki/ASCII#ASCII_control_characters

As slightwv says, "space" includes a form feed character
http://psoug.org/reference/regexp.html
0
 

Author Comment

by:phoen08
ID: 35096102
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
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35096279
>>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.
0
 

Author Comment

by:phoen08
ID: 35096563
>>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".
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35096598
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.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now