Printing an alphabet pyramid in PL/SQL

Question

I have an exercise to write a program which prints out an alphabet pyramid like this:

    A
   ABA
  ABCBA
 ABCDCBA
ABCDFDCBA

The task also suggests using INSTR, LPAD, UPPER.

I want to have the pyramid which contains the all the letters in the alphabet. However, I find it is easier to make it in numbers first so:

IF x in 0..25 loop
dbms_output.put_line(x);
end loop;
end; 

The result is just a straight line of numbers from 0-25. I don't know how to add spaces before the numbers, which eventually needs to be letters, to form a the pyramid shape. Please don't give the full answer, I just need some suggestions and diretions to solve this task.

Answer 1

This one works:

DECLARE
    aLine VARCHAR2(100);
BEGIN
    FOR PyramidLevel IN 0..25 LOOP
        aLine := NULL;

        FOR i IN 0..PyramidLevel-1 LOOP
            aLine := aLine || CHR(i+65);
        END LOOP; 

        FOR i IN REVERSE 0..PyramidLevel LOOP
            aLine := aLine || CHR(i+65);
        END LOOP;
        aLine := LPAD(aLine, 26+PyramidLevel);       

       DBMS_OUTPUT.PUT_LINE('_'||aLine);
    END LOOP;
END;


_                         A
_                        ABA
_                       ABCBA
_                      ABCDCBA
_                     ABCDEDCBA
_                    ABCDEFEDCBA
_                   ABCDEFGFEDCBA
_                  ABCDEFGHGFEDCBA
_                 ABCDEFGHIHGFEDCBA
_                ABCDEFGHIJIHGFEDCBA
_               ABCDEFGHIJKJIHGFEDCBA
_              ABCDEFGHIJKLKJIHGFEDCBA
_             ABCDEFGHIJKLMLKJIHGFEDCBA
_            ABCDEFGHIJKLMNMLKJIHGFEDCBA
_           ABCDEFGHIJKLMNONMLKJIHGFEDCBA
_          ABCDEFGHIJKLMNOPONMLKJIHGFEDCBA
_         ABCDEFGHIJKLMNOPQPONMLKJIHGFEDCBA
_        ABCDEFGHIJKLMNOPQRQPONMLKJIHGFEDCBA
_       ABCDEFGHIJKLMNOPQRSRQPONMLKJIHGFEDCBA
_      ABCDEFGHIJKLMNOPQRSTSRQPONMLKJIHGFEDCBA
_     ABCDEFGHIJKLMNOPQRSTUTSRQPONMLKJIHGFEDCBA
_    ABCDEFGHIJKLMNOPQRSTUVUTSRQPONMLKJIHGFEDCBA
_   ABCDEFGHIJKLMNOPQRSTUVWVUTSRQPONMLKJIHGFEDCBA
_  ABCDEFGHIJKLMNOPQRSTUVWXWVUTSRQPONMLKJIHGFEDCBA
_ ABCDEFGHIJKLMNOPQRSTUVWXYXWVUTSRQPONMLKJIHGFEDCBA
_ABCDEFGHIJKLMNOPQRSTUVWXYZYXWVUTSRQPONMLKJIHGFEDCBA

Looks like DBMS_OUTPUT.PUT_LINE() trims leading space characters (at least in my TOAD), so I put _ at the beginning.

Answer 2

Pure SQL using model clause

select lpad(x1, 26, ' ') || substr(x2, 2) x from dual
model
dimension by (1 id)
measures (cast(null as varchar2(26)) x1, cast(null as varchar2(26)) x2)
(x1[for id from 1 to 26 increment 1] = x1[cv(id)-1] || chr(64+cv(id)),
x2[for id from 1 to 26 increment 1] = chr(64+cv(id)) || x2[cv(id)-1]);

X
---------------------------------------------------
                         A
                        ABA
                       ABCBA
                      ABCDCBA
                     ABCDEDCBA
                    ABCDEFEDCBA
                   ABCDEFGFEDCBA
                  ABCDEFGHGFEDCBA
                 ABCDEFGHIHGFEDCBA
                ABCDEFGHIJIHGFEDCBA
               ABCDEFGHIJKJIHGFEDCBA
              ABCDEFGHIJKLKJIHGFEDCBA
             ABCDEFGHIJKLMLKJIHGFEDCBA
            ABCDEFGHIJKLMNMLKJIHGFEDCBA
           ABCDEFGHIJKLMNONMLKJIHGFEDCBA
          ABCDEFGHIJKLMNOPONMLKJIHGFEDCBA
         ABCDEFGHIJKLMNOPQPONMLKJIHGFEDCBA
        ABCDEFGHIJKLMNOPQRQPONMLKJIHGFEDCBA
       ABCDEFGHIJKLMNOPQRSRQPONMLKJIHGFEDCBA
      ABCDEFGHIJKLMNOPQRSTSRQPONMLKJIHGFEDCBA
     ABCDEFGHIJKLMNOPQRSTUTSRQPONMLKJIHGFEDCBA
    ABCDEFGHIJKLMNOPQRSTUVUTSRQPONMLKJIHGFEDCBA
   ABCDEFGHIJKLMNOPQRSTUVWVUTSRQPONMLKJIHGFEDCBA
  ABCDEFGHIJKLMNOPQRSTUVWXWVUTSRQPONMLKJIHGFEDCBA
 ABCDEFGHIJKLMNOPQRSTUVWXYXWVUTSRQPONMLKJIHGFEDCBA
ABCDEFGHIJKLMNOPQRSTUVWXYZYXWVUTSRQPONMLKJIHGFEDCBA

26 rows selected.

Update. Just for fun, the same result with single rule

select lpad(' ', 26 - ceil(length(x)/2), ' ')  || x as x from dual
model
dimension by (1 id)
measures (cast(null as varchar2(51)) x)
(x[for id from 1 to 26 increment 1] = substr(x[cv(id)-1], 1, cv(id)-1) || chr(64+cv(id)) || substr(x[cv(id)-1], cv(id)-1));

select lpad(' ', 26 - ceil(length(x)/2), ' ')  || x as x
from (select 'A' x from dual union all select 'ABA' from dual)
model
dimension by (rownum id)
measures (cast(x as varchar2(51)) x)
(x[for id from 3 to 26 increment 1] = regexp_replace(x[cv(id)-1], '(.)(.)\1', '\1\2' || chr(64+cv(id)) || '\2\1'));