how to write a query that results an pyramid structure in Oracle SQL

Question

suppose if i give '12345'

output should be:

1
12
123
1234
12345
1234
123
12
1

Answer 1

i'm not sure in SQL query, nut it can achieved from PLSQL,:

declare
str varchar2(10):='12345';
begin
for i in 1..length(str)
loop
dbms_output.put_line(substr(str,1,i));
end loop;
for i in reverse 1..length(str)-1
loop
dbms_output.put_line(substr(str,1,i));
end loop;
end;

Answer 2

SELECT RPAD('12345',LEVEL*1,'1') FROM DUAL
CONNECT BY LEVEL<=5
union all
SELECT RPAD('12345',(5-LEVEL)*1,'1') FROM DUAL
CONNECT BY LEVEL<=5;