Using dbms_sql.parse to parse APEX_ITEM.TEXT_FROM_LOV

Question

I get a

ORA-00923: FROM keyword not found where expected

and I asume its from trying to parse APEX_ITEM.TEXT_FROM_LOV.

Is they a way to parse APEX_ITEM.TEXT_FROM_LOV using dbms_sql.parse? Here is part of the code that would generate an sql query ready to be parsed:

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;

Or another way to grab the list of values from APEX_APPLICATION_LOVS so as to decode the columns from APEX_APPLICATION_PAGE_RPT_COLS? Would I have to decode the columns after I've parsed the sql for the report?

So the generated sql would look like this stored as a clob:

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

which would then be passed to dbms_sql.parse, so you would get a result like this:

EMP_NO
------
James
Jane
John
Sam

instead of

EMP_NO
------
1
2
3
4

But I figured that I can only decode the values after the sql has been parsed?

So by first grabbing the region_name from APEX_APPLICATION_PAGE_RPT_COLS and then using APEX_APPLICATION_LOVS like this:

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;

Answer 1

So, my assumption was right, I can only really use APEX_ITEM.TEXT_FROM_LOV after I have parsed the the SQL:

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;