B About Enhancements to the SQLcl Command-Line Interface

SQLcl release 25.4 includes a significantly improved command-line interface with advanced line editing, intelligent auto-completion, and customizable keyboard shortcuts.

Topics:

B.1 New Commands for SQLcl Interface

The new commands available for the SQLcl command-line interface are:

Line Number Display

Command: SET LINENUMBERS ON|OFF

Default: ON

Description: Enables line numbers for multi-line SQL statements. This is helpful in navigation, coding and debugging.

Use ON for coding or debugging and OFF for clean output without line numbers.

Enhanced Status Bar

Command: SET STATUSBAR ON|OFF

Default: OFF

Description: View or customize status bar components such as edit mode, current line/column, user, and database ID for real-time feedback and and session context.

  • EDITMODE: Displays the current edit mode, Emacs, Vicmd or Viins.

  • LINECOL: Displays the current line and column numbers.

  • USERNAME: Displays the name of the connected database user.

  • DBID: Displays the id of the connected database.

Commands available for Status Bar:

  • SHOW STATUSBAR: Shows all available components.

  • SET STATUSBAR ON: Enables status bar.
  • SET STATUSBAR OFF: Disables status bar.
  • SET STATUSBAR ADD ALL: Adds all availablecomponents.
  • SET STATUSBAR ADD <comp>..: Adds specific components.
  • SET STATUSBAR REMOVE <comp>..: Removes specific components.
  • SET STATUSBAR DEFAULT: Shows default status bar components.
  • SET STATUSBAR DEFAULT <comp>..: Adds specific components as default.

Troubleshooting: For any rendering issues, run CLEAR SCREEN to resolve.

Intelligent Auto-Pairing

Default: Built-in

Description: Automatically inserts matching brackets and quotes to reduce syntax errors and accelerate SQL entry.

Supported character pairs:

  • Parenthesis ( )
  • Square brackets [ ]
  • Curly braces { }
  • Single quotes ' '
  • Double quotes " "
  • Grave accent ` `

Personalized Auto-Suggestions

Command: SET AUTOSUGGESTIONS ON|OFF

Default: OFF

Description: Suggests command completions based on prior session history.

As you type, matching commands from your session history appear in gray text. The suggestion updates dynamically with each keystroke.

If this setting is OFF, then TAB completion is used.

Key Differences Between Auto-Suggestions and Tab Completion

- Auto-Suggestions Tab Completion
Source Command history Database schema
Best For Repeating queries Discovering objects
Shows Complete previous commands Objects, keywords, functions
When As you type Press TAB key

Keyboard Controls

  • Right Arrow: Accept suggestion.
  • Down Arrow: Ignore suggestion.
  • Backspace: Update suggestion by input.

B.2 Keyboard Shortcuts

The following tables list the shortcut support for both Emacs and Vi editing modes, streamlining navigation, editing, and search.

Navigation

Shortcut Action Description Mode
Ctrl+L Clear Screen Refresh and clear the terminal All
Ctrl+E Emacs Mode Switch to Emacs editing Vi Insert
Ctrl+\ or Esc Cancel Edit Clear input line All
Alt+A Beginning of Buffer Jump to the first line All
Alt+E End of Buffer Jump to the last line All
Ctrl+R Run Immediately Execute command without semicolon or slash terminator All

History and Search Operations

Shortcut Action Description Mode
Alt+R (Option+R) Search Backward Search command history backward with real-time matching All
Alt+< Beginning of History Jump to the oldest command All
Alt+> End of History Jump to the most recent command All

Text Editing Operations

Shortcut Action Description Mode
Ctrl+U Kill Whole Line Remove the current line to the kill ring All
Ctrl+W Kill Word Backward Remove the word before the cursor All
Ctrl+Y Yank (Paste) Insert the most recently killed text at cursor position All
Ctrl+T Transpose Characters Swap the two characters before the cursor (typo correction) Emacs
Ctrl+_ Undo Undo the last editing operation Emacs

The kill ring acts as a clipboard that stores deleted text. To retrieve text for repeated pasting, use Ctrl+Y.

Example:

SQL> SELECT * FROM employees WHERE salary > 5000; 

-- Press Ctrl+U to kill entire line 
SQL> █ 

-- Type new command 
SQL> DELETE FROM temp_table; 

-- Press Ctrl+Y to paste the killed line 
SQL> DELETE FROM temp_table;SELECT * FROM employees WHERE salary > 5000;
Shortcut Action Description Mode
Alt+B (Option+B) Backward Word Move cursor one word to the left All
Alt+F (Option+F) Forward Word Move cursor one word to the right All
Alt+C (Option+C) Capitalize Word Capitalize the word at cursor position All
Alt+U (Option+U) Uppercase Word Convert the word at cursor to uppercase All
Alt+L (Option+L) Lowercase Word Convert the word at cursor to lowercase All

Words are delimited by white space and SQL punctuation characters.

-- Type SQL keyword in lowercase 
SQL> select * from employees;      
↑ Position cursor on "select" 

-- Press Alt+U to convert to uppercase 
SQL> SELECT * from employees;      
↑ Keyword now properly formatted

Completion Help

Shortcut Action Description Mode
Tab Complete Trigger completion menu for the current context All
Tab Tab List All Choices Display all options for current context All

SQLcl provides intelligent completion from multiple sources:

  • Database schema objects (tables, views, sequences, synonyms)
  • SQL keywords and reserved words
  • SQLcl extensions and commands
  • Previously used identifiers from command history

B.3 Help, Configuration and Troubleshooting

This section covers how to get information about the new commands, ensure pesistent configuration using login scripts, and resolve common issues.

SQLcl Built-in Help

During a SQLcl session, you can type the following commands for more information about commands and settings:

  • HELP SET LINENUMBERS: Gives details about using line numbers.
  • HELP SET STATUSBAR: Explains configuration options for the status bar.
  • HELP SET AUTOSUGGESTIONS: Details about enabling auto-suggestions.

Current Settings

To check the current values or settings in your session, use:

  • SHOW LINENUMBERS: Shows if line numbers are enabled.
  • SHOW STATUSBAR: Displays current status bar configuration.
  • SHOW AUTOSUGGESTIONS: Checks if auto-suggestions are enabled.

Login Scripts

SQLcl supports persistent settings using a login.sql file. This is a script SQLcl automatically runs at startup, if present in:

  • Your current working directory or
  • Your home directory (~/login.sql on UNIX/LINUX/MAC and %USERPROFILE%\login.sql on WINDOWS).

Example

Create a login.sql file with your preferred settings:

-- Enable enhanced features 
SET LINENUMBERS ON 
SET STATUSBAR ON 
SET AUTOSUGGESTIONS ON 

-- Configure display 
SET PAGESIZE 50 
SET LINESIZE 120 

-- Set default formats 
SET SQLFORMAT ANSICONSOLE

Troubleshooting Common Problems

The following table lists resolutions for common issues.

Problem Command/Action Description
Display corruption CLEAR SCREEN Refreshes the terminal display
History issues HISTORY -clear (SESSION)? Clears or resets history
Settings reset login.sql Checks for conflicting configurations
Feature not working SHOW <setting> Ensures the feature is enabled