Friday, February 12, 2010

SQL*Plus Configuration Settings

I run sql*plus in text mode a lot. You can always count on it being there, the functionality is consistent from version to version, and it doesn't require a lot of infrastructure to be running before it can successfully connect.
But there are a few settings I want to have set up on startup. This is reasonably straightforward, just use a script file that contains your preferences, and call it as part of startup.
So here are my oracle settings (stored in a file orasettings.sql):

set termout off
set linesize 140
set pagesize 1000
set long 5000
set timing on
column D22 new_value VAR
select lower(username) || '@' || '&_CONNECT_IDENTIFIER> ' D22 from user_users;
set sqlprompt '&&VAR'
set sqlnumber on

col text format a80 word_wrapped
col st_sectiontext format a80 word_wrapped

set termout on


Now, for how to call it. From the OS command processor (i.e., windows "cmd" command prompt):
sqlplus username@service @orasettings
The other way to "reconnect" is to "modify" the connect command using a script file "conn.sql":

connect &1
@orasettings


and then from a SQL> prompt, you execute
SQL> @conn username@service
enter password:
connected.
username@service>

The last trick, at least on Windows, is to make sure these two files are placed in the location that sqlplus is looking for. That location is stored in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\\SQLPATH.
I usually add the root "c:\oracle" as the sqlpath and store the files there, but that's mainly because I have multiple accounts with little in the way of consistency as to paths and locations I can store these things, and I have little interest in figuring out some clever way to remember where to look.
Even this post is meant more for me to be able to grab the settings I use from anywhere in the world than to enlighten the masses. But feel free to use it if you want.