Ok, this is the first of (what I hope will be) a recurring feature on this blog: a post about one of the hidden gems of APEX. Little bits of functionality that casual users–and sometimes, even power users–aren't aware of, but which are really useful.

Today's installment is the “Query on Selected Table” functionality, which I only found out about because my manager caught me in a sloppy moment and showed me how to do things better.

I was adding a feature to the packaged applications which are a central part of the upcoming 4.2 release, and needed to add a report on a table to each of the applications. Each report had to refer to a different table, and I was working quickly, so I'd taken a shortcut: the report queries were simply “select * from

“. Quick to implement, and…bad for future maintenance. Oops.

Turns out that there's a really simple way to get a query for any table through APEX. From any page, click on the “Find” button in the upper-right corner (looks like a flashlight, right next to the help button). This opens up a new window which looks a lot like the one that opens when you click on the debug button when editing a page (because, well, it is; it's just a different tab). From here, click on the “Tables” tab, which brings up a report of the tables and views available to your APEX application. Click on a specific table (or view), and you get a report of the columns in that table, their data types, etc.; basically what you get in the SQL Workshop's Object Browser, but without the ability to make changes. But at the top of the page, under the tabs, is a select box with the following options:

• Tables and Views (this just takes you back to the table listing)
• Selected Table Columns (this is currently selected)
• Query on Selected Table

This last option is today's magic–it provides you with a simple, no-nonsense “select … from

” query that provides you with all the column names, all set for copying and pasting back into your report. Win!