🔄 Dynamic Reports in Oracle APEX: Display Table Data Based on Select List Selection

1. Introduction

This article explains how to Fetch & Display Table Data Dynamically from Dropdown in Oracle APEX. The solution is implemented using a combination of SQL/PL/SQL and Oracle APEX.

2. Tools and Technologies

To achieve the desired functionality, the following technologies are used:

• SQL / PL/SQL
• Oracle APEX

3. Business Requirement

The organization needs a flexible reporting feature in Oracle APEX where users can select any table from the application database and instantly view its data in report format — without creating individual reports for each table.

4. Implementation Steps

Step 1: Create Package as below code

Package Specification.

create or replace PACKAGE "REPORT" as

function get_query( p_table_name    in varchar2,
                    p_schema_name   in  varchar2 default sys_context('USERENV', 'CURRENT_USER')) return clob;

function get_headers(p_table_name   in varchar2,
                     p_pretty_yn    in varchar2 default 'Y',
                     p_schema_name  in  varchar2 default sys_context('USERENV', 'CURRENT_USER')) return varchar2;

function show_column(   p_table_name        in varchar2,
                        p_column_number     in number,
                        p_schema_name   in  varchar2 default sys_context('USERENV', 'CURRENT_USER')) return boolean;       

procedure set_IR_columns_headers(   p_table_name        in varchar2,
                                    p_base_item_name    in varchar2,
                                    p_pretty_yn         in varchar2 default 'Y',
                                    p_schema_name   in  varchar2 default sys_context('USERENV', 'CURRENT_USER')) ;  

end report;

Package Body.

create or replace PACKAGE BODY "REPORT" is

function get_query( p_table_name    in varchar2,
                    p_schema_name   in  varchar2 default sys_context('USERENV', 'CURRENT_USER')) return clob is

l_query         clob;
l_table_name    varchar2(4000) := nvl(p_table_name, 'DUAL');

begin

    select  listagg(case 
                when column_name is null
                then case 
                    when ct_val = 'NUMBER' then 'to_number(null)'
                    when ct_val = 'DATE' then 'to_date(null)'
                    else 'null'
                    end
                else sys.dbms_assert.enquote_name(column_name)
                end
            || ' ' ||
                    column_alias
            , ', ' || chr(13)  
                ) col_names
        into l_query
        from ait67_one_report_macro.user_tab_col_macro(p_table_name => l_table_name, p_schema_name => p_schema_name)
        order by alias_rn ;


    l_query := 'select ' || l_query || chr(13) || ' from ' || sys.dbms_assert.enquote_name(l_table_name, false);

    return l_query;

end get_query;


function get_headers(p_table_name   in varchar2,
                     p_pretty_yn    in varchar2 default 'Y',
                     p_schema_name  in  varchar2 default sys_context('USERENV', 'CURRENT_USER')) return varchar2 is

l_headers       varchar2(4000);
l_table_name    varchar2(4000) := nvl(p_table_name, 'DUAL');

begin

    select listagg(
            nvl(
                case 
                    when p_pretty_yn = 'Y' then initcap(replace(nvl(column_name,column_alias),'_',' '))
                    else nvl(column_name,column_alias)
                    end    
                , column_alias),':') col_headers
    into l_headers
    from ait67_one_report_macro.user_tab_col_macro(p_table_name => l_table_name, p_schema_name => p_schema_name) 
    order by alias_rn;

    return l_headers;

end get_headers;

function show_column(   p_table_name        in varchar2,
                        p_column_number     in number,
                        p_schema_name       in  varchar2 default sys_context('USERENV', 'CURRENT_USER')) return boolean is

l_count         number;                        
l_table_name    varchar2(4000) := nvl(p_table_name, 'DUAL');

begin

    if l_table_name = 'DUAL' then
        return true;
    end if;

    select count(*)
      into l_count
      from ait67_one_report_macro.user_tab_col_macro(p_table_name => l_table_name, p_schema_name => p_schema_name) 
      where alias_rn = p_column_number
        and column_name is not null
        and rownum = 1;

    return l_count > 0;     

end show_column;

--
--
procedure set_IR_columns_headers(   p_table_name        in varchar2,
                                    p_base_item_name    in varchar2,
                                    p_pretty_yn         in varchar2 default 'Y',
                                    p_schema_name       in  varchar2 default sys_context('USERENV', 'CURRENT_USER')) is

l_header        varchar2(4000);
l_table_name    varchar2(4000) := nvl(p_table_name, 'DUAL');
begin
    for i in (select alias_rn, column_name, column_alias
              from ait67_one_report_macro.user_tab_col_macro(p_table_name => l_table_name, p_schema_name => p_schema_name)
              order by alias_rn
              ) loop

        l_header := case 
                        when p_pretty_yn = 'Y' then initcap(replace(nvl(i.column_name, i.column_alias),'_',' '))
                        else nvl(i.column_name, i.column_alias)
                        end;
                        
        apex_util.set_session_state(p_base_item_name || i.alias_rn, l_header);
    end loop;

end set_IR_columns_headers;

end report;
/

Step 2: Create Select list Page Item with name “P1_TABLE_NAME”

List of Value Source Query

select distinct table_name d, table_name r
  from user_tab_cols
order by table_name

Step 3: Now create Classic Report

In classic report source select “Function body returning SQL query” and provide below code.

return report.get_query(p_table_name => :P1_TABLE_NAME);

After that go to the Attribute of Classic report and find “Heading” section. After that in Type => PL/SQL Function body and provide below code.

return report.get_headers(p_table_name => :P1_TABLE_NAME);

Step 4: Now create Dynamic Action on “P1_TABLE_NAME”

In the Dynamic Action, add True Action of type Refresh.

In Affected Element

• Selection Type=> Region
• Region Name=> Report

Step 5: Now go to your report column and provide server side code.

• Type=> Expression
• Language=> PLSQL

report.show_column(:P1_TABLE_NAME,1) --column 1 server side
report.show_column(:P1_TABLE_NAME,2) -- column 2 server side
report.show_column(:P1_TABLE_NAME,3) -- column 3 server side

Repeat this same for each column. Only change the value in series like 1,2,3…60

5. Output

Following the above steps, Dynamic Reports in Oracle APEX: Display Table Data Based on Select List Selection. This ensures a smoother and more user-friendly experience.

Thanks for reading! We hope this guide helped you implement Dynamic Reports in Oracle APEX: Display Table Data Based on Select List Selection.