The use of the Sql*plus command

                                sql*plus Command usage            Oracle's Sql*plus is a client tool that interacts with Oracle. In Sql*plus, you can run the Sql*plus command and the Sql*plus statement.     Our usual DML, DDL, DCL statements are sql*plus statements that, when executed, can be stored in an area of memory called SQL Buffer and can only save a recently executed SQL statement that we can save on a SQL The SQL statements in buffer are modified and then executed again, and Sql*plus generally deal with the database.     In addition to sql*plus statements, other statements executed in Sql*plus we call the Sql*plus command. After they have been executed, there is no memory area for SQL buffer, and they are typically used to format the results of the output to make the report available.     Here are some common Sql*plus commands:   1. Execute a SQL script file Sql>start file_name sql>@ file_name We can save multiple SQL statements in a text file so that when you execute all the SQL statements in this file, use any of the above commands. This is similar to the batch processing in DOS.   2. Edits the current input Sql>edit   3. Rerun the last run SQL statement sql>/  4. Outputs the displayed content to the specified file sql> SPOOL file_name     All content on the screen is included in the file, including the SQL statement you entered.   5. Turn off SPOOL output sql> SPOOL off     only close SPOOLOutput to see the output in the output file.   6. Displays the structure of a table sql> desc table_name   7. Col command: The main format of the column's display form. The command has many options, as follows: Col[umn] [{column|expr} [option ...]] Option options can be the following clauses: Ali[as] alias Cle[ar] fold_a[fter] fold_b[efore] for[mat] format hea[ding] text Jus[tify] {l[eft]| c[enter]| c[entre]| R[ight]} like {Expr|alias} Newl[ine] new_v[alue] variable nopri[nt]| PRI[NT] nul[l] text Old_v[alue] variable on| Off wra[pped]| wor[d_wrapped]| Tru[ncated]   1). Change default column header column column_name HEADING column_heading for Example:sql>select * from dept;      deptno dname                         loc-----------------------------------------------          accounting                   NEW YORK sql>col & nbsp LOC Heading Location Sql>select * from dept;     DEPTNO dname   &nbsP                    location------------------------ ------------------------        10 accounting         & nbsp         NEW YORK   2). Change the column name ename to the new column name Employee name and place the new column name on two lines: Sql>select * from emp department  name            salary------------------------------         10 aaa  & nbsp;             11         SQL> COLUMN ename H Eading ' employee| Name ' Sql>select * from emp             employee department  name& nbsp          salary------------------------------           10 aaa              &nbSp One note:the col heading turn into two lines to one line. &NBSP;&NBSP;3). Change the display length of the column: For[mat] format sql>select empno,ename,job from EMP;       empno ename      JOB        ------------- -------     ---------       7369 smith      CLERK  & nbsp;          7499 allen      SALESMAN   7521 ward  & nbsp;    salesman   Sql> col ename format A40       empno ename  &NBSP;&NB Sp                            & nbsp;   job----------   ----------------------------------------          ---------       7369 smith              &n bsp;                     clerk        7499 allen                             & nbsp;      salesman        7521 ward                                      Salesman   4). Set the alignment of column headings jus[tify] {l[eft]| c[enter]| c[entre]| R[ight]} sql> Col ename justify Center sql>/      EMPNO            ENAME                   JOB----------  & nbsp;----------------------------------------      ---------       7369 smith                                    clerk        7499 allen                                & nbsp;   salesman 7521 ward                                      salesman for number-type columns, the column headers default to the right, Other types of column headings default to the left   5. Do not let a column appear on the screen nopri[nt]| Pri[nt] sql> col job noprint sql>/      EMPNO          &nbs P ename----------     ----------------------------------------       7369 SMITH        7499 ALLEN 7521 WARD   6). Format number Type column display: sql> column SAL format $99,990 sql>/Employee Department name       &nbsp ; salary    Commission---------------------------------------30          allen        $1,600  & nbsp &NBSP;&NBSP;7). When the column value is displayed, if the column value is a null value, replace the null value with the text value COMM nul[l] text sql>col COMM nul[l] text   8). Sets the wrapping style for a column wra[pped]| wor[d_wrapped]| Tru[ncated]         col1--------------------how ARE?   sql>col COL1 FORMAT A5 sql>col COL1 wrapped COL1-----How A RE YO U?   SQL> COL COL1 word_wrapped COL1-----How to ARE you?   SQL> COL COL1 word_wrapped COL1-----How A   9). Displays the current display property value of the column sql> column column_name   10). Set the display property of all columns to the default value sql> clear COLUMNS   8. Masks the same value shown in a column break on Break_column sql> break on DEPTNO sql> SELECT DEPTNO, ename, SAL from EMP   where SAL < 2500   order by DEPTNO; deptno      ENAME         sal------------------------------10           CLARK        2450 miller      1300 20            SMITH       adams       1100   9. In the display of the same values shown in a column above, insert n blank rows whenever the value of the column changes before the value changes. Break on break_column skip \   SQL> Break on DEPTNO Skip 1 sql>/DEPTNO ename SAL--------------------- ---------CLARK 2450 MILLER 1300   20 SMITH ADAMS 1100   10. Displays the settings for the break sql> break   11. Remove the settings for 6, 7 sql> clear BREAKS   12. Set command: This command contains a number of subcommand commands: Set system_variable value system_variable value can be one of the following: appi[nfo]{on| Off|text} Array[size] {15|n} auto[commit]{on| Off| Imm[ediate]|n} Autop[rint] {on| Off} AutoRecovery [on| OFF] Autot[race] {on| Off| Trace[only]} [Exp[lain]] [stat[istics]] blo[ckterminator] {. | C} Cmds[ep] {; |c| on| Off} colsep {_|text} com[patibility]{v7| v8| NATIVE} Con[cat] {. | c| on| Off} Copyc[ommit] {0|n} copytypeCHECK {on| Off} Def[ine] {&|c| on| Off} DESCRIBE [DEPTH {1|n| All}][linenum {on| off}][indent {on| Off}] ECHO {on| Off} Editf[ile] File_name[.ext] emb[edded] {on| Off} Esc[ape] {\|c| on| Off} Feed[back] {6|n| on| Off} flagger {off| ENTRY | intermed[iate]| Full} Flu[sh] {on| Off} hea[ding] {on| Off} Heads[ep] {| | c| on| Off} INSTANCE [instance_path| Local] lin[esize] {80|n} Lobof[fset] {n|1} logsource [pathname] LONG {80|n} Longc[hunksize] {80|n} mark[up] HTML [on| OFF] [head text] [body text] [Entmap {on| Off}] [SPOOL {on| Off}] [Pre[format] {on| OFF}] newp[age] {1|n| NONE} NULL text Numf[ormat] format num[width] {10|n} Pages[ize] {24|n} Pau[se] {on| Off|text} recsep {wr[apped]| ea[ch]| Off} recsepchar {_|c} Serverout[put] {on| OFF} [SIZE n] [For[mat] {wra[pped]| Wor[d_ wrapped]| Tru[ncated]}] shift[inout] {vis[ible]| Inv[isible]} Show[mode] {on| Off} Sqlbl[anklines] {on| Off} Sqlc[ase] {mix[ed]|lo[wer]| Up[per]} Sqlco[ntinue] {> |text} Sqln[umber] {on| Off} Sqlpre[fix] {#|c} Sqlp[rompt] {sql>|text} sqlt[erminator] {; |c| on| Off} Suf[fix] {sql|text} TAB {on| Off} Term[out] {on| Off} Ti[me] {on| Off} Timi[ng] {on| Off} Trim[out] {on| Off} Trims[pool] {on| Off} Und[erline] {-|c| on| Off} Ver[ify] {on| Off} Wra[p] {on| Off}   1). Sets whether the current session automatically submits modified data Sql>set Auto[commit] {on| Off| imm[ediate]| n}   2). Whether to display the SQL statement being executed in the script sql> SET ECHO {on| When executing a SQL script with the start command Off}   3). Whether to display the current SQL statement query or modify the number of rows sql> SET Feed[back] {6|n| on| OFF}     defaults to the number of rows that show results only if the result is greater than 6 rows. If set feedback 1, it is returned regardless of how many rows the query is to. When off, the query's number of rows is not displayed   4). Display column headers sql> set hea[ding] {on| Off} When set heading off, column headings are not displayed on top of each page, instead of   5 in blank lines. Sets the number of characters that can be accommodated sql> set Lin[esize] {80|n}     if one row The output is greater than the number of characters that can be accommodated on a set line, the row is displayed. &NBSP;&NBSP;6). Set the separation between pages and sql> set Newp[age] {1|n| NONE} When set newpage 0 o'clock, there is a small black box at the beginning of each page. When set NewPage n, it is separated by n empty rows between pages and pages. When set newpage none, there is no spacing between pages and pages.   7). When displayed, use the text value instead of the null value sql> set NULL text   8). Set the number of rows for a page sql> set pages[ize] {24|n} If set to 0, theSome output is one page and the column header   9 is not displayed. Whether to display with Dbms_output. Put_Line the package for output information. Sql> SET Serverout[put] {on| off}   when writing stored procedures, we sometimes use dbms_output.put_line to output the necessary information to debug stored procedures, which can be displayed on the screen only if the Serveroutput variable is set to ON. &NBSP;&NBSP;10). When the length of the SQL statement is greater than linesize, the SQL statement is intercepted when it is displayed. Sql> SET Wra[p] {on| Off}     When the length of the output row is greater than the length of the set line (set linesize n command), when set wrap on, more characters from the output line are displayed on a separate line, otherwise, the output line is removed more than characters and is not displayed. &NBSP;&NBSP;11). Whether to display the output content on the screen, mainly used in conjunction with the spool. Sql> SET Term[out] {on| Off}     When you export content from a large table to a file using the spool command, the output is time-consuming on the screen, and after set Termspool off, the output is saved only in the output file and not on the screen. Greatly improved the speed of the spool. &NBSP;&NBSP;12). Remove the extra space after each line in the spool output sql> SET trims[out] {on| off}      13 shows the execution time spent by each SQL statement set timing  {on| Off}   14. Modifies the current row in SQL buffer, the first occurrence of the string C[hange]/old_value/new_value sql> l    1* select * FROM dept sql> c/dept/emp   &NBSP;1* SELECT * from emp   15. Edit SQL statement edi[t]   16 in SQL buffer. Displays SQL statements in SQL buffer, List n shows SQL buffThe nth row in the ER and make the nth row the current row l[ist] [n]   17. Add one or more lines below the current line of SQL Buffer I[nput]   18. Adds the specified text to the current line behind SQL buffer a[ppend] sql> Select Deptno,    2  dname    3  from Dept      deptno dname------------------------         10 ACCOUNTING & nbsp        20,          30 SALES      &NBSP;&NB Sp  40 OPERATIONS   SQL> L 2    2* dname sql> A, loc    2* dname,loc sql> L &nbsp ;  1  select Deptno,    2  dname,loc    3* from dept sql>/  & nbsp    deptno dname          LOC-------------------------------------          10 accounting     new YORK         & Nbsp;20 research       DALLAS         30 sales          CHICAGO           40 operations     boston   19. Save the SQL statement in SQL buffer to a file save file_name   20. Import SQL statements from one file into SQL buffer get file_name   21. Execute the SQL statement run or/  22 that has just been executed again. Executes a stored procedure execute procedure_name   23. In Sql*plus, connect to the specified database connect User_name/passwd@db_alias   24. Sets the top caption for each report Ttitle   25. Set the tail caption Btitle   26 for each report. Write a comment remark [text]   27. Outputs the specified information or a blank line to the screen PROMPT [text]   28. Pauses the execution of the procedure, waits for the user to respond, and then executes PAUSE [text]   sql>pause Adjust paper and press RETURN to continue.   29. Copy some data from one database to another (such as copying data from one table to another) copy {from database | to database | From database to database} {append| create| Insert| REPLACE} destination_table [(Column, Column, column, ...)] USING query   sql>copy from SCOTT/TIGER@HQ to JOHN/CHROME@WEST&NBSP;&NBsp;create emp_temp USING SELECT * from EMP   30. Do not exit Sql*plus and execute an operating system command in Sql*plus: Host   Sql> host hostname This command may be supported under Windows.   31. In Sql*plus, switch to the operating system command prompt, and after running the operating system command, you can switch back to Sql*plus:!   sql>! $hostname $exit sql>    The command is not supported under Windows.   32. Show Help on the Sql*plus command how to install Help files: sql>@? Qlplus\admin\help\hlpbld.sql? Qlplus\admin\help\helpus.sql sql>help index   33. Displays the value of the Sql*plus system variable or the value of the Sql*plus environment variable Syntax sho[w] option where option represents one of the following terms or Clauses:system _variable all Bti[tle] err[ors] [{function| procedure| package| PACKAGE body| trigger| view| type| TYPE body} [schema.] Name] LNO PARAMETERS [parameter_name] PNO rel[ease] repf[ooter] reph[eader] SGA spoo[l] SQLCODE Tti[tle] USER    1). Displays the value of the current environment variable: Show All   2. Displays error messages that are currently in the creation of functions, stored procedures, triggers, packages, and so on. When an error occurs, such as creating a function, stored procedure, and so on, the variable can use the command to see the error in that place and the corresponding error message, make the modification and compile again. &NBSP;&NBSP;3). Displays the value of the initialization parameter: show PARAMETERS [Parameter_name] &NBSP;&NBSP;4). Displays the version of the database: show Rel[ease]   5). Shows the size of the SGA show SGA   6). Displays the current user name show user

Turn this: http://www.cnoug.org/viewthread.php?tid=16867