This discussion is archived
7 Replies Latest reply: Mar 27, 2009 11:21 PM by 107829 RSS

How to search in all rows and all columns?

677614 Newbie
Currently Being Moderated
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. Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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%'
                    )

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points