Oracle and PL/SQL Recipes: A Problem-Solution Approach

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); ...