Formatting SQL Output - SQLPlus

How do make your SQL output more readable and pretty while working on with SqlPlus? Pretty is what you need when dealing with SQLPlus, correct!. It is very hard to focus when the SqlPlus query output is mess and hard to read. This blog will help you how to make your output clear and readable?

SET PAUSE ON  --> (Generates One Page of Output)
SET LINESIZE n  --> (Left to Right  Count)
SET PAZESIZE  n --> (Top to Bottom Line count)

Sometime correct settings of  LINESIZE & PAGESIZE isn’t enough. The width of a column is very long. This long column width makes the output ugly and mess. This  ugly output adds more stress to your hectic day. You can reduce the column width without using a DDL SQL statement.
Did you know that?

COLUMN  COLUMN_NAME FORMAT A(n)
Example:
COLUMN title FORMAT A35 — This makes the ‘title’ column width set to 35 char length. This only works for string, varchar, and char datatype. Sorry PAL, it does not work for number. Try and experiment with number.

How do you check the current  page/line settings in your SQLplus?
SHOW PAGESIZE
SHOW LINESIZE
COLUMN column_name

How to clear the column display setting after you are done?
COLUMN column_name CLEAR

Let’s work on an example to see how we can use the proper pagesize, linesize, and column width to make the difference in readable output. Lets begin!
Before Formatting: Run the query!
SELECT segment_name, 
       segment_type, 
       extents, 
       tablespace_name, 
       min_extents, 
       max_extents 
FROM   user_segments 
WHERE  rownum <= 10; 

Check the output of the above query in SQLPlus and tell me if you can read it?

After Formatting Output:
SET PAGESIZE 50
SET LINESIZE 150
SET SERVEROUTPUT ON
SET PAUSE ON
COLUMN SEGMENT_NAME FORMAT A20
COLUMN SEGMENT_TYPE FORMAT a10
COLUMN TABLESPACE_NAME FORMAT A15
SELECT segment_name, 
       segment_type, 
       extents, 
       tablespace_name, 
       min_extents, 
       max_extents 
FROM   user_segments 
WHERE  rownum <= 10; 

Check the output of the above query in SQLPlus.
Isn’t it better? Much, much better!!
Remember: These settings are good for one session only. There are ways to save the setting permanent but who cares!!