Home » SQL & PL/SQL » SQL & PL/SQL » Search the value in all fields
Search the value in all fields [message #264252] Sun, 02 September 2007 07:13 Go to next message
VVince
Messages: 2
Registered: September 2007
Junior Member
Hello !

I am dealing with the database structure that consists of several tables (about 30). The task is to find records that contain the specified value. The column name and table name that contain this value for sure are not specified. My solution was to create SQL query for all possible columns and tables that contists of multiple SQL blocks that are similar to each other and are connected by 'UNION'. As the result of it the query becomes huge and performance suffers a lot. So can you please tell be what could be the less resource consuming solution and what concrete features of oracle can be used to improve performance.

Thanks.
Re: Search the value in all fields [message #264262 is a reply to message #264252] Sun, 02 September 2007 09:15 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd use USER_TAB_COLUMNS and loop through all tables and all columns in those tables. Here's an example how to do *something like that* (an old query I used) - you'll have to modify it in order to get the result, but I hope you'll get the idea.
DECLARE
   str     VARCHAR2 (1000);
   l_cnt   NUMBER;
BEGIN
   FOR cur_t IN (SELECT table_name
                   FROM user_tables)
   LOOP
      BEGIN
         str :=
               'SELECT COUNT(*) FROM '
            || cur_t.table_name
            || ' WHERE pogon = ''400501'''; 

         -- In the WHERE clause above I knew which column I'm 
         -- searching for - it was 'pogon'. You'd use 
         -- USER_TAB_COLUMNS.COLUMN_NAME instead
			
         EXECUTE IMMEDIATE (str)
                      INTO l_cnt;

         IF l_cnt > 0
         THEN
            DBMS_OUTPUT.put_line (cur_t.table_name);
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   END LOOP;
END;
/
Re: Search the value in all fields [message #264296 is a reply to message #264252] Sun, 02 September 2007 22:54 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
My suggestion would be to sort out your table design. If you have to search through all columns in all tables (which is what the wording of you question implies) then there is something woefully wrong with your design.
Re: Search the value in all fields [message #264297 is a reply to message #264252] Sun, 02 September 2007 23:03 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Then all_tables and all_tab_columns
Re: Search the value in all fields [message #264355 is a reply to message #264252] Mon, 03 September 2007 01:47 Go to previous message
VVince
Messages: 2
Registered: September 2007
Junior Member
This structure was designed so to provide audit trail possibility for multiple entities that are represented by the corresponding data tables. Every modification of these tables is reflected in their copies, which are connected with one extra table in relation 1:1. This extra table has information about the parameters of changes(DateTime, User, ...). And the mentioned above query must search for the changes (resulting information received by joining the entity copy table with the extra table) that include the specified value, but user may not specify the column to search this value.
Previous Topic: pl/sql procedure
Next Topic: Please help on this query
Goto Forum:
  


Current Time: Sun Dec 04 00:51:39 CST 2016

Total time taken to generate the page: 0.09006 seconds