Tiny but mighty new SQLcl feature

Last week, a new version of SQLcl was released, SQLcl 25.4. The release contains a lot of cool new features, but there is one tiny one in it that came thanks to yours truly. Of course, I can only take credit for filing an enhancement request, tracking down the Dev manager, and pestering him until he kindly agreed. And truthfully, it didn’t really involve pestering. Anyway, what am I talking about? It’s about the new set linenumbers on/off option.

Have you ever had the joy of writing a cool, long SQL statement like this:

SELECT r.name AS region_name,
       c.name AS country_name,
       c.population,
       RANK() OVER (PARTITION BY c.region_id ORDER BY c.population DESC) AS rank_per_region,
       ROUND(
         (100 * RATIO_TO_REPORT(c.population)
                  OVER (PARTITION BY c.region_id)
         ),
         2) AS percent_of_region_population,
       RANK() OVER (ORDER BY c.population DESC) AS rank_worldwide,
       ROUND(
         (100 * RATIO_TO_REPORT(c.population)
                 OVER ()
         ),
         2) AS percent_of_world_population
 FROM countries c
  JOIN regions r ON (c.region_id = r.region_id)
  QUALIFY percent_of_region_population >= 10
  ORDER BY rank_worldwide, r.name;

And have you ever typed it down in SQL*Plus or SQLcl, copied it, and found it to be pasted like this:

SQL> SELECT r.name AS region_name,
  2         c.name AS country_name,
  3         c.population,
  4         RANK() OVER (PARTITION BY c.region_id ORDER BY c.population DESC) AS rank_per_region,
  5         ROUND(
  6           (100 * RATIO_TO_REPORT(c.population)
  7                    OVER (PARTITION BY c.region_id)
  8           ),
  9           2) AS percent_of_region_population,
 10         RANK() OVER (ORDER BY c.population DESC) AS rank_worldwide,
 11         ROUND(
 12           (100 * RATIO_TO_REPORT(c.population)
 13                   OVER ()
 14           ),
 15           2) AS percent_of_world_population
 16   FROM countries c
 17    JOIN regions r ON (c.region_id = r.region_id)
 18    QUALIFY percent_of_region_population >= 10
 19*   ORDER BY rank_worldwide, r.name;

This always bugged me, because now there is a choice to make: Do I say, “Congratulations, reader, if you want to copy/paste the statement and run it yourself, have fun removing all these line numbers,” or do I go through the process of removing the line numbers myself and make it easy for any reader who wants to copy/paste it? Or do I type it in a text editor, then copy/paste it into SQLcl, run it, and then copy the SQL from the text editor, but the result of the statement from SQLcl? Well, the answer is: All of this sucks one way or another. What I really want is not to have these line numbers there altogether. I don’t need them, I don’t want them, why can’t I just turn them off? Enter set linenumbers off, newly introduced in SQLcl 25.4, another reason why SQLcl is cool 😎, and the answer to my dilemma:

SQL> set linenumbers off;
SQL> SELECT r.name AS region_name,
            c.name AS country_name,
            c.population,
            RANK() OVER (PARTITION BY c.region_id ORDER BY c.population DESC) AS rank_per_region,
            ROUND(
              (100 * RATIO_TO_REPORT(c.population)
                       OVER (PARTITION BY c.region_id)
              ),
              2) AS percent_of_region_population,
            RANK() OVER (ORDER BY c.population DESC) AS rank_worldwide,
            ROUND(
              (100 * RATIO_TO_REPORT(c.population)
                      OVER ()
              ),
              2) AS percent_of_world_population
      FROM countries c
       JOIN regions r ON (c.region_id = r.region_id)
       QUALIFY percent_of_region_population >= 10
       ORDER BY rank_worldwide, r.name;

Now, (you and) I can just copy/paste from SQLcl directly, without these annoying line numbers and indentation fully intact! 🥳

Pro tip: Combine it with set tab off and you no longer get those annoying tabs in your query results either.

Pro pro tip: Add it to your login.sql and never worry about any of this ever again. Here is my login.sql:

-- Use spaces instead of tab (copy/paste)
set tab off
-- Don't print line numbers (copy/pate)
set linenumbers off
-- Show status bar at the bottom
set statusbar on
-- Status bar: current workding directory
set statusbar add cwd
-- Status bar: git repo status
set statusbar add git
-- Status bar: Oracle DB transaction status
set statusbar add txn
-- Status bar: Timing of last SQL
set statusbar add timing
-- Turn on syntax highlighting
set highlighting on
-- Define highlghting colors
set highlighting keyword foreground green
set highlighting identifier foreground magenta
set highlighting string foreground yellow
set highlighting number foreground cyan
set highlighting comment background white
set highlighting comment foreground black
-- Set output to ansiconsole (clear text)
set sqlformat ansiconsole
set sqlprompt "SQL> "
--set sqlprompt "@|red _USER|@@@|green _CONNECT_IDENTIFIER > |@"

Tiny but mighty new SQLcl feature

Leave a Comment Cancel Reply