How to customize your SQL * plus Environment

How to customize your SQL * plus environment SQL * plus is simple, powerful, and ubiquitous. What does SQL * plus mainly do? 1) autotrace 2) programming SQL * plus will automatically run glogin. SQL, which contains the global default settings. You can customize your SQL * plus environment as needed. Www.2cto.com 1). First, define an environment variable sqlpath in the system environment settings, and put your own environment setting scripts login. SQL and connect. SQL under the SQLPATH directory respectively. 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] The help command combines the two steps, and 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. Exercise: scripts think. SQL and sub_think. SQL are available under $ ORACLE_HOME/dbs. Think. the SQL statement is: @ sub_think. SQL, then we can execute: @ $ ORACLE_HOME/dbs/think. SQL 2) edit the current input. Generally, DML, DDL, and DCL are SQL * plus statements. After execution, they are saved in the memory area of SQL buffer, you can only save one of the most recently executed SQL statements. 3) re-run the SQL buffer statement/www.2cto.com 4) output the displayed content to the specified file spool filename/* anything you do in SQL * plus */spool off and 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] SQL> select first_change # from v $ log; FIRST_CHANGE # ------------- 680280 638906 659033 SQL> col first_change # heading think SQL>/www.2cto.com think -------- 680280 638906 659033 SQL> col first_change # heading "go into current's scn" SQL>/go into current's scn ------------------- 680280 638906 659033 SQL> col first_change # heading "fisrt_cha Nge # | go into current "SQL>/fisrt_change # go into current --------------- 680280 638906 659033 B] changing the display length of a column. The col field name for a is incorrect. 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] SQL> select ename, job, deptno from scott. emp where rownum = 1; www.2cto.com ename job deptno ---------- ------------ smith clerk 20 SQL> col ename for a20 SQL>/ename job deptno certificate --------- -------- smith clerk 20 SQL> col deptno for a20 SQL>/ENAME JOB DEPTNO -------------------- --------- ---------- smith clerk ########### c] set the column title Alignment Method col field name justify [L | R | C] [SQL] SQL>/www.2cto.com ename job deptno ---------- --------- ---------- SMITH cler20 SQL> col job justify c SQL>/ENAME JOB DEPTNO -------- --------- -------- SMITH cler20 d] set the rewinding method of a column col field name for axx select the following col field name wrapped, col field name word_wrapped, or col field name truncated [SQL] SQL> select * from t as needed; TEXT -------------------------------------------------------------------------------- I must w Ork hard for my parents SQL> col text for a6 SQL>/TEXT ------ I must work hard f or my parent s SQL> col text word_wrapped SQL>/www.2cto.com TEXT ------ I must work hard for my parent s SQL> col text truncated SQL>/TEXT ------ I must e] displays the current attribute value of the COLUMN col field name [SQL] SQL> col text COLUMN text ON FORMAT a6 truncate f] clear all columns as default clear columns [SQL] SQL> clear col columns cleared SQL> col text SP2-0046: C OLUMN 'text' not defined 6) set command a] set whether the current session automatically submits the modified data [SQL] SQL> set auto on SQL> update t set text = 'think big for my ure '; 1 row updated. commit complete. SQL> select * from t; www.2cto.com TEXT ---------------------------------------------------------------------------- think big for my future SQL> rollback; Rollback complete. SQL> select * from t; TEXT ---------------------------- -------------------------------------------------- Think big for my future SQL> 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} the number of rows in the result is displayed only when there are 6 rows. 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] SQL> select * from t; TEXT when think big for my future SQL> set feed 1 SQL>/www.2cto.com TEXT when think big for my future 1 row selected. c] whether to display the output information using the DBMS_OUTPUT.PUT_LINE package. Set serverout [PUT] {ON | OFF} [SQL] SQL> exec dbms_output.put_line ('think'); PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> exec dbms_output.put_line ('think'); think PL/SQL procedure successfully completed. d] display the execution time of each SQL statement [SQL] set TIMING {ON | OFF} e] When an empty row is encountered, it is not considered that the statement has ended. It is then read from subsequent lines. In set sqlblanklines on [SQL] SQL * plus, empty rows in 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, ename from emp/* I am a blank line */where empno = '000000'; if it is copied to SQL * plus for execution, an error occurs. This command can solve this problem 7) operations on data in SQL buffer a] modify c/old value/new value [SQL] QL> l 1 * select * from t SQL> c/select/update 1 * update * from t SQL> l 1 * update * from t SQL> c/from/think 1 * update * think t SQL> l www.2cto.com 1 * update * think t B] edit SQL> edit Wrote file afiedt. buf 1 * update * think t c] display list n display the nth row in SQL buffer, and the nth row becomes the current row [SQL] 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] SQL> save/home/oracle/ t. SQL Created file/home/oracle/t. SQL e] import SQL statements in a file into SQL buffer [SQL] get file_name 8) run an SQL * plus command to start another line during SQL statement input, # hitting [SQL] SQL> select deptno, empno, ename 2 from emp 3 where 6 # desc emp Name Null? Type verification -------- -------------- empno not null number (4) ENAME VARCHAR2 (10) JOB VARCHAR2 (9) mgr number (4) hiredate date sal number (7,2) comm number (7,2) deptno number (2) 6 sal> 4000; deptno empno ename ---------- ------------ 10 7839 KING basics this article is complete. 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 personalized configuration of SQL * plus www.2cto.com 1. Edit login. SQL file: REM Disables any output, make sure that no information is displayed during user logon. set termout offREM defines the default editor as vimdefine _ editor = viREM. set serveroutput on size 1000000 format wrappedREM sets the default column width column object_name format. a30column segment_name format a30column file_name format a40column name format a30column file_name format a30column what format a30 word_wrappedcolumn layout format a100set trimspool onREM defines the size of the set long 5000REM row displayed for long data type data. set linesize define REM define page size set pagesize 9999REM define prompt define gname = idlecolumn global_name new_value gnameselect lower (user) | '@' | substr (global_name, 1, decode (dot, 0, length (global_name), dot-1) global_namefrom (select global_name, instr (global_name ,'. ') dot from global_name); set sqlprompt' & gname' REM settings display system time set time onREM again display output set termout on2, edit connect. SQL file set termout offconnect & 1 @ login ================================ login. SQL = ========== set termout offdefine _ editor = viset serveroutput on size 1000000 format wrappedcolumn object_name format a30column segment_name format a30column file_name format a40column whar format a30 bytes formatted a100set trimspool on www.2cto.com set long 5000 set linesize 100 set pagesize 9999 define gname = idlecolumn global_name new_value gnameselect 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 onset termout on author linwaterbin