TL/DR:
- It may be incomplete—but doesn't say so in SQL Dev 23.1
- It may contain irrelevant entries
- The list of matching entries could be too broad (in my opinion though)
- The highlighting color could be uneasy to distinguish
- And I don't even know how to use multi-selection :-(
1/ Regarding completeness, and the list of entries
I'm working with DBA rights, and I want to write a query joining v$session and v$px_session; so I open a SQL worksheet, and type in the following:
select
from
v$session ses,
v$px_session pxs
Then I put the cursor back between the select and the from keywords, and I press Ctrl+Space in order to have the auto-completion menu pop up.
In SQL Developer 23.1, the result is as follows:
The above screenshot shows the upper part of the menu; columns from v$session are shown (with the “ses” alias from my query), in alphabetical order, not in order of column_id. The bottom of the menu is as follows:
Columns from v$px_session are shown (prefixed with the “pxs” alias; again, in alphabetical order) after columns from v$session. And there are 2 extra, unrelated entries with names “foo” and “bar”, with the corresponding icon suggesting that they are PL/SQL objects.
At this stage, several issues are visible:
a) The list of columns from v$session is incomplete.
I know v$session enough to understand that there are far more columns in it than appear in the above menu; yet there is absolutely no indication that the list is not complete.
In SQL Dev 21.4, if the list of columns is partial, it is clearly indicated by a final “…” entry in the menu, as shown in the following screenshot:
And clicking on that “…” entry causes SQL Developer to fetch the remaining columns. Nothing like this seems to happen anymore in SQL Dev 23.1.
b) The list may contain irrelevant objects
Where do the “foo” and “bar” entries come from? A quick verification shows that these are PL/SQL objects from my own schema:
I have pressed Ctrl+Space without any indication of the identifier that I was looking for, so SQL Dev has populated the menu with columns from the 2 views in the FROM clause (v$session, v$px_session), and then it considered other objects that I might want to use in my query.
It's fair to consider that the “FOO” PL/SQL function could be of some use; on the other hand, I fail to see how I could use “BAR”, a plain PL/SQL procedure, in the context of a SELECT query.
2/ SQL queries ran by the auto-completion popup
Reviewing the SQL queries ran by SQL Dev when populating the auto-completion menu is possible thanks to the Statements tab in the View -> Log panel.
Below are the 6 queries run by SQL Dev 23.1 when populating the menu (in reverse chronological order: the most recent query is shown at the top, the oldest at the bottom).
From this we see that there are 2 queries for resolving the synonym names (v$session, v$px_session), 2 queries for fetching columns of corresponding SYS views, and 2 more queries for finding the extra objects to be listed.
It's worth reviewing the query used to fetch the column names:
select column_name from all_tab_columns where owner = :1 and table_name = :2
and column_name like :2 and rownum < 50 order by column_name;
(I'm a bit surprised that the all_ view is used here: I have DBA rights, so the corresponding dba_ view could have been used, but that's not the point. Further, there are 2 occurrences of the :2 bind variable, but this is bind by position, and there are actually 3 bind values; again, not the main point.)
What's wrong with this query?
Well… rownum < 50 in the same query block as order by to begin with: this guarantees that the SORT ORDER BY will be performed only after 49 rows have been fetched (in unspecified order); so we'll have at most 49 rows, sorted by ascending column_name, which are not necessarily the lowest 49 column names in the collating sequence.
Additionally, there is absolutely nothing in that query, nor in any other query here, that may indicate that the v$session view may contain more than 49 columns; hence the disappearance of the “…” entry which is shown by SQL Dev 21.4.
The 2 other queries are as follows:
select object_name from sys.all_procedures where owner = user and object_name like :1
and object_type in ('PROCEDURE','FUNCTION')
and rownum < 50 order by procedure_name;
with bind values: 1="%"
And:
select /*distinct*/ object_name, object_type from all_objects
where object_type in ('PACKAGE','TYPE') and owner=user
and object_name like :1 ESCAPE '\' and rownum < 50
union all
select username||'.', 'USER' from all_users where username like :2 and rownum <= 20
order by object_name;
with bind values: 1="%", 2="%"
These 2 queries add the extra names which the user may, or may not, find useful. It's a bit a matter of opinion here whether this is useful or not, but I fear this might make the list very broad at times. (That said, the owner=user condition will not apply in case the schema owning the objects of interest is a distinct schema, as user returns the session user.)
And, including procedures would make sense in the context of PL/SQL code, but probably not in a SELECT query.
3/ The list can be overly broad
Let's redo the experiment from step 1, but this time the input is as follows:
select
sql
from
v$session ses,
v$px_session pxs
I have omitted the "ses." prefix before the “sql” string. Let's put the cursor right after that string, and press Ctrl+Space…
I was expecting that SQL Dev would find columns from either view with names containing “sql”, but it did find a lot more than that: the following screenshot shows entries which appeared after scrolling down a bit:
And after scrolling further:
Oh no: even shadow types are listed!
(By definition, these usually remain in the shadow… Who would ever use one explicitly in actual code?)
Did I mention that I find this selection of "matching things" overly broad, and possibly vastly out of context?
4/ Regarding the UI aspects
Please allow me two additional remarks:
a) The highlighting color may be uneasy to distinguish
Above the "sql" string is highlighted in bold+green, so that the user may understand why each entry was found as matching the “sql” infix. Meanwhile, depending on color rendition, it could be hard to see the difference with the normal foreground color, in the case of unselected entries. On the contrary, for selected entries the green foreground color could be too close to the blue background color, possibly making the highlighted part uneasy to read.
b) Does pasting multiple entries work?
As shown above, I can select multiple entries in the menu, but so far I haven't found any way of pasting them all at once in my query, no matter what I tried. E.g., nothing happens when I press Enter… Is this supported? Is it through a key combination that I'm unaware of?
Thanks & regards,