Catchback: Oracle Reg Expressions

This is useful in cases where there is a need to replace only a part of a text and the before and after parts have to be retained.

Eg: http://mywebsite/page/1.htm has to be changed to http://yoururl/page/1.htm

select regexp_replace(URL, 'http://.*mywebsite(.*)','http://yoururl\1')
from table


Using the (.*) to put the /page/1.htm is called catchback

 

Oracle: Query Rewrite settings

Query rewrite is a very useful concept provided by Oracle so that queries on a table can be redirected to a Materialized view. I am not too familiar with this, so that will be the topic of another post.

This is a quick post about the settings on the database to enable query rewrite.

The following query helps in knowing the settings:

show parameter rewrite


In the results, the query_rewrite_enabled property must be true.

If it is false, query rewrite will not happen

To set it to true, use the following:

alter system set query_rewrite_enabled = TRUE ;