SQL Plus - Startup Script

> Procedural Languages > (SQL Plus|SqlCl) (Oracle Database Console)

1 - About

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

Advertising

3 - login.sql

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.

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

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 149 

REM default print column heading every 14 lines
set pagesize 9999

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

4 - glogin.sql

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

5 - @connect

Sqlplus run the login.sql once at startup. If you reconnect, the prompt doesn't change, and some settings are reset (example: serveroutput). To resolve this issue, use @connect to fire the file connect.sql below :

set termout off
connect &1
@login

6 - Reference

lang/sqlplus/startup_script.txt · Last modified: 2017/05/09 16:51 by gerardnico