Finding and Setting SQL*Plus Settings

SQL*Plus offers several handy features you can use to control your SQL*Plus session. The SHOW command works the same in both UNIX and Windows environments, and SHOW ALL can be used to display the settings or values of nearly 70 items. Instead of just showing these settings, SQL*Plus offers a way to quickly set and customize them. If you have written a script to produce output from a query, chances are pretty good you have also added one or more SET commands to format the output. Short of logging out and restarting SQL*Plus, or taking the time to reset all of the SET commands, is there an easier way to restore your favorite or preferred session settings? The good news is that the answer is yes, and even better news is that this is very easy to perform.

One Step Beyond SET: Using STORE

From the SQL*Plus® User’s Guide and Reference (Release 10.2) guide, the purpose of the STORE command is to save “attributes of the current SQL*Plus environment in a script.” Start up SQL*Plus and then immediately enter STORE SET defaultenv.sql at the SQL prompt. STORE and SET are capitalized only to point out their special meaning, otherwise, case does not matter for these words. If on UNIX, the case of “defaultenv” does matter. Is the “dot sql” at the end necessary? Using a text editor, let’s open defaultenv.sql and see.

The first thing you should notice is that every line in the file is preceded by a “set” command. Around line 50, the value for “suffix” is “sql.” Given that the default suffix is “sql,” then all that was need before is simply “STORE SET defaultenv.” One other difference that may stand out between the output of show all and the contents of defaultenv is the number of lines. The differences (virtually all are in what SHOW ALL displays) are shown below.

appinfo is OFF and set to “SQL*Plus”

blockterminator “.” (hex 2e)

btitle OFF and is the first few characters of the next SELECT statement

concat “.” (hex 2e)

define “&” (hex 26)

FEEDBACK ON for 6 or more rows

headsep “|” (hex 7c)

instance “local”

lno 26

loboffset 1

pno 1

recsepchar ” ” (hex 20)

release 1002000100

repfooter OFF and is NULL

repheader OFF and is NULL

sqlcode 0

sqlprefix “#” (hex 23)

sqlterminator “;” (hex 3b)

ttitle OFF and is the first few characters of the next SELECT statement

underline “-” (hex 2d)

USER is “SYSTEM”

wrap : lines will be wrapped

Some of the differences are informational in nature. All of the settings with “(hex xx)” are the same, except that SHOW ALL displays the value’s corresponding hex value (how does that help?).

If you can store your favorite SQL*Plus settings in a separate file, why not re-purpose your login.sql file by adding all of the non-default settings you prefer? You certainly can, but if your login.sql script outputs anything (I have mine output the user and name of the database), you will get that output as well. A practical reason to use a separate settings file is to keep reports versus non-reports settings separate (one script sets them and the other unsets them).

If in an extract, transform, and load (ETL) environment, it is very common to spool out the contents of a table (or query) into a delimited file. Six SET commands can be used to produce a “clean” header and footer for an output file.

set pagesize 0

set feedback off

set trimspool on

set linesize 1000

set heading off

set termout off

To reset your session settings, you can explicitly reset these parameters in a script, but that introduces unnecessary code and maintenance effort. Create a SQL file (defaultenv or a one off “reset” file) with the normal values set, and then call it at the SQL prompt.

Extending What STORE Offers

As an extension to what STORE offers with respect to settings, you can also add in column format settings (i.e., “col object_name format a20”). Personally, I do not ever see myself using 513 characters for a file name (describe dba_data_files and note the length of file_name), so that is one column I “set” in an environment file (either in login.sql or a settings file).

Another extension is to DEFINE what is defined. At a SQL*Plus prompt, enter DEFINE by itself.

Don’t like Notepad as your editor? Change it to your favorite text editor, and while at it, dump afiedt.buf as the file name (something more suggestive such as “working.sql” via “set editfile working.sql”). Notepad is hard to beat in terms of easy start/easy exit and return to SQL*Plus, but for any serious editing, you may want to start with a more heavy-duty editor.

Restricting Access to Settings

What can be set can be reset, unless you (the DBA) prevent users from doing so. In addition to restricting users via product_user_profile, you can restrict users from performing certain SQL*Plus commands via the RESTRICT option invoked upon startup of SQL*Plus.

The SQL*Plus reference guide description of RESTRICT is shown below.

Use of the RESTRICT option would be appropriate in PC/desktop environments where users do not have local admin rights on their computers, and other security measures are required such as when a company works with a financial institution and user access is not only audited, but severely restricted. If working under a level 3 restriction, and users cannot run scripts because START (which includes RUN, @ and @@), nothing (other than roles, etc.) prevents a user from pasting a script directly into a SQL*Plus session window.

The only real value of restricting the running of scripts in this manner is to prevent a user from running known scripts, but if the contents of a script can be divined (e.g., generate an employee salary report), other security measures are still required.

In Closing

From a security standpoint, the user interface – SQL*Plus in this example – into an application or database is much like the line of contact between friendly (you, the DBA) and enemy (users, and well, toss in developers for good measure) forces. You have to balance giving a user some degree of flexibility in customizing his SQL*Plus environment, but at the same time, your company’s security needs have to come first. As a compromise, you may be the person responsible for setting or creating customized settings files, and knowing how to create these files via the STORE command, in addition to knowing how to restrict access to these files and other commonly used scripts, can make this juggling act easier to perform.

» See All Articles by Columnist Steve Callan