Many developers don’t like select *. I’m a big fan of it, but there are some edge cases where one needs to be careful. I don’t want to discuss pros and cons here in this thread. This is just to point out a few areas where useing select * gives not the advantage that one could expect.
I like to program select * in multiple scenarios. Usually to express, that I want to access (almost) every column from that table or view. I’m too lazy to write down every single column name.
A typical APEX example would be to edit a table via an Interactive Grid.
Scenario setup
Imagine a remote database with a table MYTAB. We access this remote database via a database link from a local database with APEX installed. In the SQL workshop we have a view that was originally created as
create view myTab_remote as
select * from mytab@dblinkname;
That advantage of that view is, that we create a layer where the name of the DB link is used. Using this layer we don’t need to know the DB link name inside our APEX application. Which makes it easier to switch or change the link.
Also the APEX framework is now able to use the oracle data dictionary for that view. Newer APEX versions have improved capabilities to work with remote tables. But here I’m talking about version 5.1.
Our Interactive Grid uses a fairly simple query.
select * from myTab_remote
where parent_id = :P1_MASTER_ID
Modifications
So what happens when a column is added on the remote database to our table, for example the column price_unit.
alter table myTab
add price_unit varchar2(5);
The APEX application is still working, no issue happend there. It does not automatically has the new column.
In general this is good. We have a robust appliaction that does not break, simply because a new column was added (it would break if a column is dropped however).
Certainly the column was added with some intention in mind. We also want to show and edit this new column in our Interactive Grid.
How to add this new column to our application?
First step is to update the view. One might think this is not necessary, because we created the view using select *. Unfortunatly that is not how Oracle works, especially not via a database link.
When investigating the view via the SQL tab in the SQL workshop, we find that the DDL is different than what one would think.
CREATE OR REPLACE FORCE VIEW "MYTAB_REMOTE" ("ID", "PARENT_ID", "COL_A", "COL_B", "PRICE") AS
select "ID", "PARENT_ID", "COL_A", "COL_B", "PRICE" from MYTAB@DBLINKNAME
/
The column list was expanded into the select statement and also captured as the column list name of the view itself. The new column price_unit is missing.
To add the new column we could again simply create the view. A normal recompile is not enough. Btw. this also happens with views over local tables.
create or replace view myTab_remote as
select * from mytab@dblinkname;
Hint: never use the FORCE keyword for development work. It may hide important error messages.
After that we can select from the view and will see the new column. However it is still missing from the Interactive Grid.
The second step is to push APEX into reevaluating the column list for this view.
Simply validating the SQL query will not be enough. I found the following sequence of steps do work.
First add an alias and a new dummy column to the query.
select m.*, 'x' new_dummy_col
from myTab_remote m
where m.parent_id = :P1_MASTER_ID
Validate it and press OK. This results in two new columns added to the grid column list. Then enter the SQL query again and remove the dummy column.
select m.*
from myTab_remote m
where m.parent_id = :P1_MASTER_ID
Result is we now have the new column in our list.
The third and last step is to add this column to our default reports.
The new column is at the very end of the column list. On an IG it is shown, on an IR it is not shown by default. In any case we want to move this new column via the Actions > Columns menu to the correct position. And then save the public report Actions > Save Report. This should be done for each public report in that Grid.
Conclusion
Using select * in a APEX Interactive Grid scenario did not lower the maintenance needed when adding new columns to the base table. The second step even might be slightly confusing for many developers. So it might be a good idea to use a complete column list there instead of select *.
Site note: Using select * in other scenarios like inside PLSQL indeed does lower the maintenance when done right compared to an explicit full column list.
svenweller 