Solved

Replace Oracle hex values

Posted on 2011-03-09
6
1,727 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 77

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
How our DevOps Teams Maximize Uptime

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

 
LVL 77

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 77

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

738 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