Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Searching a database to find column value

Posted on 2008-06-10
7
Medium Priority
?
750 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 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

972 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