Solved

Searching a database to find column value

Posted on 2008-06-10
7
724 Views
Last Modified: 2013-12-19
Hi. I am trying to use a function which will search for a value in all columns in all tables in a given schema
like described here: http://www.adp-gmbh.ch/ora/misc/find_in_tables.html

It is invoked like: select find_in_schema('value') from dual;

Defined like:

create or replace function find_in_schema(val varchar2)
return varchar2 is
  v_old_table user_tab_columns.table_name%type;
  v_where     Varchar2(4000);
  v_first_col boolean := true;
  type rc     is ref cursor;
  c           rc;
  v_rowid     varchar2(20);

begin
  for r in (
    select
      t.*
    from
      user_tab_cols t, user_all_tables a
    where t.table_name = a.table_name
      and t.data_type like '%CHAR%'
    order by t.table_name) loop
 
    if v_old_table is null then
      v_old_table := r.table_name;
    end if;
 
    if v_old_table <> r.table_name then
      v_first_col := true;
 
      -- dbms_output.put_line('searching ' || v_old_table);
 
      open c for 'select rowid from "' || v_old_table || '" ' || v_where;
 
      fetch c into v_rowid;
      loop
        exit when c%notfound;
        dbms_output.put_line('  rowid: ' || v_rowid || ' in ' || v_old_table);
        fetch c into v_rowid;
      end loop;
 
      v_old_table := r.table_name;
    end if;
 
    if v_first_col then
      v_where := ' where ' || r.column_name || ' like ''%' || val || '%''';
      v_first_col := false;
    else
      v_where := v_where || ' or ' || r.column_name || ' like ''%' || val || '%''';
    end if;
 
  end loop;
  return 'Success';
end;
/

As mentioned I run this command in SQL plus:set serveroutput on size 1000000 format wrapped

Howerver, I am getting this error: select find_in_schema('SYSAPPLY') from dual
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "FPICDW_MART_V2.FIND_IN_SCHEMA", line 45


Any ideas on this, or another way to accomplish the objective? Thanks...


-Dinesh
0
Comment
Question by:dprasad
  • 4
7 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
One of your tables is probably an index-organized table,  That is, it doesn't have rowid's.
Modify your "r" cursor to exclude them...

 FOR r IN (SELECT   t.*
                  FROM user_tab_cols t, user_all_tables a
                 WHERE t.table_name = a.table_name
                   AND t.data_type LIKE '%CHAR%'
                   and iot_type != 'IOT'
              ORDER BY t.table_name)
    LOOP
0
 

Author Comment

by:dprasad
Comment Utility
Thanks. Yes, some of them don't have row id's now that you mention it. I can now run the script without an error and get the 'success' message. Howerver, it doesn't print the column or table. Can you think of a way it can be modified?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
you could modify the function to return the rowids instead of a success message.

I think you want to modify your loop processing as well
It doesn't look like you'll ever examine the last table in your list.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
oops, change

 and iot_type != 'IOT'  
to
and iot_type is NULL

that's why nothing was being returned,

Here's the pipelined version that returns the rowid's in a queryable format

create or replace type vcarray as table of varchar2(4000);
 
 

CREATE OR REPLACE FUNCTION find_in_schema (val VARCHAR2)

    RETURN vcarray PIPELINED

IS

    v_old_table   user_tab_columns.table_name%TYPE;

    v_where       VARCHAR2 (4000);

    v_first_col   BOOLEAN                            := TRUE;
 

    TYPE rc IS REF CURSOR;
 

    c             rc;

    v_rowid       VARCHAR2 (20);

    v_sql         VARCHAR2 (32767);

BEGIN

    FOR r IN (SELECT   t.*

                  FROM user_tab_cols t, user_all_tables a

                 WHERE t.table_name = a.table_name

                   AND t.data_type LIKE '%CHAR%'

                   AND iot_type IS NULL

              ORDER BY t.table_name)

    LOOP

        IF v_old_table IS NULL

        THEN

            v_old_table := r.table_name;

        END IF;
 

        IF v_old_table <> r.table_name

        THEN

            v_first_col := TRUE;

            -- dbms_output.put_line('searching ' || v_old_table);

            v_sql :=

                   'select rowidtochar(rowid) from "'

                || v_old_table

                || '" '

                || v_where;
 

            --DBMS_OUTPUT.put_line (v_sql);

            OPEN c FOR v_sql;
 

            FETCH c

             INTO v_rowid;
 

            LOOP

                EXIT WHEN c%NOTFOUND;

                PIPE ROW (v_rowid);

                DBMS_OUTPUT.put_line (   '  rowid: '

                                      || v_rowid

                                      || ' in '

                                      || v_old_table

                                     );
 

                FETCH c

                 INTO v_rowid;

            END LOOP;
 

            CLOSE c;
 

            v_old_table := r.table_name;

        END IF;
 

        IF v_first_col

        THEN

            v_where :=

                       ' where '

                    || r.column_name

                    || ' like ''%'

                    || val

                    || '%''';

            v_first_col := FALSE;

        ELSE

            v_where :=

                   v_where

                || ' or '

                || r.column_name

                || ' like ''%'

                || val

                || '%''';

        END IF;

    END LOOP;
 

    RETURN;

END;

/

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
if you have the stragg function (and I highly recommend you download and install it)

try this.  it's smaller and simpler, and it doesn't have the problem of skipping the last table


select * from table(find_in_schema('your_test_string'))
CREATE OR REPLACE FUNCTION find_in_schema (val VARCHAR2)

    RETURN vcarray PIPELINED

IS

    v_old_table   user_tab_columns.table_name%TYPE;

    v_where       VARCHAR2 (4000);

    v_first_col   BOOLEAN                            := TRUE;
 

    TYPE rc IS REF CURSOR;
 

    c             rc;

    v_rowid       VARCHAR2 (20);

    v_sql         VARCHAR2 (32767);

BEGIN

    FOR r IN (SELECT   t.table_name,

                          'select rowidtochar(rowid) from "'

                       || t.table_name

                       || '" where '

                       || REPLACE (stragg (   t.column_name

                                           || ' like ''%'

                                           || val

                                           || '%'''

                                          ),

                                   ',',

                                   ' or '

                                  ) sqlstr

                  FROM user_tab_cols t, user_all_tables a

                 WHERE t.table_name = a.table_name

                   AND t.data_type LIKE '%CHAR%'

                   AND iot_type IS NULL

              GROUP BY t.table_name

              ORDER BY t.table_name)

    LOOP

        OPEN c FOR r.sqlstr;
 

        FETCH c

         INTO v_rowid;
 

        LOOP

            EXIT WHEN c%NOTFOUND;

            PIPE ROW (v_rowid);

            DBMS_OUTPUT.put_line (   '  rowid: '

                                  || v_rowid

                                  || ' in '

                                  || r.table_name

                                 );
 

            FETCH c

             INTO v_rowid;

        END LOOP;
 

        CLOSE c;

    END LOOP;
 

    RETURN;

END;

/

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now