How to customize your SQL * Plus Environment

SQL * Plus is simple, powerful, and ubiquitous. What does SQL * Plus mainly do?

1) autotrace

2) Programming

The "best practices" for creating stored procedures in SQL * Plus should be as follows:

create or replace program-typeasyour codeend；/show err

 

SQL * Plus automatically runs glogin. SQL, which contains the global default settings. You can customize your SQL * Plus environment as needed.

1) First, define an environment variable sqlpath in the system environment settings, and set your own environment scripts login. SQL and connect. SQL.
Put them in the sqlpath directory.

2) define a login. SQL script, which is automatically executed when SQL * Plus is enabled.

3) define a script connect. SQL, which is called during identity switching.

To implement this login. SQL script, we need to master some basic SQL * Plus knowledge first.

Reminder: you do not have to remember too many commands.
A] Help Index
B] HELP command
When these two steps are combined, the friendly SQL * Plus will tell you.

1) execute an SQL script
@
@: Execute the nested script. It indicates that the nested script and the host are in the same directory. SQL * Plus will only find the execution file in the current directory (the directory where SQL * Plus is logged in is the current directory), and the two @ will tell him that the directory where one @ is located is the current directory.
Exercise:
Scripts think. SQL and sub_think. SQL are available under $ ORACLE_HOME/DBS.
Think. The content of SQL is: @ sub_think. SQL
Then we can execute:
@ $ ORACLE_HOME/dbs/think. SQL

@ Tell SQL * Plus that $ ORACLE_HOME/dbs is the current directory.
2) edit the current input.
Edit
Generally, the DML, DDL, and DCL statements are SQL * Plus statements. After execution, they are saved in the memory area of the SQL Buffer, and only one recently executed SQL statement can be saved.
3) re-run the statement in SQL Buffer
/
4) output the displayed content to the specified file
Spool filename
/* Anything you do in SQL * Plus */
Spool off
Then you can view it in the file path.
5) COL command
A] Change the default Column Title
Col default Column Title heading Custom column title

SQL> select first_change# from v$log;FIRST_CHANGE#-------------       680280       638906       659033SQL> col first_change# heading thinkSQL> /        think----------    680280    638906    659033SQL> col first_change# heading "go into current's scn"SQL> /go into current's scn---------------------               680280               638906               659033SQL> col first_change# heading "fisrt_change#|go into current"SQL> /  fisrt_change#go into current---------------         680280         638906         659033

B] changing the display length of a column
Col field name for
There is a problem here. We need to pay attention to it. In SQL * Plus, Column Display always starts from the leftmost. In addition, the character values are left aligned, and the numeric values are right aligned.
The following example shows how to use this function.

SQL> select ename,job,deptno from scott.emp where rownum=1;ENAME      JOB           DEPTNO---------- --------- ----------SMITH      CLERK             20SQL> col ename for a20SQL> /ENAME                JOB           DEPTNO-------------------- --------- ----------SMITH                CLERK             20SQL> col deptno for a20SQL> /ENAME                JOB           DEPTNO-------------------- --------- ----------SMITH                CLERK     ##########

C] set the column title alignment

Col field name justify [L | r | C]

SQL> /ENAME      JOB           DEPTNO---------- --------- ----------SMITH      CLERK             20SQL> col job justify cSQL> /ENAME         JOB        DEPTNO---------- --------- ----------SMITH      CLERK             20

D] set the rewinding Method for a column
Col field name for axx
Select the following
Col field name wrapped or
Col field name word_wrapped or

Col field name truncated

SQL> select * from t;TEXT--------------------------------------------------------------------------------I must work hard for my parentsSQL> col text for a6SQL> /TEXT------I must workhard for myparentsSQL> col text word_wrappedSQL> /TEXT------ImustworkhardformyparentsSQL> col text truncatedSQL> /TEXT------I must

E] display the current attribute value of a column
Col field name

SQL> col textCOLUMN   text ONFORMAT   a6truncate 

F] Clear all columns as default values
Clear Columns

SQL> clear colcolumns clearedSQL> col textSP2-0046: COLUMN 'text' not defined

6) set command

A] set whether the current session automatically submits the modified data

SQL> set auto onSQL> update t set text='think big for my future';1 row updated.Commit complete.SQL> select * from t;TEXT--------------------------------------------------------------------------------think big for my futureSQL> rollback;Rollback complete.SQL> select * from t;TEXT--------------------------------------------------------------------------------think big for my futureSQL> set auto off

