Oracle APEX中Function Returning Error Text报错求助

错误场景 #

在APEX中配置Function Returning Error Text类型的验证，统计交互式网格记录数并返回提示时，出现以下错误：

ORA-06550: line 1, column 141: PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following: := . ( @ % ; The symbol "." was substituted for "NUMBER" to continue.

原验证代码如下：

l_count NUMBER := 0;

BEGIN

  SELECT COUNT(1)
      INTO l_count
      FROM  ugh
     WHERE ugh.pre = :PRE
       AND ugh.APP1 = :APP1
       AND ugh.APP2 = :APP2
       AND ugh.APP3 = :APP3
       AND ugh.FINL_APP = :FINL_APP;
 
IF l_count > 1 THEN
      IF END_DATE IS NULL THEN
         RETURN 'Error Message to be displayed.';
      ELSE
      RETURN NULL;
      END IF;
    ELSE
     RETURN NULL;
   END IF;
END;

问题分析 #

1. 缺少DECLARE块：PL/SQL中变量声明必须放在DECLARE关键字之后，原代码直接定义变量，违反语法规范，导致解析错误。
2. 页面项引用错误：END_DATE是APEX页面项，必须通过绑定变量:END_DATE引用，否则会被视为未定义的PL/SQL标识符。

修正后的代码 #

DECLARE
  l_count NUMBER := 0;
BEGIN
  SELECT COUNT(1)
    INTO l_count
    FROM ugh
   WHERE ugh.pre = :PRE
     AND ugh.APP1 = :APP1
     AND ugh.APP2 = :APP2
     AND ugh.APP3 = :APP3
     AND ugh.FINL_APP = :FINL_APP;

  IF l_count > 1 THEN
    IF :END_DATE IS NULL THEN
      RETURN 'Error Message to be displayed.';
    ELSE
      RETURN NULL;
    END IF;
  ELSE
    RETURN NULL;
  END IF;
END;

修改说明 #

• 添加DECLARE关键字，将变量l_count的声明放在DECLARE块内，符合PL/SQL语法要求。
• 将END_DATE修改为:END_DATE，正确引用APEX页面绑定变量。
• 保持原有业务逻辑不变：当符合条件的记录数大于1且END_DATE为空时返回错误提示，否则返回NULL表示验证通过。

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