How to generate more than 1 blank space between 2 variables in Oracle plsql
The following Oracle pl/sql code
set serveroutput on
cursor emp_cursor is
fetch emp_cursor into v_nam,v_sal;
exit when emp_cursor%notfound;
generates results as follows:
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.
for rec in (
select rpad(first_name,20) first_name
,rpad(last_name ,25) last_name
,lpad(salary ,12) salary
This produces the following output:
De Haan 17000
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.