More remarks about tab completion in SQLcl 23.1 (23.1.0.89.0930).
If anything, that will illustrate some of the difficulties—chief among which: SQLcl does not really know how to read my mind.
Here I'll be using SCOTT, a user with only basic privileges (CREATE SESSION + RESOURCE).
Remark #1: many SQL functions appear to be missing
Suppose I want to type in: select sysdate from dual; below I have hit the TAB key at the position depicted by the | character:
SQL> select sys|
SYS$UMF. SYSBACKUP. SYSEVENT SYSRAC. SYS_DGAGG SYS_IXMLAGG SYS_IXQAGGAVG SYS_NT_COLLECT
SYS. SYSDG. SYSKM. SYSTEM. SYS_HIERDGAGG SYS_IXQAGG SYS_IXQAGGSUM SYS_XMLAGG
Not exactly what I expected.
Besides, it looks like there are multiple kinds of names here. I understand that schema names are suffixed by a dot (.) character, but still, it's a bit hard to figure out what is what.
Now, suppose I change my mind: I want select localtimestamp from dual instead, so I hit the backspace key 3 times (erasing sys), then I type in loc, and hit the TAB key again:
SQL> select loc|
DBMS_BLOCKCHAIN_TABLE DBMS_PDB_CHECK_LOCKDOWN KU$_BYTES_ALLOC_T KU$_EXTLOC_T KU$_PROCOBJ_LOCS OWA_OPT_LOCK
DBMS_BLOCKCHAIN_UTL KU$_BLOCKCHAIN_T KU$_EXTLOC_LIST_T KU$_PROCOBJ_LOC KU$_TAB_BYTES_ALLOC_T
Isn't that scary, if you're not prepared for it? Hey, this is just SCOTT, not a DBA account…
(I know, SQLcl has been designed chiefly with DBA needs in mind.)
And here we see what happens as a result of looking for names matching the searched string not only as a prefix, but at any position: suddenly plenty of matching names are found—but not the LOCALTIMESTAMP function that was expected in the first place.
Remark #2: searching for names matching the searched string at any position can result in overly long lists of completion suggestions, which may not always be relevant.
Here I want to type: select * from session_roles; I hit the TAB key at the position shown by the | symbol:
SQL> select * from session|
GV$SESSION DBA_WORKSPACE_SESSIONS GV$SESSION_EVENT CDB_XS_SESSION_NS_ATTRIBUTES DBA_XS_SESSION_NS_ATTRIBUTES GV$DETACHED_SESSION
APEX_SESSION DBMS_MACOLS_SESSION GV$SESSION_FIX_CONTROL CDB_XS_SESSION_ROLES DBA_XS_SESSION_ROLES GV$EQ_DEQUEUE_SESSIONS
DBMS_SESSION DBMS_XS_SESSIONS TSM$SESSION_ID CTX_USER_SESSION_SQES GV$ACTIVE_SESSION_HISTORY GV$GES_DEADLOCK_SESSIONS
ACCHK_SESSION GV$HS_SESSION TSM$SESSION_ID_LIST DBA_AUDIT_SESSION GV$ALL_ACTIVE_SESSION_HISTORY GV$LOGMNR_SESSION
APEX_WORKSPACE_SESSIONS GV$PX_SESSION APEX_SESSION_STATE DBA_DATAPUMP_SESSIONS GV$AQ_DEQUEUE_SESSIONS GV$PLSQL_DEBUGGABLE_SESSIONS
APEX_WORKSPACE_SESSION_GROUPS GV$SESSIONS_COUNT CDB_AUDIT_SESSION DBA_DV_RAS_ATTACH_SESSION_AUTH GV$AW_SESSION_INFO GV$RSRC_SESSION_INFO
CDB_LOGMNR_SESSION GV$SESSION_BLOCKERS CDB_DATAPUMP_SESSIONS DBA_SQL_FIREWALL_SESSION_LOGS GV$CURRENT_SESSION_FIX_CONTROL WRI$_REPT_SESSION
CDB_WORKSPACE_SESSIONS GV$SESSION_CONNECT_INFO CDB_XS_ACTIVE_SESSIONS DBA_XS_ACTIVE_SESSIONS GV$DATAPUMP_SESSION
DBA_LOGMNR_SESSION GV$SESSION_CURSOR_CACHE CDB_XS_SESSIONS DBA_XS_SESSIONS GV$DATAPUMP_SESSIONWAIT_INFO
That's plenty, but not at all what I expected.
If I just hit _ and r keys at that point, the completion suggestions menu narrows down to:
SQL> select * from session_r|
CDB_XS_SESSION_ROLES DBA_XS_SESSION_ROLES
(The cursor is at the position shown by the | character.) Now if I just hit TAB again, abracadabra!
SQL> select * from SESSION_ROLES
If, on the other hand, I had typed in select * from session_ before hitting the TAB key, the results would have been very different:
SQL> select * from session_|
SESSION_CONTEXT SESSION_PRIVS SESSION_PRIVS_ALL SESSION_ROLES SESSION_SCHEMA_PRIVS
(Did I say the user experience could be more consistent?)
Remark #3: even if a single match is found, completion should not be automatic.
Or if is, there ought to be a control key to undo it easily, in case SQLcl has found a match which is not what the user expected.
(By the way, such an undo shortcut would also be useful for leaving the menu of suggested completions, after having entered it, and restore the previous state of the input buffer.)
Here I want to type the following query: select * from user_objects; however, if I hit the TAB key right after typing select * from user (boldly expecting that SQLcl offer USER_XXX views as completion suggestions), the result is a complete surprise:
SQL> select * from USER_XML_PARTITIONED_TABLE_OK
(But why?)
Same if hitting the TAB key just after select * from user_
It is necessary to type as far as select * from user_o before hitting the TAB key for view names matching 'USER_O%' to appear in the completion suggestions.
Plenty of room for improvement, I guess…
Regards,