• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 756
  • Last Modified:

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
0
dprasad
Asked:
dprasad
  • 4
1 Solution
 
sdstuberCommented:
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
 
dprasadAuthor Commented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now