Category Archives: Oracle

ORACLE: How to escape special characters

We often need to escape special characters while retrieving the rows. To do that there are two ways;

  1. Use “SET ESCAPE ‘{ESC CHAR}’
  2. End the select statement with ‘ESCAPE ‘{ESC CHAR}’

For example;

SELECT SL.SYSTEM_CODE, SL.SYSTEM_DESCRIPTION, SL.SYSTEM_PARTICULARS FROM SYSTEMS_LIBRARY SL WHERE SL.SYSTEM_CODE LIKE 'UOTE\_%' ESCAPE '\'

the above statement returns all the systems with system code like ‘UOTE_’. But i cannot use the underscore directly in the quotes. Therefore I want to escape it with \ and tell Oracle to treat it as escape character by typing “ESCAPE ‘\'” at the end of the select statement.

ORACLE: View compiled with errors

Many times when ever we try to create the views under Oracle, often end up with messages like “View created with warnings” or ” View compiled with errors”. But SQL Developer does not reveal the error. In such cases to know the error actually generated fire the following query in SQL Developed or SQL Plus prompt;

SELECT TEXT FROM DBA_ERRORS WHERE NAME='VIEW_NAME_HERE'

If you have same view name in more than one schema then put that target schema name as

AND OWNER='SCHEMA_NAME'

This table always contains most recent error raised on that object.