Oracle APEX automatic column add/remove in region

Question

I have made a view that changes it's columns frequently with dynamic SQL. I use a pivot to make rows into columns. I display the view in an interactive grid. The SQL query that is executed:

select * from <DB>.<VIEWNAME>. 

On refresh it updates the ROWS in the grid, but not the COLUMNS. The select * does not take column changes into account, BUT if I alter the SQL query by adding a space ( or any other thing in the query) and then saving the page in the page designer, the columns sync up to the view. Does someone know a good solution to my problem? Where can I find the procedure that executes this refresh? If I know where it is I can possiby use it after the insertion of a column (or delete / update). Any tips? Warning, I am a total novice in oracle apex and sql developer.

Thanks in advance!

Answer 1

This is the wrong way to go about this. In Apex, and in Oracle in general, columns are determined when the query is parsed. If you change the underlying structure, your query has to be reparsed and only then do the columns change.

Think about it. If the first column in your result set was a DATE and you had your Apex column attributes set up to format and display that data, then your query changed to a NUMBER, its not clear what would happen.

What you probably want to do is create your region based on a function that returns a sql query as a VARCHAR2. (I think you can do this in 18.x; I'm still mostly using 5.2.) Your function gets parsed when the region is displayed. You can even use another function to return a colon-separated list of column headers if the names are dynamic.