SQL Plus - Startup Script

Card Puncher Data Processing

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.

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

glogin.sql

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

@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

Reference





Discover More
Sqldeveloper Sqlpath
(SQL Plus|SQL Developer)- SQLPATH

Setting up the SQLPATH environment variable in the windows environemnt tell to search the file in this directories. The startup files (login.sql,...) must be located in this directories. 1524210.1Bug:...
Card Puncher Data Processing
(SQL Plus|SqlCl) (Oracle Database Console)

SQLPlus is the first client of the Oracle Database. On windows, there is two versions of SQLPlus : a GUI one (sqlplusw.exe) Officialy deprecated in the next release of Oracle.Documentation...
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Oracle Database 12c Step 12
Oracle Database - 12c Installation on Windows 64 bit

Installation of Oracle Database 12c (12.1.0.1.0) on Windows. You can download the oracle software from OTN or better...
Install 10g 8 End Of Installation
Oracle Database - Installation Oracle Database 10gR2 on Windows

Installation of the database software Installation of the software patch Configuration of the listener Creation of a database instance Download the installation file : Oracle...
Install Oradb 11g Screen 3 Install Location
Oracle Database - Installation of 11gR1/R2 on Windows

Installation of Oracle Database 11g on Windows. You can download the oracle software from OTN or better the latest patch...
Card Puncher Data Processing
SQL Plus - Configuration

See: Buffer configuration Configuring SQLPlus Tns Names Configuration
Card Puncher Data Processing
SQL Plus - Editor

The dynamic substitution variable _EDITOR gives the current editor You use it in conjunction with the edit function. You must define it in an startup script
Card Puncher Data Processing
SQL Plus - Formatting Reports

How to format SQLplus output (ie ) All below parameters can be configured in the login script. Through the SQLPlus COLUMN command with the heading options, you can change the column headings...



Share this page:
Follow us:
Task Runner