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.