stored procedures - ORACLE Search All Tables of a String with BLOB Column -

stored procedures - ORACLE Search All Tables of a String with BLOB Column -

i'm trying search using string tables didn't seem work in blob columns. got procedure forum. it's working in searching string not in blob. can please help me please?

create or replace procedure find_string( p_str in varchar2 ) authid current_user l_query long; l_case long; l_runquery boolean; l_tname varchar2(30); l_cname varchar2(30); l_x number; begin dbms_application_info.set_client_info( '%' || upper(p_str) || '%' ); x in (select * user_tables ) loop l_query := 'select ''' || x.table_name || ''', $$ ' || x.table_name || ' rownum = 1 , ( 1=0 '; l_case := 'case '; l_runquery := false; y in ( select * user_tab_columns table_name = x.table_name ) loop l_runquery := true; l_query := l_query || ' or upper(' || y.column_name || ') userenv(''client_info'') '; l_case := l_case || ' when upper(' || y.column_name || ') userenv(''client_info'') ''' || y.column_name || ''''; end loop; if ( l_runquery ) l_case := l_case || ' else null end'; l_query := replace( l_query, '$$', l_case ) || ')'; begin execute immediate l_query l_tname, l_cname; dbms_output.put_line ( 'found in ' || l_tname || '.' || l_cname ); exception when no_data_found /*select 0 l_x dual;*/ dbms_output.put( '.'); end; end if; end loop; end;

thank much!

you cannot search strings within blob column using normal sql string operations. need utilize dbms_lob bundle accomplish goal.

refer this link illustration of how done. there plenty of examples on google can guide further.

oracle stored-procedures blob