I want to perform a search for a specific value "04L" in the column across all the tables,procedures,functions etc in the DB. I also want that output ( table,procedures that have same value) to the text file i.e the tables , procedures functions that have that value.
Any help appreciated.
It'll have to be in PL/SQL, I don't think there's any way you can do that in a single query. You can iterate through all tables names (found in all_tables) and then iterate through all columns (found in all_table_columns) and have a dyanmic sql query that will do that actual updating (or whatever it is you want).
Not sure how you'd do the procedures and functions, maybe query user_source, and use the same trick as above. Of course, you'd probably have to recompile all the objects, and I have no way of knowing if that would succeed. I would bet not, at least not on the first try. ;)
You could use a dynamic query that uses SQL-generating-SQL.
Examining the pieces:
Dynamic SQL allows you to use the EXEC SQL command to execute a string as a SQL statement. This is a powerful technique that lets you generate or load a SQL statement into a string and then evaluate it as if it is a normal statement.
SQL-generating-SQL is a technique where we use SQL (typically against the Oracle data dictionary) to produce a string that is itself valid SQL. A simple example is:
SELECT 'SELECT * FROM ' || all_tables.table_name
FROM all_tables
which would produce a result set consisting of strings that are themselves SELECT statements against all of the tables available in your environment.