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