0

I've written a stored procedure to display on screen details about employees salary and it works great, but I would like to fix the information displayed on screen. I tried to use LPAD and RPAD function to order "columns" on screen but it doesn't work or probably I didn't used it well. Do you have any idea? Thanks.

            FOR j IN c_recibos (t_emp(i).numero, t_emp(i).legajo)
            LOOP
                DBMS_OUTPUT.PUT_LINE(
                    RPAD(j.concepto, 24, ' ') || ' ' || 
                    LPAD(j.cantidad, 10, ' ') || ' ' || 
                    RPAD(TO_CHAR(j.haberes, '0000.00'), 9, ' ') || ' ' || 
                    LPAD(TO_CHAR(j.retenciones, '0000.00'), 28, ' '));
            END LOOP;

            FOR k IN c_totales(t_emp(i).numero, t_emp(i).legajo)
            LOOP
                DBMS_OUTPUT.PUT_LINE(
                    'Totales: ' || k.total_haberes || ' ' || 
                    k.total_retenciones);
            END LOOP;

            DBMS_OUTPUT.PUT_LINE('------------');
        END LOOP;

Current information displayed:

enter image description here

When I export information to notepad I would like to see something like that:

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Young Al Capone
  • 369
  • 1
  • 6
  • 25
  • 1
    Looks OK apart from a couple of extra spaces before 'Cantidad' and the numbers in the 'Retenciones' column. Bear in mind that null values don't get padded, and space is the default padding character so you don't need the `' '`. (btw is that Notepad or Excel?) – William Robertson May 29 '17 at 17:38
  • Sorry, It's for excel but doesn't work yet. Is there any function or something to format the output? or I should continue trying with LPAD and RPAD functions? – Young Al Capone May 29 '17 at 21:38
  • 1
    If it's only for Excel then you might use a delimiter character (e.g. a tab is or pipe) rather than aligning the text in columns. If it has to be aligned, then `lpad` and `rpad` should do the job. You might assign text to a `char` variable in place of right-padding, as it comes to the same thing (with the same caution about null values). – William Robertson May 30 '17 at 07:25

1 Answers1

1

You can do this with LPAD and RPAD. Just a little math to do.

1) you should add 2 spaces to Cantidad

2) Totales should also be padded (statically for the first, then the numbers):

 FOR j IN c_recibos (t_emp(i).numero, t_emp(i).legajo)
 LOOP
     DBMS_OUTPUT.PUT_LINE(
         RPAD(j.concepto,                        24, ' ') || ' ' || 
         LPAD(j.cantidad,                        12, ' ') || ' ' ||
         RPAD(TO_CHAR(j.haberes, '0000.00'),      9, ' ') || ' ' || 
         LPAD(TO_CHAR(j.retenciones, '0000.00'), 28, ' ')
     );
 END LOOP;

 FOR k IN c_totales(t_emp(i).numero, t_emp(i).legajo)
 LOOP
     DBMS_OUTPUT.PUT_LINE(
         RPAD('Totales:',                              24, ' ') || ' ' || 
         LPAD(':',                                     12, ' ') || ' ' || 
         RPAD(TO_CHAR(k.total_haberes, '0000.00'),      9, ' ') || ' ' || 
         LPAD(TO_CHAR(k.total_retenciones, '0000.00'), 28, ' ')
     );
 END LOOP;
J. Chomel
  • 8,193
  • 15
  • 41
  • 69