Monday, August 3, 2009

PL SQL query to create a search query.

Hi,

Often we search for a particular value in the user tables of a database, for each table we have to change the query, if not the column name, at least the table name. The good new is, we can avoid doing that. We can write a query which will create a search query as its result records each for a user table in the database.

select 'select * from '||table_name||'
where upper (to_char(&in_column)) = upper (to_char('||'''&in_value'''||'));'
from user_tab_columns
where column_name = upper ('&in_column');


Run tha above query in PL SQL and see the magic.

Hope that helps you.

Thanks..