SQL * Plus command

Source: Internet
Author: User
Tags dname trims
SQL * Plus command (1)
Introduction: Oracle SQL * Plus is a client tool for interacting with Oracle. In SQL * Plus, you can run the SQL * Plus command and the SQL * Plus statement.
The DML, DDL, and DCL statements we usually call are SQL * Plus statements. After they are executed, they can all be saved in

In the memory region called SQL Buffer, the latest SQL statement can only be saved.

SQL statements in SQL Buffer are modified and executed again. SQL * Plus generally deals with databases.


Apart from SQL * Plus statements, other statements executed in SQL * Plus are called SQL * Plus commands. They execute

After completion, it is not stored in the memory area of the SQL Buffer. They are generally used to format and display the output results,

This allows you to easily create reports.

The following describes some common SQL * Plus commands:

1. Execute an SQL script file

SQL> Start file_name

SQL> @ file_name

You can save multiple SQL statements in a text file.

When using a sentence, use any of the following commands, which is similar to the batch processing in DOS.

2. Edit the current input.

SQL> Edit

3. Run the last SQL statement again.


4. output the displayed content to the specified file.

SQL> spool file_name

All content on the screen is included in this file, including the SQL statement you entered.

5. Disable spool output

SQL> spool off

The output content is displayed in the output file only when spool output is disabled.

6. display the structure of a table

SQL> DESC table_name

7. Col command:

It mainly formats the display of columns.

This command has the following options:

Col [umn] [{Column | expr} [option...]

The option can be the following clause:

Ali [as] alias

CLE [ar]

Fold_a [fter]

Fold_ B [efore]

For [Mat] format

Hea [Ding] Text

Jus [cipher] {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 the default Column Title

Column column_name heading column_heading

For example:

SQL> select * from Dept;

Deptno dname Loc


10 Accounting New York

SQL> Col loc heading location

SQL> select * from Dept;

Deptno dname location


10 Accounting New York

2) change the name of the column to the new column name "employee" and put the new column name on two rows:

SQL> select * from EMP

Department name salary


10 AAA 11

SQL> column ename heading 'employee | name'

SQL> select * from EMP


Department name salary


10 AAA 11

Note: The Col heading turn into two lines from one line.

3). Change the display length of the column:

For [Mat] format

SQL> select empno, ename, job from EMP;

Empno ename job


7369 Smith clerk

7499 Allen salesman

7521 ward salesman

SQL> Col ename format A40

Empno ename job


7369 Smith clerk

7499 Allen salesman

7521 ward salesman

4). Set the column title alignment

Jus [cipher] {L [EFT] | C [enter] | C [entre] | r [ight]}

SQL> Col ename justify Center


Empno ename job


7369 Smith clerk

7499 Allen salesman

7521 ward salesman

For number-type columns, the column title is on the right by default, and for other types of columns, the column title is on the left by default.

5). prevent a column from being displayed on the screen.

Nopri [nt] | pri [nt]

SQL> Col job noprint


Empno ename


7369 Smith

7499 Allen

7521 ward

6). format the display of number columns:

SQL> column Sal format $99,990



Department name Salary Commission


30 Allen $1,600 300

7). If the column value is null when the column value is displayed, replace the null value with the text value.

Comm NUL [l] Text

SQL> Col comm NUL [l] Text

8). Set the rewinding Method for a column.

Wra [pped] | wor [d_wrapped] | Tru [ncated]



How are you?

SQL> Col col1 format A5

SQL> Col col1 wrapped




Re yo


SQL> Col col1 word_wrapped






SQL> Col col1 truncated




9). display the current display attribute value of the column

SQL> column column_name

10). Set the display attribute of all columns to the default value.

SQL> clear Columns

8. Block the same value displayed in a column

Break on break_column

SQL> Break on deptno

SQL> select deptno, ename, Sal

From EMP

Where Sal <1, 2500

Order by deptno;

Deptno ename Sal


10 Clark 2450

Miller 1300

20 Smith 800

Adams 1100

9. Shielding the display of the same value displayed in a column above, insert n before the value change whenever the column value changes

Empty rows.

Break on break_column skip n

SQL> Break on deptno skip 1


Deptno ename Sal


10 Clark 2450

Miller 1300

20 Smith 800

Adams 1100

10. Display break settings

SQL> Break

11. Delete settings 6 and 7

SQL> clear breaks

SQL * Plus command (2)
[Introduction] Oracle SQL * Plus is a client tool for interacting with Oracle. In SQL * Plus, you can run the SQL * Plus command and the SQL * Plus statement.
12. set command:

This command contains many sub-commands:

Set system_variable Value

System_variable value can be one of the following clauses:

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 [visible] | 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) set whether the current session will automatically submit the modified data

SQL> set auto [Commit] {on | off | Imm [ediate] | n}

2) Whether to display the SQL statements being executed in the script when you run an SQL script using the start command

SQL> set echo {on | off}

3). Whether to display the number of rows queried or modified by the current SQL statement

