如何排查表中特殊字符？动态SQL报ORA-01403错误的解决方法

问题场景 #

使用动态SQL遍历指定表的所有字段，查找包含非字母、数字、点号的特殊字符记录，执行时触发ORA-01403错误。

原代码 #

declare 
    type e_list is record (v_col varchar2(100),
                           v_num number);
    r_rec e_list;
    type t_list_rec  is record
                              (table_name all_tab_columns.table_name%type,
                              column_name all_tab_columns.COLUMN_NAME%type);
    type t_list is table of t_list_rec index by PLS_INTEGER;
    v_array t_list;
begin
    select /*+ parallel(14) */ table_name,column_name bulk collect into v_array 
    from all_tab_columns 
    where table_name=upper('fms_user_address_book') and OWNER='FMS_ADMIN';
    
    for i in 1..v_array.count() loop
        EXECUTE IMMEDIATE
              'select '||v_array(i).column_name||',count(*)  from '||v_array(i).table_name||' where not regexp_like ('||v_array(i).column_name||',''[A-za-z0-9.]'')
        group by '||v_array(i).column_name||''
              into r_rec;
              
        if r_rec.v_num<>0 then
            dbms_output.put_line(v_array(i).table_name||' -- '||v_array(i).column_name||'------------>>>>'||r_rec.v_col||' '||r_rec.v_num);
        end if;
    end loop;
end;  

错误信息 #

ORA-01403: no data found
ORA-06512: at line 13
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.

原因分析 #

ORA-01403错误的核心触发点：当某个字段没有任何包含特殊字符的记录时，动态执行的SELECT ... GROUP BY语句不会返回任何行，但INTO r_rec要求查询必须返回恰好一行数据，此时就会抛出"无数据找到"的异常。

解决方案 #

在动态SQL执行逻辑外包裹局部代码块，捕获NO_DATA_FOUND异常——当出现该异常时，说明当前字段无特殊字符，直接跳过后续判断和输出即可。

修改后的代码 #

declare 
    type e_list is record (v_col varchar2(100),
                           v_num number);
    r_rec e_list;
    type t_list_rec  is record
                              (table_name all_tab_columns.table_name%type,
                              column_name all_tab_columns.COLUMN_NAME%type);
    type t_list is table of t_list_rec index by PLS_INTEGER;
    v_array t_list;
begin
    select /*+ parallel(14) */ table_name,column_name bulk collect into v_array 
    from all_tab_columns 
    where table_name=upper('fms_user_address_book') and OWNER='FMS_ADMIN';
    
    for i in 1..v_array.count() loop
        begin -- 新增局部块用于捕获异常
            EXECUTE IMMEDIATE
                  'select '||v_array(i).column_name||',count(*)  from '||v_array(i).table_name||' where not regexp_like ('||v_array(i).column_name||',''[A-za-z0-9.]'')
            group by '||v_array(i).column_name||''
                  into r_rec;
                  
            if r_rec.v_num<>0 then
                dbms_output.put_line(v_array(i).table_name||' -- '||v_array(i).column_name||'------------>>>>'||r_rec.v_col||' '||r_rec.v_num);
            end if;
        exception
            when NO_DATA_FOUND then
                -- 无特殊字符，跳过处理
                null;
        end; -- 结束局部块
    end loop;
end;  

补充说明 #

如果需要明确输出无特殊字符的字段信息，可以在NO_DATA_FOUND异常处理块中添加如下语句：

dbms_output.put_line(v_array(i).table_name||' -- '||v_array(i).column_name||'------------>>>> 无特殊字符');

内容的提问来源于stack exchange，提问作者Aman