Solved

Searching a database to find column value

Posted on 2008-06-10
7
728 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
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 73

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 73

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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Bulk insert into global temporary table 2 63
compre toata in where clue oracle 4 56
automatic email alert 1 42
join 2 views with 5 conditions 3 45
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

920 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

16 Experts available now in Live!

Get 1:1 Help Now