:

SQL Plus Configuration – Login.sql

sqlplus-alter-session-on-login

There are a number of SQL Plus environment settings which can be altered for various purposes; the main one being to manipulate the display settings in order to make your job easier.

SQL Plus Settings

Let’s take an example to demonstrate what I mean. I’m sure that you are familiar with logging on to your database with a SQL Plus command line window. I hope you are because I believe it’s very important to understand how to interact with the database at the most basic level, without GUIs, but that’s a different story. Anyway, I digress…

So, you log on with SQL Plus and you issue a SELECT statement. There are a few hundred rows returned, but the output is not readable very easily. How do you fix this? Well, the first two things I would do are to set the linesize and pagesize settings so that more is displayed on my screen. You would do this by issuing the following commands:

SET LINES 120
SET PAGES 1000

These two commands are shorthand for LINESIZE and PAGESIZE and it changes the length of each line to 120 characters and will repeat headings every 1000 rows.

Making SQL Plus Changes Permanent

If, like me, you log on and off of different environments a lot of times each day you would rather not have to type these types of commands out each time you log on to a database environment. The good news is you don’t have to. You can add all the SQL System Variable commands you like to one file so it is executed each time you start a SQL Plus session.

sqlplus-login-change-parameters

Settings in login.sql, read below for full details

Follow these steps to create your own file of SQL Plus system variables:

  • Create a file called login.sql and insert all the commands you want to include¬†in it (full list of variables from Oracle documentation)
  • Save the file as “login.sql” and add it to a folder, anywhere you like
  • Right click My Computer -> Properties -> Advanced System Settings -> Environment Variables
  • Add new “system variables” variable called “SQLPLUSPATH” if it does not already exist
  • Put the full path to your freshly created login.sql file as the value and save it
windows-environment-variable-sqlpluspath

Windows environment variables

That’s it. Pretty simple really and it will save you a lot of time if you were used to typing out the commands each time that you wanted to alter the appearance of your SQL Plus window.

Note: Be careful when using the login.sql file because if you are running script from the machine where you have one of these in place which will later be run against another database, like your production how to take ativan environment where you don’t have the same login.sql file, it may cause issues with the scripts and change the way they run. I do not use a login.sql script on any servers where scripts and jobs run from, only on my desktop so the change applies to me only.

Like it, share it...

Category: SQL Plus


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *