The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
Solved
Replace Oracle hex values
Posted on 2011-03-09
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
[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
Active 1 day ago
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,'[[:
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.
When selecting it out of the database try:
Regexp_replace(column,'[[:
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.
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
http://en.wikipedia.org/wiki/ASCII#ASCII_control_characters
As slightwv says, "space" includes a form feed character
http://psoug.org/reference/regexp.html
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
Active 1 day ago
>>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.
>>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".
Featured Post
Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Working with Network Access Control Lists in Oracle 11g (part 2)
Part 1: http://www.e-e.com/A_8429.html
Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access. For many…
Configuring and using Oracle Database Gateway for ODBC
Introduction
First, a brief summary of what a Database Gateway is. A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month9 days, 9 hours left to enroll
609 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.