oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step

Oracle SQL

Tutorial for Oracle SQL


Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions

Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.

Formatting Output in Oracle SQL * Plus

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

SQL Plus output

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

commands to increase linesize in sqlplus

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

sqlplus structured output

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

formatting numeric values

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

number format

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

formatting dates in sqlplus

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

format sql prompt in sqlplus

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.



HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats

Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved