Monday, October 22, 2018

7 Days of Oracle: Day #3 - Formatting SQL*Plus Results

A luxury of executing queries via SQL Server Management Studio is well formatted and easy to read output.  When executing queries via SQL*Plus you may not enjoy the same experience.  This is not an apples to apples comparison, however, if you're used to SSMS then executing queries via the SQL*Plus command line could be a shock to the system. 

Here is an example of query output with default settings:
SQL> select username, account_status, expiry_date from dba_users;

We can make the results fit on one line by extended the number of characters a line can contain.  This made the results above more readable, but improvement can still be made.

SQL> set linesize 400

You can see above the ‘Username’ attribute was defined with several more characters than what was needed in the result sets.  We can use the command below to change the number of characters dedicated to an attribute on a single line.

SQL> column username format a20

Now that the columns look good, we can change how frequently the column headings display.  Notice above 11 users are displayed before the headings are displayed again.  Below, once we change the size of the page the results display more users before displaying the headings again.

SQL> set pagesize 20

There is so much more to formatting than what I go into here.  To learn more you can review Oracle documentation that covers the topic very well.

No comments:

Post a Comment

PASS Summit - Day 5

Wakey Wakey My very last morning waking up in Seattle and I finally sleep until my alarm goes off.  It was a bitter sweet morning.  On one...