SELECT * breaks when adding columns in Oracle Application Express (ApEx) 3.0

Question

When I define a report region's SQL as SELECT * FROM some_table, all is fine until new columns are added to some_table -- then it breaks with a "ORAxxx No data found" error. It is easy to remediate, as it's enough to Apply Changes on the region again, even without making any changes. However, it does not make for a robust application.

Is there some combination of parameters that would allow SELECT * that does not break with new columns? It would be enough to apply any default formatting or heading to the new columns.

I'm aware I could construct the column list from data dictionary and then concatenate everything into the SELECT statement to evaluate, but this seems rather inelegant.

Answer 1

Normally is not recommended to use SELECT * queries because:

• Returns all the columns, then the optimizer have less play to do.
• It makes less robust the applications because adding new columns changes the result of the query giving unexpected results. Without SELECT *, I mean giving exactly the columns you need, adding new columns does not matter to the application.

Anyway, remember that creating a SELECT * for a view, oracle create the view replacing the * for all the columns, may be APPEX is making the same thing.

Answer 2

Currently your region source is (I presume) set to "Use Query-Specific Column Names and Validate Query". This means that a report column is defined explicitly for each column in the query, and the SQL is expected to be static.

If you change the region source to "Use Generic Column Names (parse query at runtime only)", then it will still work after a new column is added, with the column title defaulting to the column name.

There is another property "Maximum number of generic report columns" that defaults to 60 and must be set to a value big enough to accommodate any future columns added to the table.