ORACLE使用BLOB列搜索字符串的所有表 [英] ORACLE Search All Tables of a String with BLOB Column

问题描述

我正在尝试使用字符串搜索所有表，但是它似乎在BLOB列中不起作用.我从论坛上获得了此程序.它在搜索字符串中起作用，但在BLOB中不起作用. 你能帮我吗?

I'm trying to search using a String for all tables but it didn't seem to work in BLOB columns. I got this procedure from a forum. It's working in searching string but not in BLOB. Can you please help me please?

create or replace procedure find_string( p_str in varchar2 )
authid current_user
as
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) || '%' );

for x in (select * from user_tables )
loop
l_query := 'select ''' || x.table_name || ''', $$
                         from ' || x.table_name || '
                       where rownum = 1 and ( 1=0 ';
           l_case := 'case ';
           l_runquery := FALSE;
           for y in ( select *
                        from user_tab_columns
                       where table_name = x.table_name
                    )
           loop
               l_runquery := TRUE;
               l_query := l_query || ' or upper(' || y.column_name ||
                          ') like userenv(''client_info'') ';
              l_case := l_case || ' when upper(' || y.column_name ||
                         ') like userenv(''client_info'') then ''' ||
                         y.column_name || '''';
           end loop;
           if ( l_runquery )
           then
               l_case := l_case || ' else NULL end';
               l_query := replace( l_query, '$$', l_case ) || ')';
              begin
                   execute immediate l_query into l_tname, l_cname;
                   dbms_output.put_line
                   ( 'Found in ' || l_tname || '.' || l_cname );
               exception
                   when no_data_found then
                      /*select 0 into l_x from dual;*/
                      dbms_output.put( '.');
               end;
           end if;

      end loop;
   end;

非常感谢！

推荐答案

您无法使用常规SQL String操作在BLOB列中搜索字符串.您需要使用dbms_lob软件包来实现您的目标.

You cannot search for strings within a BLOB column using the normal SQL String operations. You would need to use the dbms_lob package to achieve your goal.

请参考此链接，以获取有关操作方法的示例. Google上也有很多示例可以为您提供进一步的指导.

Refer to this link for an example of how it is to be done. There are plenty of examples on Google too which can guide you further.

这篇关于ORACLE使用BLOB列搜索字符串的所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！