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.