login.sql file tips

 

This section will be very brief, but highlights a very useful SQL*Plus facility that can often make both SQL*Plus interactive sessions and scripting less difficult. Note too that some of the prior mentioned tools such as Toad and SQL Developer support this SQL*plus feature, although with some minor differences based upon Oracle version. For instance, prior to Oracle 11g, the login.sql script fired only upon startup. Whereas in 11g and Toad, it fires up each time a script is executed.

 

As is covered in the following sections, there will be times when SQL*Plus will be used and issue a common set of commands over and over again. Often these commands will be those that help define the environment and/or behavior for SQL*Plus during that session. For example, the six SQL*Plus commands below might well represent commands that a user wants active anytime they enter SQL*Plus or execute a SQL script.

 

 

Obviously, typing these six commands each time SQL*Plus is entered is not an optimal solution. What about adding those six lines to the top of each of the scripts? Actually, that is also not good because if the commands are changed, then the next step will be to edit all of the scripts. The most logical idea is to simply place those six lines within their own little script set_environment.sql, and then simply call that script at the top of all the other scripts, such as this:

 

 

 

 

 

But guess what? SQL*Plus already has a built-in mechanism to do this!

 

SQL*Plus supports the concepts of automatically executed user and site profile scripts. These are simply SQL script files containing either SQL or SQL*Plus commands that are executed automatically whenever SQL*Plus is launched. Think of these files as being like Microsoft DOS autoexec.bat files or a UNIX login.sh script. These user and site profile scripts are named login.sql and glogin.sql, respectively.

 

When SQL*Plus first starts, it simply looks for and executes these scripts in the working directory or SQL_PATH. That is all there is to it. Here is a simple example where SQL*Plus prints �Welcome to SQL*Plus� and lists both the current database SID and connected user name. Figure 5.5 shows the login.sql script and its effect. Notice how the script is executed automatically upon launch. So in many cases, one can simply rely upon the login.sql script behavior rather than writing and then calling a new script such as set_environment.sql.

  

As to the difference between login.sql and glogin.sql: SQL*Plus provides two automatic script environmental setup options. Login.sql is for a particular user and is loaded from one of their local directories, whereas glogin.sql is more for departmental or enterprise wide standards and usually kept on shared file servers.

Oracle Training from Don Burleson  The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:   and include the URL for the page.                      Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services   Copyright © 1996 -  2020 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation.