PL/SQL - Find String Value

[InsaneProgrammer]

I'm trying to write a stored procedure that will loop through a schema looking for a string. I need it to search all tables and all columns. I'm able to get the list of all of the tables and columns to search and then populate a temp table with them. I can't get the syntax correct for building my dynamic SQL statement to search for the string. Does that make sense? Can anyone help?

create or replace procedure find_value (schema_name in varchar2, search_string in varchar2)

as

  --Cursor with all of the columns to search
  cursor cur_cols_to_srch is
    select dtc.owner || '.' || dtc.table_name tbl_name, dtc.column_name
    from dba_tab_cols dtc
    where dtc.table_name in
      (select dt.table_name
      from dba_tables dt
      where dt.owner = schema_name
      and dt.num_rows > 0)
    and dtc.data_type in ('CHAR','VARCHAR2');
    
    --Search results
    v_results   varchar2(4000);

begin

  --Create temp table for the results of the search
  execute immediate 'create global temporary table tbl_results_tmp
    (TableName varchar2(200),
    ColumnName varchar2(200),
    ColumnValue varchar2(4000))';  

  dbms_output.put_line('Temp Table Created');

  --Make the search string upper case
  search_string :=  upper(search_string);

  --Add wild card characters to the search string
  search_string := '%' || search_string || '%';

  for col in cur_cols_to_srch
     
--***************************************
--Some how I need to select from the tables and columns
--and then test to see if it matches the string.
-- If it matches then insert it into the results temp table.


    --execute immediate 'select ' || col.column_name ||
    --                  ' into ' || v_results ||
    --                  ' from ' || col.tbl_name ||
    --                  ' where ' || upper(col.column_name) || ' like ' || search_string;
    
    --execute immediate 'select ' || col.column_name ||
    --                  ' into v_results 
    --                   from ' || col.tbl_name ||
    --                  ' where ' || upper(col.column_name) || ' like ' || search_string;
   
    --select col.column_name
    --      into v_results
    --      from col.tbl_name
    --      where upper(col.column_name) like search_string;

--***************************************
    
    dbms_output.put_line (v_results);
    
    --If the value in the column matched the string upate the results table
    if v_results is not null then
      insert into tbl_results_tmp values (col.tbl_name, col.column_name, v_results);
    end if;
    
    dbms_output.put_line('Insert Done');
    
  end loop;

  --Close the cursor
  close cur_cols_to_srch;
  
  dbms_output.put_line('Done');
  
end;

Thanks

InsaneProgrammer.com

 

[LKBrwnDBA]

Try:

...etc...
like '''|| search_string ||'''';

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Beilstwh]

Your cursor is more complex then it needs to be. Use

cursor cur_cols_to_srch is
select owner || '.' || table_name tbl_name, column_name
from dba_tab_columns
where owner = schema_name
and data_type in ('CHAR','VARCHAR2');

Bill
Oracle DBA/Developer
New York State, USA