8 Replies Latest reply on Mar 28, 2013 7:07 PM by Frank Kulash

# one more for loop

Sorry to troubled you...

I need logic for the below output...same with for loop
``````                          1
2    2
3  3   3
4   4   4   4
5   5   5   5   5

and

1
1      2
1     2      3
1     2     3    4
1   2    3     4        5``````
regards
SA

Edited by: 871270 on Mar 28, 2013 10:55 AM
• ###### 1. Re: one more for loop
Hi,
871270 wrote:
Sorry to troubled you...

I need logic for the below output...same with for loop
``````1
2    2
3  3   3
4   4   4   4
5   5   5   5   5                     ``````
``````BEGIN
FOR  j  in  2 .. 5
LOOP
dbms_output.put_line ( '|' || LPAD ( TO_CHAR ( j * ( POWER (10, j)
- 1
)
/ 9
, '9G9G9G9G9'
, 'NLS_NUMERIC_CHARACTERS = ''. '''
)
, j + 9
)
);
END LOOP;
END;
/``````
SQL*Plus doesn't like to display spaces at the beginning of dbms_output lines. That's why I started each line with a '|'. The actual output is:
``````|        2 2
|       3 3 3
|      4 4 4 4
|     5 5 5 5 5``````
Thanks to Solomon Yakobson for showing how to use TO_CHAR (... 'NLS_NUMERIC_CHARACTERS...') {message:id=10935027}
1 person found this helpful
• ###### 2. Re: one more for loop
Hi,
871270 wrote:
...
``````and

1
1      2
1     2      3
1     2     3    4
1   2    3     4        5``````
``````BEGIN
FOR  j  IN  1 .. 5
LOOP
dbms_output.put_line (  '|'
||     LPAD ( SUBSTR ( '1   2   3   4   5'
, 1
, (4 * j) - 3
)
, (2 * j) + 15
)
);
END LOOP;
END;
/``````
Actual output:
``````|                1
|              1   2
|            1   2   3
|          1   2   3   4
|        1   2   3   4   5``````
1 person found this helpful
• ###### 3. Re: one more for loop
Frank Kulash wrote:
SQL*Plus doesn't like to display spaces at the beginning of dbms_output lines. That's why I started each line with a '|'.
Hi Frank,

There is no need for a pipe character in fist postion to make dbms_output output to line up. Just use serveroutput wrapped format:
``````SQL> set serveroutput on
SQL> BEGIN
2      FOR  j  IN  1 .. 5
3      LOOP
4          dbms_output.put_line (
5             LPAD ( SUBSTR ( '1   2   3   4   5'
6                         , 1
7             , (4 * j) - 3
8             )
9           , (2 * j) + 15
10           )
11          );
12      END LOOP;
13  END;
14  /
1
1   2
1   2   3
1   2   3   4
1   2   3   4   5

PL/SQL procedure successfully completed.

SQL> set serveroutput on format wrapped
SQL> BEGIN
2      FOR  j  IN  1 .. 5
3      LOOP
4          dbms_output.put_line (
5             LPAD ( SUBSTR ( '1   2   3   4   5'
6                         , 1
7             , (4 * j) - 3
8             )
9           , (2 * j) + 15
10           )
11          );
12      END LOOP;
13  END;
14  /
1
1   2
1   2   3
1   2   3   4
1   2   3   4   5

PL/SQL procedure successfully completed.

SQL> ``````
SY.
1 person found this helpful
• ###### 4. Re: one more for loop
can you explain what is the mean of this task for business? ))))
• ###### 5. Re: one more for loop
Hi,
Solomon Yakobson wrote:
``````... There is no need for a pipe character in fist postion to make dbms_output output to line up. Just use serveroutput wrapped format:
...
SQL> set serveroutput on format wrapped``````
Thanks!
• ###### 6. Re: one more for loop
Hi SY

I am not gettting the following output
``````1
1   2
1   2   3
1   2   3   4
1   2   3   4   5

you have given both queries for below output only...

1
1   2
1   2   3
1   2   3   4
1   2   3   4   5``````
Please help me for above pyramid...

regards
SA
• ###### 7. Re: one more for loop
871270 wrote:

you have given both queries for below output only...
I didn't. Frank did. I just showed how to display DBMS_OUTPUT leading spaces in SQL*Plus using wrapped format.

SY.
1 person found this helpful
• ###### 8. Re: one more for loop
Hi,
871270 wrote:
Hi SY

I am not gettting the following output
If you want left-justified output like that, then make sure the SERVEROUTPUT FORMAT is WORD_WRAPPED:
``SET  SERVEROUTPUT  ON    FORMAT  WORD_WRAPPED``
or simply don't use LPAD.
``````1
1   2
1   2   3
1   2   3   4
1   2   3   4   5

you have given both queries for below output only...

1
1   2
1   2   3
1   2   3   4
1   2   3   4   5``````
Please help me for above pyramid...
I am so confused.
First you said you were only getting the indented version.
Now you're saying you want help to get the indented version.
Which is it?

Don't concatenate a '|' to the beginning of the row.

If you want leading spaces in the output, then use
``SET SERVEROUTPUT     ON     FORMAT  WRAPPED``
If you don't want leading spaces, then use
``SET SERVEROUTPUT     ON     FORMAT  WORD_WRAPPED``