Nowadays, so many things we interact with come with a purpose-built GUI that saves our last state.  We take for granted all the modern niceties that make our daily lives easier.  Then something happens, and we are stuck with that blinking cursor and nearly blank screen where we need to manually type in, and remember, all the various commands necessary to keep these systems up and running.

As Oracle database administrators we recognize SQL*Plus is one of the later tools. People seem to pause when they need to use it. Entering their commands, grumbling because the results come back unreadable, issuing the syntax to fix some formatting, then making a single typo that requires you to retype out the whole command again.

Oracle realized that saving off preferences could be helpful but didn’t call much attention to them.

Where to save these changes

I’m going to walk through three preferences you can use to sand off a few of the rough edges of this utility.  These preferences can be saved in one of two files:

• login.sql – If you have an ORACLE_PATH environment variable for Linux or SQLPATH environment variable for Windows set up, SQL*Plus will look in that path to see if this file exists

• glogin.sql – This file exists in $ORACLE_HOME/sqlplus/admin.  It is a global login file and would be executed before the login.sql

Formatting

SQL*Plus was written in a time when your average monitor had less screen real-estate and helped make sure your desk didn’t float off the ground.  Thus, some default settings were decided upon, making us scratch our heads in bewilderment when using current viewpoints.  Thankfully we can adjust those to be something more meaningful to our monitor, or more importantly, SSH application window size.

There are two main settings you will likely always want to set when entering SQL*Plus:

• LINESIZE – This tells SQL*Plus how many characters to place on a line.  The default size is 80.  Max Size is 32,727.  Depending on your preferences, I suggest a happy medium of around 150-200.

• PAGESIZE – This tells SQL*Plus how many lines can be placed on a “page.”  The default size is 14.  Max Size is 50,000 lines.  Page size also includes header information.  Not setting this parameter with enough lines is the usual cause for seeing headers fly by and maybe 1 row of information.  I’d suggest at least 100-200 lines – your current SSH programs can scroll back enough to see the header information.

To add these two settings into your preferences file would require the following line:

SET LINESIZE 200 PAGESIZE 200

You can add many parameters with one SET command or put each parameter on its own line with individual SET commands.

Editing

SQL*Plus never had strong roots in being an editor.  It was meant to be an intermediary that tells the database engine what your needs are.  That stated, I know I make typos.  While there are some built-in editing capabilities in SQL*Plus, they can be painful to use and might cause more issues than they fix.  For Linux, we can use vi, which is admittedly not a WYSIWYG-style editor but is a helpful editor once you learn to work within its constraints.  We can make vi the default editor for SQL*Plus by using one of the two following methods:

Define it at the OS Level:

export EDITOR=vi

Add it to your parameter file:

DEFINE _EDITOR="vi"

Then the next time you want to edit your typo, you can type ED on the command line to invoke the editor, save the file (afiedt.buf) when you exit, and then type: / to rerun the edited buffer.

SQL> DEFINE _EDITOR="vi" 
SQL> seect instance_name from v$instance;
SP2-0734: unknown command beginning "seect inst... " - rest of line ignored.
SQL> ed
Wrote file afiedt.buf

  1* select instance_name from v$instance
SQL> /

INSTANCE_NAME
----------------
mycdb01

Dates and their Time Component

When you query tables, you will notice that any DATE fields will only return data as DD-MON-YY by default.  While useful as a date holder, developers often use this datatype for its additional time component.  We could code all of our SELECT queries to convert the datatype into a CHAR style datatype with our preferred formatting, or we could tell Oracle we want to return all DATE datatype fields in a more helpful format.

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

Now we will be able to see the time portion of the DATE datatype

SQL> select current_date from dual;

CURRENT_D
---------
28-SEP-22

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select current_date from dual;

CURRENT_DATE
-------------------
2022-09-28 15:59:06

Conclusion

With these three items, you can get past some of the pain points of this essential utility. There are many other options you can include, and those can be explored in Oracle’s Documentation for SQL*Plus.

For any other support questions, reach out to our team of experts at House of Brick today.