B] whether to display the number of rows queried or modified by the current SQL statement
Set feed [Back] {6 | n | on | off}
By default, only rows with more than 6 results are displayed. If set feedback 1 is returned, no matter how many rows are queried. When it is off, the number of queried rows is not displayed.

SQL> select * from t;TEXT--------------------------------------------------------------------------------think big for my futureSQL> set feed 1SQL> /TEXT--------------------------------------------------------------------------------think big for my future1 row selected.

C] whether to display the output information using the dbms_output.put_line package.
Set serverout [put] {on | off}

SQL> exec dbms_output.put_line('think');PL/SQL procedure successfully completed.SQL> set serveroutput onSQL> exec dbms_output.put_line('think');thinkPL/SQL procedure successfully completed.

D] display the execution time of each SQL statement

set TIMING {ON|OFF}

E] When an empty row is encountered, the statement is not considered to have ended. It is then read from the subsequent rows.
Set sqlblanklines on

In SQL * Plus, empty rows in the middle of SQL statements are not allowed, which is troublesome when copying scripts from other places to SQL * Plus. for example, the following Script: Select deptno, empno, enamefrom EMP/* I am a blank line */where empno = '000000'; if it is copied to SQL * Plus for execution, an error will occur. This command can solve this problem

7) operations on data in SQL Buffer
A] modify
C/old value/New Value

QL> L 1 * select * From tsql> C/select/update 1 * update * From tsql> L 1 * update * From tsql> C/from/think 1 * update * think tsql> L 1 * update * think t B] edit editsql> editwrote file afiedt. buf 1 * update * think T

C] display
List n displays the nth row in SQL Buffer and makes the nth row the current row.

SQL> update t  2  set text='think';1 row updated.SQL> l  1  update t  2* set text='think'SQL> l 2  2* set text='think'

D] Save the statements in SQL Buffer to a file.

SQL> save /home/oracle/t.sqlCreated file /home/oracle/t.sql

E] import SQL statements in a file into SQL Buffer

get file_name

8) run the SQL * Plus command when you enter an SQL statement.
Start another line, # hitting the header

SQL>> select deptno, empno, ename2 from emp3 where6 #desc empName Null? Type----------------------------------------- -------- --------------EMPNO NOT NULL NUMBER(4)ENAME VARCHAR2(10)JOB VARCHAR2(9)MGR NUMBER(4)HIREDATE DATESAL NUMBER(7,2)COMM NUMBER(7,2)DEPTNO NUMBER(2)6 sal > 4000;DEPTNO EMPNO ENAME---------- ---------- ----------10 7839 KING

The basics have been introduced here. We recommend that you refer to the SQL * Plus guide on the Oracle official website. Next we will use Tom's login. SQL to introduce the idea of configuring SQL * Plus in a personalized way.

1. Edit the login. SQL file:
Rem Disables any output and ensures that no information is displayed during user login.
Set termout off
The default editor for REM definition is vim.
Define _ editor = vi
Rem sets the size of the output buffer.
Set serveroutput on size 1000000 format wrapped
Rem sets the default 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
Set trimspool on
Rem defines the size of the long data type.
Sets long 5000
Rem defines the length of a row.
Set linesize 131
Rem defines page size
Set pagesize 9999
Rem definition prompt
Define gname = idle
Column global_name new_value gname
Select lower (User) | '@' |
Substr (global_name, 1, decode (dot,
0, length (global_name ),
Dot-1) global_name
From (select global_name, instr (global_name, '.') dot
From global_name );
Set sqlprompt '& gname'
Rem settings display system time
Set Time on
Rem Displays output again
Set termout on
2. Edit the connect. SQL File
Set termout off
Connect & 1
@ Login
===================================== Login. SQL = ============
Set termout off
Define _ editor = vi
Set serveroutput on size 1000000 format wrapped
Column object_name format A30
Column segment_name format A30
Column file_name format A40
Column whar format A30 word_wrapped
Column plan_plus_exp format a100
Set trimspool on
Sets long 5000
Set linesize 100
Set pagesize 9999
Define gname = idle
Column global_name new_value gname
Select lower (User) | '@' | substr (global_name, 1, decode (dot, 0, length (global_name), dot-1) global_name from (select global_name, instr (global_name ,'. ') Dot from global_name );
Set sqlprompt '& gname'
Set Time on
Set termout on