使用dbms_sql.parse解析APEX_ITEM.TEXT_FROM_LOV遇ORA-00923错误求解决

一、dbms_sql.parse解析含APEX_ITEM.TEXT_FROM_LOV的SQL报错解决 #

使用dbms_sql.parse解析包含APEX_ITEM.TEXT_FROM_LOV的SQL时触发ORA-00923错误，本质是dbms_sql属于原生PL/SQL工具，无法识别APEX专属内置函数，导致SQL语法解析失败。

可行解决方法有两种：

• 替换APEX函数为原生SQL逻辑：APEX_ITEM.TEXT_FROM_LOV的作用是根据LOV返回值匹配显示文本，可直接替换为关联APEX_APPLICATION_LOVS和APEX_APPLICATION_LOV_ENTRIES的子查询。比如原SQL中的APEX_ITEM.TEXT_FROM_LOV(emp_status, 'EMP_STATUS_LOV')可替换为：

  (SELECT display_value 
   FROM apex_application_lovs l
   JOIN apex_application_lov_entries e ON e.lov_id = l.lov_id
   WHERE l.application_id = :APP_ID
     AND l.lov_name = 'EMP_STATUS_LOV'
     AND e.return_value = emp_status)
  

• 使用APEX专属执行工具：如果在APEX应用环境内操作，改用APEX_EXEC包的PREPARE_QUERY方法替代dbms_sql.parse，该工具能识别APEX内置函数，避免解析报错。

待解析SQL的生成代码示例 #

DECLARE
    l_sql VARCHAR2(4000);
BEGIN
    l_sql := 'SELECT emp_id, emp_name, ' ||
             'APEX_ITEM.TEXT_FROM_LOV(emp_status, ''EMP_STATUS_LOV'') emp_status_text ' ||
             'FROM employees';
    -- 此处调用dbms_sql.parse会触发ORA-00923错误
END;
/

二、通过APEX_APPLICATION_LOVS解码APEX_APPLICATION_PAGE_RPT_COLS列的可行性 #

完全可以直接通过APEX_APPLICATION_LOVS和APEX_APPLICATION_LOV_ENTRIES视图解码APEX_APPLICATION_PAGE_RPT_COLS中的列，不需要等到SQL解析完成后再处理。

APEX_APPLICATION_PAGE_RPT_COLS会存储报表列关联的LOV名称，直接通过这个LOV名称关联到APEX_APPLICATION_LOVS获取LOV基本信息，再关联APEX_APPLICATION_LOV_ENTRIES拿到返回值与显示值的映射关系，就能提前完成列值的解码。

三、解析后解码的代码示例 #

如果业务场景确实需要在SQL解析执行后再进行列解码，可参考以下代码：

DECLARE
    l_cursor NUMBER;
    l_emp_id NUMBER;
    l_emp_name VARCHAR2(100);
    l_emp_status VARCHAR2(20);
    l_status_text VARCHAR2(100);
BEGIN
    -- 执行不含APEX函数的原生SQL，获取原始数据
    l_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(l_cursor, 'SELECT emp_id, emp_name, emp_status FROM employees', dbms_sql.native);
    dbms_sql.define_column(l_cursor, 1, l_emp_id);
    dbms_sql.define_column(l_cursor, 2, l_emp_name, 100);
    dbms_sql.define_column(l_cursor, 3, l_emp_status, 20);
    
    IF dbms_sql.execute(l_cursor) > 0 THEN
        WHILE dbms_sql.fetch_rows(l_cursor) > 0 LOOP
            dbms_sql.column_value(l_cursor, 1, l_emp_id);
            dbms_sql.column_value(l_cursor, 2, l_emp_name);
            dbms_sql.column_value(l_cursor, 3, l_emp_status);
            
            -- 通过APEX视图解码状态值
            BEGIN
                SELECT display_value
                INTO l_status_text
                FROM apex_application_lov_entries e
                JOIN apex_application_lovs l ON e.lov_id = l.lov_id
                WHERE l.application_id = :APP_ID
                  AND l.lov_name = 'EMP_STATUS_LOV'
                  AND e.return_value = l_emp_status;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    l_status_text := '未知状态';
            END;
              
            DBMS_OUTPUT.PUT_LINE('员工ID: ' || l_emp_id || ', 姓名: ' || l_emp_name || ', 状态: ' || l_status_text);
        END LOOP;
    END IF;
    dbms_sql.close_cursor(l_cursor);
END;
/

内容的提问来源于stack exchange，提问作者another apex user