2-8. Formatting Query Results
Problem
Your boss asks you to print the results from a couple of queries in a nicely formatted manner.
Solution
Use a combination of different built-in formatting functions along with the concatenation operator (||) to create a nice-looking basic report. The RPAD and LPAD functions along with the concatenation operator are used together in the following example that displays a list of employees from a company:
DECLARE
CURSOR emp_cur IS
SELECT first_name, last_name, phone_number
FROM employees;
emp_rec employees%ROWTYPE;
BEGIN
FOR emp_rec IN emp_cur LOOP
IF emp_rec.phone_number IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(RPAD(emp_rec.first_name || ' ' || emp_rec.last_name, 35,'.') ||
emp_rec.phone_number);
ELSE
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name ||
' does not have a phone number.'),
END IF;
END LOOP;
END;
The following is another variant of the same report, but this time dashes are used instead of using dots to space out the report:
DECLARE
CURSOR emp_cur IS
SELECT first_name, last_name, phone_number
FROM employees;
emp_rec employees%ROWTYPE;
BEGIN
FOR emp_rec IN emp_cur LOOP
IF emp_rec.phone_number IS NOT NULL THEN
-- CHECK FOR INTERNATIONAL PHONE NUMBERS
IF length(emp_rec.phone_number) > 12 THEN
DBMS_OUTPUT.PUT_LINE(RPAD(emp_rec.first_name || ' ' || emp_rec.last_name, 20)||
' - ' || LPAD(emp_rec.phone_number,18));
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD(emp_rec.first_name || ' ' || emp_rec.last_name, 20)||
' - ' || LPAD(emp_rec.phone_number,12));
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name ||
' does not have a phone number.'),
END IF;
END LOOP;
END;
How It Works
The RPAD and LPAD functions are used to return the data in a formatted manner. The RPAD function takes a string of text and pads it on the right by the number of spaces provided by the second parameter. The syntax for the RPAD function is as follows:
RPAD(input_text, n, character)
In this syntax, n is the number of spaces used to pad. Similarly, the LPAD function pads on the left of the provided string. The syntax is exactly the same as RPAD; the only difference is the direction of the padding. The combination of these two functions, along with the concatenation operator (||), provides for some excellent formatting options.
It is important to look at the data being returned before you try to format it, especially to consider what formatting options will look best when generating output for presentation. In the case of the examples in this recipe, the latter example would be the most reasonable choice of formatting for the data being returned, since the phone number includes dots in it. The first example uses dots to space out the report, so too many dots may make the output difficult to read. Know your data, and then choose the appropriate PL/SQL built-ins to format accordingly.
Note When using DBMS_OUTPUT to display data, please be sure to pay attention to the size of the buffer. You can set the buffer size from 2,000 to 1,000,000 bytes by passing the size you desire to the DBMS_OUTPUT.ENABLE procedure. If you attempt to display content over this size limit, then Oracle will raise an exception.
Oracle provides a number of built-in functions to use when formatting strings. Two others that are especially useful are LTRIM(<string>) and RTRIM(<string>). These remove leading and trailing spaces, respectively. See your Oracle SQL Reference manual for a complete list of available string functions.