Simple and useful hints for formatting output in SQL*Plus

While using Oracle SQL*Plus for interacting with the database you must have many times seen unstructured output for SQL queries. i.e. the output is hard to interpret.  

Like for example if you give a query like this

 SQL> select * from all_users;

You will get a output like this

You can easily structured the output by adjusting the line size and formatting the column by typing the following commands

SQL> set linesize 100
SQL> col username format a30

And then again give the same query, you will see the output in well structured format as shown below

Formatting Number Values in SQL Plus

You can also set Number format so see the numeric values with commas for easy reading.

For example if you select the rows from scott emp table you will see the output like this

In the above output the salary column is shown without any formatting which is the default in SQL Plus. If you want to format numeric column values with commas, you can format it like this for example

SQL> col sal format $999,99,999.99

and now you will get the output like this

Similarly you can also format all numeric values by giving the following command

SQL> set numformat "999,99,999.99"

Remember the above command will format all numeric values i.e. even empno, deptno etc will be shown in the format, which you don't want in most case.

Format DATES in SQL Plus

Similarly you can also format date values in whatever date format you want by setting the session variable NLS_DATE_FORMAT

For example if you set it to the following

SQL> alter session set nls_date_format='dd-Mon-yyyy hh:mi:sspm';

You will get the output like this

Changing SQL Prompt in SQL PLus

You can change the default SQL> prompt in SQL Plus to something more meaningful like you can show username and SID and date in the prompt by giving the following command

SQL> set sqlprompt "_user 'ON' _connect_identifier':'_date> "

Then SQL Prompt will change to the following

This is particularly useful if you work on multiple databases.

Automatic Setting

What about automatically setting the above formats whenever you login to SQL Plus?

If you want specific settings to be set whenever you login to SQL Plus, then you can write these set commands in glogin.sql or login.sql file located in ORACLE_HOME/sqlplus/admin folder

For example, you can open or create a new glogin.sql or login.sql file using any text editor and write the following commands

 alter session set nls_date_format='dd-Mon-yyyy hh:mi:sspm';
set sqlprompt "_user 'ON' _connect_identifier':'_date>  "

Now whenever you login to Oracle using SQL Plus , SQL Plus will show the dates in the above format and SQL Prompt will also change to the above format.