我得到一个

ORA-00923：在预期的位置未找到FROM关键字

我不想解析APEX_ITEM.TEXT_FROM_LOV。
它们是使用dbms_sql.parse解析APEX_ITEM.TEXT_FROM_LOV的一种方法吗？下面是生成准备解析的sql查询的部分代码：

DECLARE
    report_columns VARCHAR2(32767);
    column_formating VARCHAR(225) := CHR(10)||CHR(9); --next line and tab
BEGIN
    SELECT LISTAGG(column_alias || column_formating, ', ') 
            WITHIN GROUP(ORDER BY display_sequence)
      INTO report_columns
      FROM (SELECT CASE
                     WHEN REGEXP_COUNT(column_alias, CHR(32), 1, 'c') > 0 AND
                          column_alias NOT LIKE '%APEX_ITEM.TEXT_FROM_LOV%' THEN
                      '"' || column_alias || '"'
                     ELSE
                      column_alias
                   END column_alias,
                   display_sequence
              FROM (SELECT CASE
                             WHEN named_list_of_values IS NOT NULL THEN
                              'APEX_ITEM.TEXT_FROM_LOV( p_value => ' ||
                              column_alias || ', p_lov => ''' ||
                              named_list_of_values || ''') ' || column_alias
                             ELSE
                              column_alias
                           END column_alias,
                           display_sequence
                      FROM apex_application_page_rpt_cols
                     WHERE application_id = p_applicaion_no
                       AND page_id = p_page_no
                       AND region_id = p_region_id
                       AND include_in_export = 'Yes'
                     ORDER BY display_sequence));    

END;

字符串
或者另一种从APEX_APPLICATION_LOVS获取值列表以便从APEX_APPLICATION_PAGE_RPT_COLS解码列的方法？我必须在解析完报告的sql之后解码列吗？
因此，生成的sql看起来像这样存储为clob：

'SELECT 
APEX_ITEM.TEXT_FROM_LOV(EMP_NO,'''EMP_NO_ENAME_LOV''') EMP_NO 
FROM EMP'

型
然后它会被传递给dbms_sql.parse，所以你会得到这样的结果：

EMP_NO
------
James
Jane
John
Sam

型
而不是

EMP_NO
------
1
2
3
4

型
但是我想我只能在SQL被解析之后才能解码这些值。
因此，首先从APEX_APPLICATION_PAGE_RPT_COLS获取region_name，然后像这样使用APEX_APPLICATION_LOVS：

DECLARE
TYPE lov_array IS TABLE OF VARCHAR2(255);
l_lov lov_array := lov_array();
      
      cursor c_region_lov is
        select
        named_list_of_values
        from apex_application_page_rpt_cols
        where named_list_of_values is not null
        and include_in_export = 'Yes'
        and region_name = p_region;

  BEGIN
  
        for lov_name in c_region_lov loop
            l_lov.extend;
            l_lov(l_lov.count) := lov_name.named_list_of_values;            
        end loop;
        
        l_cursor_id := dbms_sql.open_cursor;
        dbms_sql.parse(l_cursor_id, p_query, dbms_sql.native);
        
        l_execute := DBMS_SQL.EXECUTE(l_cursor_id);
        
        loop 
            if dbms_sql.fetch_rows(l_cursor_id) = 0 then exit; 
            end if;  

            for i in 1..l_col_cnt loop
                l_column_values.extend;
                dbms_sql.column_value(l_cursor_id, i, l_values);
                    if l_lov.count != 0 then
                        for r_lov in (select list_of_values_name from APEX_APPLICATION_LOVS where application_id = p_id and list_of_values_name member of l_lov) loop
                            l_column_values(l_column_values.count) :=  APEX_ITEM.TEXT_FROM_LOV (l_values, r_lov.list_of_values_name, l_values);
                        end loop;
                    else
                        l_column_values(l_column_values.count) :=  l_values;
                    end if;
            end loop;
                        
        end loop;
        
        dbms_sql.close_cursor(l_cursor_id);
    END;

型