Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle Advanced SQL Techniques
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Andrew Kerber, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Login Scripts

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.

 

SET TAB OFF

SET VERIFY OFF

SET LINESIZE 132

SET PAGESIZE 999

SET TRIMOUT ON

SET TRIMSPOOL ON

 

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:

 

set_environment.sql script

 

SET TAB OFF

SET VERIFY OFF

SET LINESIZE 132

SET PAGESIZE 999

SET TRIMOUT ON

SET TRIMSPOOL ON

 

show_init_parms.sql script

 

@SET_ENVIRONMENT

 

col name   format a40   heading 'Name'

col value  format a80  heading 'Value'

 

select NAME, VALUE

from v$parameter

where value not like '%\%'

  and value not like '%/%'

  and name not like 'log_archive_dest%'

order by name;

 

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.

 

Figure 5.5:  SQL*Plus using LOGIN.SQL script

 

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.

     


Fo
r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.