LLI Wiki — My Digital Knowledge Base

Oracle: draw a Christmas tree in SQL*Plus

Fun SQL query that prints an asterisk Christmas tree using DECODE, LPAD and ROWNUM.

A fun Oracle query to paste into SQL*Plus: it draws a Christmas tree of asterisks using DECODE, SIGN, LPAD/RPAD and ROWNUM.

set heading off;
set pages 5000;
SELECT DECODE(SIGN(FLOOR(maxwidth / 2) - ROWNUM),
              1,
              LPAD(' ', FLOOR(maxwidth / 2) - (ROWNUM - 1)) ||
              RPAD('*', 2 * (ROWNUM - 1) + 1, ' *'),
              LPAD('* * *', FLOOR(maxwidth / 2) + 3))
  FROM all_objects, (SELECT 40 AS maxwidth FROM DUAL)
 WHERE ROWNUM < FLOOR(maxwidth / 2) + 5
union all select '|Merry Christmas!' from dual;

How it works

Result

SQL*Plus output: Christmas tree of asterisks

Mastodon LLIMastodon SocialFosstodon