Removing NUL characters
When one of my jobs sends an email from the database, it gets the error message "ORA-29279: SMTP permanent error: 550 Requested action not taken: NUL characters are not allowed." The data in one of the columns has trailing NUL values. These are hex value 00. I can't see them and they don't seem to be good for much except getting rejected by spam filters and mail servers.
How do I remove these NUL characters? I've looked at the REPLACE function, but I'm not sure on how to pass it characters that can't be typed. Is there some way to pass it characters in hex representation? I didn't see any such thing in the documentation.
I'm using Oracle 9.2 with UTF8 character set.