asked on

Searching a database to find column value

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

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dprasad

ASKER

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?

Sean Stuber

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.

Sean Stuber

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

Sean Stuber

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