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
maxwidth(40) sets the width of the tree;ROWNUMnumbers the rows taken fromall_objects, which is used only as a row source.- While
FLOOR(maxwidth / 2) - ROWNUMis positive,DECODEreturns a foliage line (left padding then a growing number of asterisks); afterwards it returns the trunk* * *. set heading offhides the column header andset pages 5000avoids page breaks.