SQL> Set feed [Back] {6 | n | on | off}

By default, only rows with more than 6 results are displayed. If set feedback 1, no matter how many

All rows are returned. When it is off, the number of queried rows is not displayed.

4). Whether to display the column title

SQL> set hea [Ding] {on | off}

When set heading off, Column Titles are not displayed on each page, instead of blank rows.

5) set the number of characters that a row can hold

SQL> set LiN [esize] {80 | n}

If the output content of a row is greater than the number of characters that can be accommodated in a set row, the line is displayed.

6). Set the separation between pages

SQL> set newp [age] {1 | n | none}

When set Newpage 0, a small black box is displayed at the beginning of each page.

When set Newpage N is set, there are n blank lines between the page and the page.

When set Newpage none is set, there is no interval between the page and the page.

7). Replace the null value with the text value during display.

SQL> set null text

8). Set the number of rows on a page.

SQL> set pages [ize] {24 | n}

If it is set to 0, all the output content is a page, and the column title is not displayed.

9). Whether to display the output information using the dbms_output.put_line package.

SQL> set serverout [put] {on | off}

When writing a stored procedure, we sometimes use dbms_output.put_line to output necessary information

After the serveroutput variable is set to on, the information can be displayed on the screen.

10) Whether to intercept the SQL statement when the length of the SQL statement is greater than linesize.

SQL> set wra [p] {on | off}

When the output line length is greater than the set line length (set with the set linesize n command), when set wrap

On, more than the characters in the output line are displayed in another line. Otherwise, more than the characters in the output line are removed and not displayed.


11). Whether to display the output content on the screen is mainly used in combination with spool.

SQL> set term [out] {on | off}

When you use the spool command to output the content of a large table to a file, it will be very expensive to output the content on the screen.

Set termspool off, the output content will only be saved in the output file, not displayed in

The spool speed is greatly improved on the screen.

12) Remove unnecessary spaces behind each line in the spool output.

SQL> set trims [out] {on | off}

13) display the execution time of each SQL statement

Set timing {on | off}

14. Modify the first string in the current row of SQL Buffer

C [hange]/old_value/new_value


1 * select * from Dept


1 * select * from EMP

15. Edit the SQL statement in SQL Buffer


16. Displays SQL statements in SQL Buffer. List n displays the nth row in SQL Buffer. Limit indicates that the nth row is the current row.


L [ist] [N]

17. Add one or more rows under the current row of SQL Buffer

I [nput]

18. Add the specified text to the current row of the SQL Buffer.

A [ppend]

SQL> select deptno,

2 dname

3 from Dept;

Deptno dname


10 Accounting

20 research

30 sales

40 operations

SQL> L 2

2 * dname

SQL> A, Loc

2 * dname, Loc


1 select deptno,

2 dname, Loc

3 * from Dept


Deptno dname Loc


10 Accounting New York

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 in a file into SQL Buffer

Get file_name

21. Execute the SQL statement that has just been executed again




22. Execute a stored procedure

Execute procedure_name

23. Connect to the specified database in SQL * Plus

Connect user_name/passwd @ db_alias

24. Set the top title of each report


25. Set the end title of each report


26. Write a comment

Remark [text]

27. output the specified information or an empty row to the screen.

Prompt [text]

28. Pause the execution process and wait for the user to respond.

Pause [text]

SQL> pause adjust paper and press return to continue.

29. Copy data from one database to another (for example, copy data from one table to another)


Copy {from database | to database | from database to database}

{Append | create | insert | replace} destination_table

[(Column,...)] using query

SQL> copy from Scott/tiger @ hq to John/chrome @ West

Create emp_temp

Using select * from EMP

30. If you do not exit SQL * Plus, execute an operating system command in SQL * Plus:


SQL> host hostname

This command may be supported in windows.

31. In SQL * Plus, switch to the operating system command prompt and run the operating system command.

Back to SQL * Plus:



$ Hostname

$ Exit


This command is not supported in windows.

32. display the help of the SQL * Plus command


How to install the Help file:

SQL> @? \ Sqlplus \ admin \ HELP \ hlpbld. SQL? \ Sqlplus \ admin \ HELP \ helpus. SQL

SQL> Help Index

33. display the SQL * Plus System variable value or SQL * Plus environment variable value


Sho [w] Option

Where option represents one of the following terms or clses:




Err [ors] [{function | procedure | package body |

Trigger | View | type body} [schema.] Name]


Parameters [parameter_name]


Rel [statement]

Repf [Ooter]

Reph [eader]


Spoo [l]




1). display the value of the current environment variable:

Show all

2). displays the error information of the currently created function, stored procedure, trigger, package, and other objects.


If an error occurs when a function or stored procedure is created, you can use this command to view the errors and corresponding errors in that place.


3). display the value of the initialization parameter:

Show parameters [parameter_name]

4). display the database version:

Show rel [Rules]

5). display the SGA size

Show SGA

6). display the current user name

Show user

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.