Oracle APEX中PL/SQL函数引用页面项生成查询的问题

在Oracle APEX中创建列表视图区域时，通过PL/SQL函数动态生成查询语句，硬编码参数值时可正常运行，但使用页面项或SELECT INTO赋值时出现报错，以下是具体问题及解决方案：

可正常运行的基础代码 #

DECLARE
    list_query varchar2(4000);
    table_name varchar2(400);
    part_id number(6,1);
    table_prefix varchar2(400);
    table_suffix varchar2(400);
    
BEGIN
    table_suffix := 'placeholder value';
    table_prefix := 'placeholder value';
    table_name := table_prefix || table_suffix;
    part_id := 1001;
    list_query := 'select
                      CRITERIA_ID,
                      CRITERIA_NAME,
                      PART_ID,
                      PART_NAME
                   from
                      ' || table_name || '
                   where 
                      PART_ID = ' || part_id || '';
    return(list_query);
END;

问题1：页面项赋值table_suffix时报ORA-00942（表或视图不存在） #

报错场景代码：

table_suffix := :P21_PASSED_TABLE;
table_prefix := 'CABLES';
table_name := table_prefix || table_suffix;
part_id := 1001;

原因：

• 页面项:P21_PASSED_TABLE可能包含多余空格、大小写不匹配（Oracle表名默认大写，若页面项值为小写会导致表名不匹配）
• 拼接后的表名实际不存在
• APEX执行用户对目标表无访问权限

解决方案：

1. 去除页面项值的前后空格并统一转为大写：

table_suffix := UPPER(TRIM(:P21_PASSED_TABLE));

2. 增加表名存在性校验，避免非法表名：

DECLARE
    v_exists NUMBER;
BEGIN
    SELECT 1 INTO v_exists
    FROM ALL_TABLES
    WHERE TABLE_NAME = UPPER(table_prefix || table_suffix);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, '目标表不存在');
END;

3. 确保APEX执行用户拥有目标表的SELECT权限

问题2：页面项赋值part_id时报ORA-00936（缺少表达式） #

报错场景代码：

table_suffix := '_YES';
table_prefix := 'CABLES';
table_name := table_prefix || table_suffix;
part_id := :P21_PASSED_PART;

原因：

• 页面项:P21_PASSED_PART为空，导致SQL拼接后PART_ID = 后面无内容
• 页面项是字符串类型，直接赋值给数值变量part_id时转换失败，导致拼接出无效SQL
• 直接拼接数值存在SQL注入风险，且APEX解析时可能因格式问题报错

解决方案：
使用绑定变量代替直接拼接，避免SQL注入和格式问题：

list_query := 'select
                  CRITERIA_ID,
                  CRITERIA_NAME,
                  PART_ID,
                  PART_NAME
               from
                  ' || table_name || '
               where 
                  PART_ID = :P21_PASSED_PART';

注：无需将页面项赋值给part_id变量，直接在动态SQL中使用绑定变量，APEX会自动处理参数传递和类型转换

问题3：SELECT INTO赋值table_prefix时报ORA-01403（无数据找到） #

报错场景代码：

table_suffix := '_YES';
SELECT TABLE_NAME into table_prefix FROM ASSEMBLIES_TABLE WHERE ASSEMBLY_ID = :P21_PASSED_ASSEMBLY;
table_name := table_prefix || table_suffix;
part_id := 1001;

原因：

• 页面项:P21_PASSED_ASSEMBLY的值在ASSEMBLIES_TABLE中无匹配的ASSEMBLY_ID
• 页面项类型与ASSEMBLY_ID类型不匹配（比如页面项是字符串，ASSEMBLY_ID是数值，隐式转换后无匹配）

解决方案：

1. 验证页面项:P21_PASSED_ASSEMBLY的有效性，确保其值存在于ASSEMBLIES_TABLE中
2. 处理NO_DATA_FOUND异常，避免程序中断：

BEGIN
    SELECT TABLE_NAME into table_prefix 
    FROM ASSEMBLIES_TABLE 
    WHERE ASSEMBLY_ID = TO_NUMBER(:P21_PASSED_ASSEMBLY); -- 显式转换类型
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20002, '未找到对应的表前缀');
END;

3. 检查页面项:P21_PASSED_ASSEMBLY的来源页面，确保传递的值正确

整合后的完整代码 #

DECLARE
    list_query varchar2(4000);
    table_name varchar2(400);
    table_prefix varchar2(400);
    table_suffix varchar2(400);
    v_exists NUMBER;
BEGIN
    -- 处理表后缀（来自页面项）
    table_suffix := UPPER(TRIM(:P21_PASSED_TABLE));
    -- 从表中获取表前缀
    BEGIN
        SELECT TABLE_NAME into table_prefix 
        FROM ASSEMBLIES_TABLE 
        WHERE ASSEMBLY_ID = TO_NUMBER(:P21_PASSED_ASSEMBLY);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20002, '未找到对应的表前缀');
    END;
    -- 拼接并验证表名
    table_name := table_prefix || table_suffix;
    BEGIN
        SELECT 1 INTO v_exists
        FROM ALL_TABLES
        WHERE TABLE_NAME = UPPER(table_name);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20001, '目标表 ' || table_name || ' 不存在');
    END;
    -- 生成动态查询，使用绑定变量
    list_query := 'select
                      CRITERIA_ID,
                      CRITERIA_NAME,
                      PART_ID,
                      PART_NAME
                   from
                      ' || table_name || '
                   where 
                      PART_ID = :P21_PASSED_PART';
    return(list_query);
END;

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