Oracle Escape Characters

Oracle Tips by Burleson Consulting

Oracle allows the assignment of special escape characters to tell Oracle that the character is interpreted literally. Certain characters such as the underscore �_� are not interpreted literally because they have special meaning within Oracle.

In the example below, we want to find all Oracle parameter that relate to I/O, so we are tempted to use the filter LIKE �%_io_%�. Below we will select from the x$ksppi fixed table, filtering with the LIKE clause:

select ksppinm
from x$ksppi
where ksppinm like '%_io_%';

KSPPINM

--------------------------------

sessions

license_max_sessions

license_sessions_warning

_session_idle_bit_latches

_enable_NUMA_optimization

java_soft_sessionspace_limit

java_max_sessionspace_size

_trace_options

_io_slaves_disabled

dbwr_io_slaves

_lgwr_io_slaves

As you can see above, we did not get the answer we expected. The SQL displayed all values that contained �io�, and not just those with an underscore. To remedy this problem, Oracle SQL supports an ESCAPE clause to tell Oracle that the character is to be interpreted literally:

select ksppinm
from x$ksppi
where ksppinm like '%\_io\_%' ESCAPE '\';

KSPPINM

--------------------------------------

_io_slaves_disabled

dbwr_io_slaves

_lgwr_io_slaves

_arch_io_slaves

_backup_disk_io_slaves

backup_tape_io_slaves

_backup_io_pool_size

_db_file_direct_io_count

_log_io_size