SQLPlus session formatting script

In spite of the many GUI-based tools now available to developers working with Oracle, it is still sometimes necessary to use the SQLPlus prompt. A properly configured session helps a lot to make your interaction more pleasant and readable. SQLPlus will automatically execute any script named login.sql that resides in the user’s launching directory. However, I frequently find myself moving around directories to more easily reference scripts on disk. That’s why I keep a file in my root directory with a short name. Then, after launching SQLPlus on Windows, I simply need to type @C:ses (notice a backslash is not needed) to set up my session. Below is an example of a session configuration I like to use:

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

SET SERVEROUTPUT ON     -- Show output from PL/SQL
SET TRIMSPOOL ON        -- When spooling to file, trim trailing whitespace on all lines
SET PAGESIZE 40000      -- Maximum available pagesize, setting to zero will also erase headers
SET LINESIZE 200        -- Line length of 200
SET LONG 40000          -- Visible long length matches pagesize
SET LONGCHUNKSIZE 200   -- Visible long chunk matches line length
SET SQLPROMPT "_user'@'_connect_identifier> "
SET TIMING ON           -- Show runtimes of all commands
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s