Oracle PL/SQL – SQL Plus Startup Script with glogin.sql or login.sql

This article shows about how to configure the environment variable when SqlPlus – SqlPlus Command start up in order to have always a good behavior in the formatting of the result.

You have to setup the Windows – Environment Variable (SQL Plus|SQL Developer)- SQLPATH with a directory. Copy then the file login.sql described below in it. This file is execute each time that you connect to a database with SQLPlus.

  • SQLPATH in Windows environment (Windows Oracle server):  HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\
  • SQLPATH in Windows envirinment (Windows Client):
    • Create a Windows Environment Variable (system type) called  SQLPATH and point its path to the location where your login.sql is located (E.g.: C:\app\Tenemaza\product\11.2.0\client_1)

SQLPlus can also run a glogin.sql (global login.sql) which can contain site-wide default settings.

REM print out anything when we log in
set termout off 

REM DBMS_OUTPUT.PUT_LINE set on and as big as possible
set serveroutput on size 1000000 format wrapped 

REM column width
column object_name format a30
column segment_name format a30
column file_name format a40
column name format a30
column file_name format a30
column what format a30 word_wrapped
column plan_plus-exp format a100
column column_name HEADING column_heading
SET UNDERLINE =

REM removing training blanks from spool
set trimspool on

REM default to 80 for LONG or CLOB
set long 5000 

REM default widht at which sqlplus wraps out
set linesize 500 

REM default print column heading every 14 lines
set pagesize 500

REM signature
column global_name new_value gname
set termout off
define sql_prompt=idle
column user_sid new_value sql_prompt 
select lower(user) || '@' || lower('&_CONNECT_IDENTIFIER') user_sid from dual; 
set sqlprompt '&sql_prompt>'

REM sqlplus can now print to the screen
set termout on
One you start running the queries, its output may look like the image below: 
 

Finally, if you don’t like the output of the query, you can change anything within that login file and logout and log back in and start having fun.

, ,

No comments yet.

Leave a Reply

*