Getting most of Code Completion in SQL Developer

Here are few tips to make yourself comfortable with the redesigned code completion in SQL Developer 1.5, which is currently available as a preview release.

The most ambitious code completion feature is that it is triggered by any character typed, not only the “.”. If you make a slight pause when typing, you’ll notice a list of suggestions. The delay is set on the preferences page (Code Editor -> Code Insight) and the recommended value is the default 300 ms.

Code completion is designed to respond in less than 0.5 sec. Everything longer than that is logged onto java console. If you experience performance problems, you can disable the “eager” mode of code completion on the preferences page so that it would be triggered by ctrl-space only. However, before you do that I suggest to spend 5 min and report the db version, how large it is (the number of objects), and possibly the output of java console. (The executable with java console sqldeveloper.exe is located in sqldev_home/sqldeveloper/bin).

In case if you are still reading, begin typing

select *

and the list of 3 keywords appears. However, if you begin the query with

select 1

then no suggestion would appear at all. This is because the list of suggested keywords is artificially restricted to 3 items. Having more than 3 keywords in the suggestion list is too overwhelming for the end user so that it was decided not to prompt anything in this case. Sure when we type “1”, the expression can be amended at least with any of the 4 arithmetic operators (and even more choices). Continued the second query we get the “from” keyword suggestion as soon as the “f” is typed. A list of reserved oracle SQL&PL/SQl keywords contains four items starting with “F”: FETCH, FORM, FOR, FROM, but the code completion is syntax driven, so that the other options are out of context. Therefore we arrived to

select 1 from

At this point you would be prompted with the list of the tables and views in your current schema, and the list of the users. So you either start typing the other schema name, or continue with the table in current schema. You can also may start typing the name in the other schema without schema prefix, say “all_objects”. The list of options would shrink to match the prefix you typed, and it may well be that your choice is not on the list. Say, you are connected as a familiar SCOTT user, but want to query all_objects. Therefore, as soon as you typed

select 1 from a

you would get the following prompt:

The list shows all the tables and views in all the schemas, and again, in order not to make it overwhelmingly large for the end user, it is truncated to 20 items for each object type. Hence the the “…” item at the end of the list of tables. You can either select from the list, or type more in order to narrow the selection. At some point you may get a list with fewer than 20 items, yet there would still be the “…” list entry at the end.

This is because the list of items may have been narrowed by pure selection of the items matching the prefix. You may select the “…” item, which would retrigger the code completion, and you’ll get the list repopulated from the database, as opposed to narrowing the previous incomplete list of 20 items:

Also this retriggering happens automatically whenever the narrowed list becomes empty.

So now that we have the basic skeleton of the query, you can continue on and make it more sophisticated. Join more tables or views, alias them, build the where clause, or complete the select clause. Note that if you just start typing

select emp.

then no code completion prompt would appear. There are several reasons why.

First, here is how ANSI SQL standard specifies the order of query evaluation:

• Start in the FROM clause specify the list of all tables/views used in the query block. Use aliases for disambiguation/references from the other clauses.
• Go to the WHERE clause and remove rows that do not pass criteria.
• Go to the optional GROUP BY clause…
• Go to the optional HAVING clause…
• Go to the SELECT clause and construct the expressions in the list.

Optimization can affect this order, of course, but the point is that you don’t complete the SELECT clause until the FROM clause is finished (or is partially constructed, at least). Something like select * from is a good template to start.

Beyond basic single table queries you most likely would want table aliases, not full table names in the SELECT clause, and there is no way telling what “e” in the

select e.

is without having the FROM clause build in advance.

Second, you type more this way. Compare

select emp.empno, emp.ename, emp.sal from emp

to

select e.empno, e.ename, e.sal from emp e

or even

select empno, ename, sal from emp e

(and note that “emp” is pretty short name for a table!). Code completion handles all the three cases seamlessly, but only after you complete the FROM clause (which you have to do anyway!)

The identifier case is controlled from the formatter preferences page. You can set the keywords be upper/lower, the whole statement to be upper/lower, or select “no change” so that you have explicit control.

Finally there are few quirks, that look odd at first. Suppose you are editing the following PL/SQL fragment:

PROCEDURE PROCEDURE2 (
in_arg IN NUMBER ,
out_arg1 OUT NUMBER)
AS
i NUMBER;
BEGIN
DECLARE
x NUMBER;
i NUMBER;
y VARCHAR2(1);
BEGIN
x:=1
END;
END PROCEDURE2;


You’ll be prompted local variable names correctly scoped when you type “x:=1”, but not the trailing semicolon which is “obviously” missing. This impression is wrong, however. There is lot more keywords and operation symbols that can be inserted after the constant 1 (including arithmetic operators again!), so that it overwhelms the list of suggestions.

The other example where code completion fails to make a suggestion is the case expression. Suppose you have

PROCEDURE PROCEDURE2 (
in_arg IN NUMBER ,
out_arg1 OUT NUMBER)
AS
i NUMBER;
BEGIN
DECLARE
x NUMBER;
i NUMBER;
y VARCHAR2(1);
BEGIN
i:=CASE WHEN
END;
END PROCEDURE2;


Then no local variable is suggested. This is because the completion algorithm tries only few insertions. More insertions would explode the search space and cause slow response time, this is why the completion tries to insert no more than 3 grammar symbols (terminals or nonterminals). The grammatical structure of the incomplete case expression can’t be repaired by adding 3 symbols, so the completion fails. You are advised to refine the case statement, say by adding the “END;” then returning to where you’ll get code completion prompted the local variables as it should.