How to generate more than 1 blank space between 2 variables in Oracle plsql



The following Oracle pl/sql code

set serveroutput on
declare
v_nam employees.last_name%type;
v_sal employees.salary%type;
cursor emp_cursor is
select last_name,salary
from employees;
begin
open emp_cursor;
loop
fetch emp_cursor into v_nam,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line(to_char(v_nam)||'======' ||to_char(v_sal));
end loop;
end;


generates results as follows:

King======24000
Kochhar======17000
De Haan======17000
Hunold======9000
Ernst======6000
Austin======4800
Pataballa======4800
Lorentz======4200

I want to replace ==== with blank space and numbers to be right aligned.


It is actually quite straightforward to do this in Oracle PL/SQL using the SQL functions LPAD and RPAD as we'll see in a minute. However if you're writing reports, Oracle PL/SQL is not the best tool for that. Quite sophisticated reports can be created using SQL*Plus or you can use tools like Oracle Application Express (APEX) or Oracle Reports (part of Oracle Fusion Middleware).

Back to the question though. By slightly modifying your Oracle PL/SQL code we can produce output that is formatted neatly with the the numbers right justified.

begin
  for rec in (
     select rpad(first_name,20) first_name
           ,rpad(last_name ,25) last_name
           ,lpad(salary   ,12) salary
      from employees)
  loop
     dbms_output.put_line(
       rec.last_name||rec.salary
                         );
  end loop;

end;


This produces the following output:
King                       24000
Kochhar                    17000
De Haan                    17000
Hunold                      9000
Ernst                       6000
Austin                      4800
Pataballa                   4800
Lorentz                     4200


The LPAD function pads the specified string to the required length with (in this case) spaces at the beginning of the string (the left). RPAD does the same thing by adding spaces to the right of the string.

The other change we made was to dispense with the cursor declaration and use a CURSOR FOR LOOP to save the trouble of having to code the cursor open and close and the condition for exiting from the loop. The CURSOR FOR LOOP does all that for you.

For more help with Oracle pl/sql see our pl/sql tutorials


If you're interested in formal Oracle training (either on-line or in a classroom) then see our Oracle training page for more information.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.