Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    10

    Unanswered: whole namespace string search procedure

    I need to run a string search on every field of every record of every table in a given namespace. Here's my code so far:
    Code:
    create or replace procedure search_value_proc (user_name IN VARCHAR2, search_string IN VARCHAR2)
    AS
    foundIt NUMBER;
    BEGIN
    ---this cursor will get all the tables in the <user_name> schema.
    FOR table_rec IN (select table_name, column_name from all_tab_columns where owner=user_name) LOOP
    ---this will loop through each table and each column of it to search for a specific value, if it has, the the ----count(*) will be greater than 1.
    
          execute immediate 'select count(*) from '|| table_rec.table_name || ' where '||table_rec.column_name || ' like '||chr(39)||'%'||search_string ||'%'||chr(39) INTO foundIt;
          IF foundIt > 0 THEN
          dbms_output.put_line(table_rec.table_name || '.'||table_rec.column_name|| ' contains ' || search_string);
          END IF;
    END LOOP;
    END;
    /
    It works well with a small amount of data (such as the default SCOTT tables), but I am trying to run this on a somewhat large database (more than 5GB), and get the following error:

    ERROR at line 1:
    ORA-00932: inconsistent datatypes
    ORA-06512: at "<user_name>.SEARCH_VALUE_PROC", line 9
    ORA-06512: at line 1

    And of course line 9 is the heart of the whole operation:
    Code:
    execute immediate 'select count(*) from '|| table_rec.table_name || ' where '||table_rec.column_name || ' like '||chr(39)||'%'||search_string ||'%'||chr(39) INTO foundIt;
    Any suggsetions on how I could type proof this without slowing everything down? Or maybe just handle the exception and have the loop keep searching?
    Last edited by Gherald; 10-29-03 at 16:29.

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: whole namespace string search procedure

    The problem is you are doing character comparisons on columns that aren't characters. If you restrict your select on tables to only columns that are char or varchar then you won't run into this problem.

    select table_name, column_name
    from all_tab_columns
    where owner=user_name
    and data_type in ('VARCHAR','VARCHAR2','CHAR')

    Of course this means you won't search blobs and other large objects, but a search routine like this won't work on those types of data anyways.

    HIH

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    try trapping the wrong statement and showing it on the sqlprompt.
    This way u can figure out why is says 'inconsistent datatype'. This indicates that u try to put a date into a number for example.
    So check out the statement, check out if it works on sqlplus and what it returns and compare it to what the proc does. I don;t think u need to rewrite the code, but only a small enhancement.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  4. #4
    Join Date
    Oct 2003
    Posts
    10
    Thanks carloa, I think that'll solve my problem. Will know for certain in a few hours

  5. #5
    Join Date
    Oct 2003
    Posts
    10
    I've been using this procedure to great effect (well, usefulness) over the years. But now I have a new problem:

    Code:
    SQL> set serveroutput on
    SQL> exec search_value_proc('SCOTT', 'SALESMAN');
    BEGIN search_value_proc('SCOTT', 'SALESMAN'); END;
    
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at "RNAPGM.SEARCH_VALUE_PROC", line 9
    ORA-06512: at line 1
    
    
    SQL>
    And of course STILL have not had time to learn proper debugging techniques... how do I find out what is going on?
    I would like to "trap" the statement as evanhattem suggests, but have no idea where to start.

    Alternatively, is there exception handleing so I can just ignore such pesky errors and move on? As you can probably guess, I'm really not interested in tables that don't exist.

    Many thanks,
    Gherald

  6. #6
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    column search

    Looking at your error, I would guess the problem is you are trying to query a table for which you do not have privileges to select against.

    You can wrap exception handling in your search procedure, and display what you were trying to run when you hit an error or if you prefer, you can ignore errors.

    This is how you setup exception handling to ignore errors.
    Code:
    BEGIN
       --- Your Code goes here
    EXCEPTION
       WHEN OTHERS THEN 
            NULL;
    END;
    To display debug info try something like

    Code:
    BEGIN
       --- Your Code goes here
    EXCEPTION
       WHEN OTHERS THEN 
            DBMS_OUTPUT.PUT_LINE('Error Searching Table:'   
                                               ||table_rec.table_name||
                                               ' Column: '||table_rec.column_name);
    END;
    HIH

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You might want to ignore -00942 errors, but I woulnd't recommend you to ignore all of them as the method suggested above. What I would do, is to catch and handle only those I am expecting to have ( and therefore, want to ignore ) and leave the others to Oracle to handle them. You can map that error message to your own exception with the compiler directive pragma exception_init.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •