7 Replies Latest reply: Mar 28, 2009 1:21 AM by 107829 RSS

    How to search in all rows and all columns?

    movilogo
      Suppose I want to search for a word but I don't know in which column it exists.

      Is there any function to do search in all columns?

      Or it is something I need to write myself by parsing data dictionary and then looping thru all columns in my procedure?
        • 2. Re: How to search in all rows and all columns?
          Kamran Agayev A.
          Another solution

          1. Create a cursor by selecting DBA_OBJECTS. Get all Objects you want (Tables, views)

          2. Create a cursor by selecting DBA_TAB_COLUMNS. Pass object_name from first cursor and add WHERE clause to get all "WHERE OBJECT_TYPE IN ('CHAR','VARCHAR2')


          - - - - - - - - - - - - - - - - - - - - -
          Kamran Agayev A. (10g OCP)
          http://kamranagayev.wordpress.com
          • 3. Re: How to search in all rows and all columns?
            MichaelS
            Is there any function to do search in all columns?
            SQL> select table_name, 
                   column_name, 
                   :search_string search_string, 
                   result
              from cols, 
                   xmltable(('ora:view("'||table_name||'")/ROW/'||column_name||'[ora:contains(text(),"%'|| :search_string || '%") > 0]')
                   columns result varchar2(10) path '.'
                   )
            where table_name in ('EMP', 'DEPT')
            /
            TABLE_NAME           COLUMN_NAME          SEARCH_STRING        RESULT    
            -------------------- -------------------- -------------------- ----------
            DEPT                 DNAME                ES                   RESEARCH  
            DEPT                 DNAME                ES                   SALES     
            EMP                  ENAME                ES                   JONES     
            EMP                  ENAME                ES                   JAMES     
            EMP                  JOB                  ES                   SALESMAN  
            EMP                  JOB                  ES                   SALESMAN  
            EMP                  JOB                  ES                   SALESMAN  
            EMP                  JOB                  ES                   PRESIDENT 
            EMP                  JOB                  ES                   SALESMAN  
            
            9 rows selected.
            • 4. Re: How to search in all rows and all columns?
              Sven W.
              Nice select. Does this work already in 10.2 db or is it 11g only?

              I get some error "ORA-19102: XQUERY string literal expected" when trying in 10g.
              • 5. Re: How to search in all rows and all columns?
                MichaelS
                Unfortunately I don't have 10g at hand. So presumably the query is for 11g only.
                • 6. Re: How to search in all rows and all columns?
                  Gaff
                  You might try an Oracle Text [ approach|http://asktom.oracle.com/pls/asktom/f?p=100:11:1301326260255400::::P11_QUESTION_ID:5533095920114] .
                  • 7. Re: How to search in all rows and all columns?
                    107829
                    Michaels2 has probably a nicer way to do it in 11g, here is one that should work in at least 10g also. (I think) And possibly even in 9i.

                    %YORK% is of course where the search condition is placed. Note that this searches through XML tags and such so if the search would be equal to part or whole column name, then all rows will be found. That could be fixed with regexp like to make sure the found value is not inside a tag.
                    select * from dept
                     where rowid in (select cast(extractvalue(xmltype(data_row), '/ROW/RI') as rowid) ri
                                       from (select cast(column_value as varchar2(4000)) data_row 
                                               from table(xmlsequence(extract(xmltype(dbms_xmlgen.getxml('select rowid ri, a.* from dept a')),'/ROWSET/ROW')))
                                            )
                                      where data_row like '%YORK%'
                                    )