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

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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