Oracle PL/SQL函数执行报错ORA-01422，需仅返回首行结果

问题原因 #

当你执行的动态SQL返回多行结果时，EXECUTE IMMEDIATE ... INTO 语句要求精确返回一行数据，因此触发 ORA-01422: exact fetch returns more than requested number of rows 错误。

解决方法 #

在动态执行的SQL外层包裹一层子查询，通过ROWNUM = 1（兼容所有Oracle版本）或FETCH FIRST 1 ROW ONLY（Oracle 12c及以上版本）限制只获取首行数据。

修改后的完整函数 #

create or replace function DEF_ID(
in_checkid IN SQLTEXTDEFN.SQLID%type
) 
return varchar2
as
sql_stmt clob;
ret varchar2(254);
begin
   begin 
     execute immediate 'select SQLTEXT from SQLTEXTDEFN where sqlid=:1'
         into sql_stmt
         using in_checkid;
       begin
         -- 包裹原SQL，限制仅取首行
         execute immediate 'select * from (' || sql_stmt || ') where rownum = 1' into ret;
         EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    RETURN 'NO DATA FOUND';
                when others then
                    return substr(sqlerrm,1,200);
       end; 
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    RETURN 'NO CHECK FOUND';
                when others then
                    return substr(sqlerrm,1,200);
            END;
return ret;
end;
/

说明 #

• 使用ROWNUM = 1可以确保无论原SQL返回多少行，都只取第一行结果赋值给ret，避免触发ORA-01422错误。
• 如果你使用Oracle 12c或更高版本，也可以改用更直观的语法：

  execute immediate sql_stmt || ' fetch first 1 row only' into ret;
  

  这种写法不需要嵌套子查询，但仅支持Oracle 12c及以上版本。

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