0

Possible Duplicate:
Search All Fields In All Tables For A Specific Value (Oracle)
Search for a given string in all fields of an entire schema for Oracle

I have this:

select * from 
{select table_name
from user_tab_columns
where data_type = 'VARCHAR2')

but, of course, it's not working. I'm only using SQL Developer and not sure how to proceed.

I thought I was asking in the above query to select all from each table, but that is not correct. I saw some answers on SO, but they didn't help. I don't need just column_namein user_tab_columns, I need the values of the columns

I really just want to find where the name 'JONES,' is in the database. I don't know the table or the column, just that the value is there...somewhere.

Thanks.

When I ran the code from:

Search All Fields In All Tables For A Specific Value (Oracle)

 SET SERVEROUTPUT ON SIZE 100000

        DECLARE
          match_count INTEGER;
        BEGIN
          FOR t IN (SELECT owner, table_name, column_name
                      FROM all_tab_columns
                      WHERE owner <> 'SYS' and data_type LIKE '%CHAR%') LOOP

            EXECUTE IMMEDIATE
              'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
              ' WHERE '||t.column_name||' = :1'
              INTO match_count
              USING 'JONES%';

            IF match_count > 0 THEN
              dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
            END IF;

          END LOOP;

        END;
        /

I received:

Error report:
ORA-00920: invalid relational operator
ORA-06512: at line 8
00920. 00000 -  "invalid relational operator"
*Cause:    
*Action:

EDIT: for some reason oracle does not like the not equal <> tried != as well.

EDIT: if I run just:

SELECT owner, table_name, column_name
                      FROM all_tab_columns
                      WHERE owner != 'SYS' and data_type LIKE '%CHAR%'

I get no error.

If I run without the owner != 'SYS', I get insufficient privilege (I own the db though)

remove owner != 'SYS' runs, put it back with != or <>, fails

EDIT: trying CONNECT BY at the moment

Community
  • 1
  • 1
johnny
  • 19,272
  • 52
  • 157
  • 259
  • 1
    See http://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle – Codo Jul 26 '12 at 15:30
  • @Codo that did not help me. It did not work in sql developer. it is not a duplicate. – johnny Jul 26 '12 at 15:31
  • Can you be a bit more specific than *did not work*? Did it produce an error and if so, what error? Didn't you understand how to use it? Didn't if find anything? – Codo Jul 26 '12 at 15:36
  • Change that to `OWNER NOT IN ('SYS')` and see if you still get the error? – Radix Jul 26 '12 at 16:05
  • 1
    The error probably occurs in the EXECUTE IMMEDIATE statement, and not in the SELECT of the FOR loop. Print the table and column name before each EXECUTE IMMEDIATE to see where it fails. It's probably related to the type of the table or the column. And for the percent sign (JONES%) to be effective, you need to change the equal operator to LIKE. – Codo Jul 26 '12 at 17:07
  • why the heck are there closes for this? Why??? – johnny Jul 26 '12 at 17:14
  • @codo do you know how to print that out? I am new to pl/sql. – johnny Jul 26 '12 at 17:18
  • 1
    It's: `dbms_output.put_line(t.table_name ||' '||t.column_name);`. And you have to turn output on in the DBMS Output view of SQL Developer. – Codo Jul 26 '12 at 17:21
  • @codo thank you. I would give you the checkmark for the answer, but it's all in comments. I truly appreciate your help. It blows up on a table in the output, but I don't why. At least the query runs though. – johnny Jul 26 '12 at 17:34
  • 1
    Do you have a table or column with an extremely weird name? You may want to surround the names with quotation marks. – Jon Heller Jul 26 '12 at 19:55

0 Answers0