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.
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
So what happens when a column is added on the remote database to our table, for example the column
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.
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
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.