You Asked

Hello there,

I'm trying to find the name of the table and column in which it has the particular string.

The below code searches for the string in the whole database and prints it out. However I wanted to use wild card because there are instances that the string value I'm searching for has got other characters in the field.

Also the string's case is not uniform across the database, sometime it is full upper case(REGISTER BOX), lower case(register box) and init caps(Register Box)

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%' AND TABLE_NAME NOT LIKE 'V\_%' ESCAPE '\'  ) LOOP

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

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

      END LOOP;

    END;
    /

Please help me in fixing this.

Joe

and Connor said...

Change

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

to something like this (I'm just using 'replace' so not too many nested quotes are needed)

 EXECUTE IMMEDIATE
    replace(replace(
          q'{SELECT COUNT(*) FROM @@tab where @@col like '%'||:1||'%'}',
         '@@tab',t.table_name),
         '@@col',t.column_name)
          INTO match_count
          USING 'REGISTER BOX';

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Joseph Swamidass, November 08, 2016 - 3:17 am UTC

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%' AND TABLE_NAME NOT LIKE 'V\_%' ESCAPE '\' ) LOOP
EXECUTE IMMEDIATE
replace(replace(
q'{SELECT COUNT(*) FROM @@tab where UPPER(@@col) like '%'||:1||'%'}',
'@@tab',t.table_name),
'@@col',t.column_name)
INTO match_count
USING 'REGISTER BOX';

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

END LOOP;

END;
/

Error report -
ORA-00942: table or view does not exist
ORA-06512: at line 7
00942. 00000 - "table or view does not exist"
*Cause:
*Action:

wondering why I'm receiving this error?!

November 09, 2016 - 2:10 am UTC

Yes :-)

So - to assist with your debugging, rather than do this:

execute immediate 'complex string' into match_count using 'REGISTER BOX';

do this:

l_str := 'complex string';
dbms_output.put_line(l_str);
execute immediate l_str into match_count using 'REGISTER BOX';

so you can *see* the SQL we are trying to run.

The answer should become self-evident

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

DBMS_OUTPUT

How to find the tables of a particular string

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

DBMS_OUTPUT