Oracle: How to replace non printable characters in a string

You can replace non printable characters in a string using regexp_replace

select regexp_replace('Test'||chr(10)||chr(13)||chr(1)||' String ', '([^[:print:]])',' ') from dual

If you want to retain the the new line characters like chr(10) and chr(13) then

Select regexp_replace('Test'||chr(10)||chr(13)||chr(1)||' String ','['||chr(1)||'-'||chr(9)||']|['||chr(11)||'-'||chr(12)||']|['||chr(14)||'-'||chr(31)||']',' ') from dual

Above sql is removing chr from 1-9, 11-12 and 14-31



Here is SQL to identify printable, non printable, control characters

select level,CHR(level),
decode(chr(level), regexp_substr(chr(level), '[[:graph:]]'), '*') is_graph, --printable character
decode(chr(level), regexp_substr(chr(level), '[[:blank:]]'), '*') is_blank,
decode(chr(level), regexp_substr(chr(level), '[[:alnum:]]'), '*') is_alnum, --Any alphanumeric character, [A-Za-z0-9]
decode(chr(level), regexp_substr(chr(level), '[[:alpha:]]'), '*') is_alpha, -- Any letter, [A-Za-z]
decode(chr(level), regexp_substr(chr(level), '[[:digit:]]'), '*') is_digit, -- Any digit, [0-9]
decode(chr(level), regexp_substr(chr(level), '[[:cntrl:]]'), '*') is_cntrl, --Any character not part of the character classes: [:upper:], [:lower:], [:alpha:], [:digit:], [:punct:], [:graph:], [:print:], [:xdigit:]
decode(chr(level), regexp_substr(chr(level), '[[:lower:]]'), '*') is_lower, -- Any lowercase letter, [a-z]
decode(chr(level), regexp_substr(chr(level), '[[:upper:]]'), '*') is_upper, --Any uppercase letter, [A-Z]
decode(chr(level), regexp_substr(chr(level), '[[:print:]]'), '*') is_print, --Any printable character
decode(chr(level), regexp_substr(chr(level), '[[:punct:]]'), '*') is_punct, --Any punctuation character: START ! ' # S % & ' ( ) * + , - . / : ; < = > ? @ [ / ] ^ _ { | } ~ END
decode(chr(level), regexp_substr(chr(level), '[[:space:]]'), '*') is_space, --A tab, new line, vertical tab, form feed, carriage return, or space
decode(chr(level), regexp_substr(chr(level), '[[:xdigit:]]'), '*') is_xdigit --Any hexadecimal digit, [0-9A-Fa-f]
from dual
where level between 0 and 255
connect by level <= 256

Vedas

Vedas are holy books of Hinduism, written in Sanskrit sometime between 1500 and 500BC

There are 4 Vedas (Sacred Texts)

1. Rig Veda : Knowledge of Praise. It contains Hymns(verses) praising gods
2. Sama Veda : Knowledge of Sacred Songs(chants). It contains Hymns recited as religious services
3. Yajur Veda : Knowledge of Rites(Sacrifices).Its divided into 2 parts, Black Yajurveda Taittiriy & White Yajurveda Vajasaneyi
4. Athara Veda :

More to come...

Vedas

Vedas are holy books of Hinduism, written in Sanskrit sometime between 1500 and 500BC

There are 4 Vedas (Sacred Texts)

1. Rig Veda : Knowledge of Praise. It contains Hymns(verses) praising gods
2. Sama Veda : Knowledge of Sacred Songs(chants). It contains Hymns recited as religious services
3. Yajur Veda : Knowledge of Rites(Sacrifices).Its divided into 2 parts, Black Yajurveda Taittiriy & White Yajurveda Vajasaneyi
4. Athara Veda :

More to come...

கடவுள் இல்லை என்பவனை நம்பலாம்....
கடவுள் இருக்கு என்பவனையும் நம்பலாம் ..
ஆனால் நான் தான் கடவுள் என்று சொல்பவனை என்றும் நம்பவே கூடாது

PLS-00306 WRONG number or types of arguments in call to XMLAGG

This a bug in Oracle. This error is thrown when you use ORDER BY clause with XMLAGG in PL/SQL.

Work Around: Remove the ORDER BY Clause

Funny part is, the SQL will work fine in SQLPLUS, but when you use in PL/SQL it will throws the above error.

Versions confirmed as being affected: 9.2.0.2 to 9.2.0.6

This bug is fixed from 9.2.0.7 onwards