Oracle search string in database

In some cases, fortunately not too many, I had to search for a string (usually something related to configuration) in a schema of the database.

Gets complicated when there is a huge amount of tables and also no way to contact the developer, is as hell.

Well good for these cases I want to share a PL / SQL script homegrown (as almost everyone who put in the blog), which facilitates the issue fairly:

Source   
DECLARE
sql_str VARCHAR2(1000);
sql_del VARCHAR2(1000);
string_to_search VARCHAR(200);
total_val number;
cursor c1 IS SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM dba_tab_columns WHERE owner='WL_VAPMGMT' AND DATA_TYPE IN ('CHAR','VARCHAR2');
BEGIN
string_to_search:='ceac7d09275562c6126a29405510e1ca';
FOR fila IN c1
LOOP
sql_str := 'SELECT COUNT(*) FROM '||fila.OWNER||'.'||fila.TABLE_NAME||' where '||fila.COLUMN_NAME||' like ''%'|| string_to_search||'%''';
sql_del := 'DELETE FROM '||fila.OWNER||'.'||fila.TABLE_NAME||' where '||fila.COLUMN_NAME||' like '''||string_to_search||'''';
EXECUTE IMMEDIATE sql_str INTO total_val;
IF total_val>0 then
dbms_output.put_line(sql_str);
dbms_output.put_line(fila.OWNER||'.'||fila.TABLE_NAME||'.'||fila.COLUMN_NAME||' Hits '||total_val);
dbms_output.put_line(sql_del);
dbms_output.put_line('---');
end IF;
END LOOP;
END;

The PL/SQL search within all tables WL_VAPMGMT scheme (which should be replaced by the one that comes with the case), the string assigned to string_to_search variable.

Shows that table and column coincidence has occurred, how many and what would be the DML to eliminate these matches.

I hope you find it useful …

Leave a Reply