Search code examples
plsqloracle11goracle-apex-5.1

Is there a way to display dynamic columns in Oracle apex

Long story short, I can't use pivot for this task due to the long elements that I need to include in the columns. Although I tried to create a Classic Report based on function in Oracle Apex. The query it's generated correctly but it's not working in the Classic Report.

Solution

  • A general hint first: Output your variable l_sql to your console using dbms_output.put_line or use some kind of debugging table where you can insert it into. Also be careful about the data type of that variable. If you need to expand the SQL you can reach a point where you need to use a CLOB variable instead of varchar2.

    You will need to supply table structures and test data if you like to have your problem analyzed completely, therefore I will at first give you some general explanations:

    enter image description here

    Use Generic Column Names is ok if you have a permanent, unchangable amount of columns. But if the order of your columns or even the amount can change, then this is a bad idea, as your page will show an error if your query results in more columns than Generic Column Count

    Option 1: Use column aliases in your query

    Enhance your PL/SQL Function Body returning SQL Query in a way that it outputs verbose display names, like this:

    return 'select 1 as "Your verbose column name", 2 as "Column #2", 3 as "Column #3" from dual';

    That looks like this:

    enter image description here

    It has the disadvantage that the column names also appear in this way in the designer and APEX will only update these column names if you re-validate the function. You will have a hard time to reference a column with the internal name of Your verbose column name in a process code or dynamic action.

    enter image description here

    However it still works, even if you change the column names without telling APEX, for example by externalizing the PL/SQL Function Body into a real function.

    Option 2: Use custom column headings

    A little bit hidden, but there is also the option of completely custom column headings. It is almost at the end of the attributes page of your report region.

    enter image description here

    Here you can also supply a function that returns your column names. Be careful that this function is not supposed to return an SQL query that itself returns column names, but instead return column names seperated by a colon.

    enter image description here

    With this method, it is easier to identify and reference your columns in the designer:

    enter image description here

    Option 3: Both of it

    Turn off Generic Column Names, let your query return column names that can be easily identified and referenced, and use the custom column headings function return verbose names for your users.

    My personal opinion

    Im using the 3rd option in a production application where people can change the amount and order of columns using shuttle items on the report page themselves. It took some time, but now it works like a charm, like some dynamic PIVOT without PIVOT.