Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Searching a database to find column value

Posted on 2008-06-10
7
Medium Priority
?
744 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
7 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 21752950
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
ID: 21753195
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 74

Expert Comment

by:sdstuber
ID: 21753673
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 74

Expert Comment

by:sdstuber
ID: 21753806
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 74

Expert Comment

by:sdstuber
ID: 21753897
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

721 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