SQL * plus commands in oracle

Oracle 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. The DML, DDL, and DCL statements we usually call are SQL * plus statements. After they are executed, they can be stored in a memory area called sqlbuffer, you can only save one of the most recently executed SQL statements.

Oracle 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. The DML, DDL, and DCL statements we usually call are SQL * plus statements. After they are executed, they can be stored in a memory area called SQL buffer, you can only save one of the most recently executed SQL statements.

Oracle 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.
The DML, DDL, and DCL statements we usually call are SQL * plus statements. After they are executed, they can be stored in a memory area called SQL buffer, in addition, only one recently executed SQL statement can be saved. We can modify the SQL statement stored in SQL buffer and execute it again. SQL * plus generally deals with databases.
Apart from SQL * plus statements, other statements executed in SQL * plus are called SQL * plus commands. After they are executed, they are not stored in the memory area of the SQL buffer. They are generally used to format and display the output results to facilitate the preparation of reports.
The following describes some common SQL * plus commands:

1. Execute an SQL script file
SQL> start file_name
SQL> @ file_name
We can save multiple SQL statements in a text file, so that when we want to execute all the SQL statements in this file, we can use any of the following commands, which is similar to batch processing in dos.

What is the difference between @ and?
@ Equals to the start command, used to run an SQL script file.
@ Command to call the script file in the current directory, or specify the full path, or you can use the SQLPATH environment variable to search for the script file. This command is generally used to specify the full path of the file to be executed, otherwise the specified file is read from the default path (specified by the SQLPATH variable.
@ Is used in the SQL script file to indicate that the SQL script file executed with @ is in the same directory as the file where @ is located, instead of specifying the full path of the SQL script file to be executed, or finding the SQL script file from the path specified by the SQLPATH environment variable, this command is generally used in the script file.
For example, in the c:/temp directory, the start. SQL and nest_start. SQL files and the start. SQL script files are as follows:
@ Nest_start. SQL--equivalent to @ c:/temp/nest_start. SQL
In SQL * plus, run the following command:
SQL> @ c:/temp/start. SQL

2. Edit the current input.
SQL> edit

3. Run the last SQL statement again.
SQL>/

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 column NAME to the new column NAME "employee name" 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
Employee
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
SQL>/
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
SQL>/
EMPNO ENAME
--------------------------------------------------
7369 SMITH
7499 ALLEN
7521 WARD

6). format the display of NUMBER columns:
SQL> COLUMN SAL FORMAT $99,990
SQL>/
Employee
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]
COL1
--------------------
How are you?

SQL> COL COL1 FORMAT A5
SQL> COL COL1 WRAPPED
COL1
-----
HOW
RE YO
U?

SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW
ARE
YOU?

SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW

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. The display of the same values displayed in a column is shielded above. Each time the column value changes, n blank rows are inserted before the value changes.
Break on break_column SKIP n

SQL> BREAK ON DEPTNO SKIP 1
SQL>/
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
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 is returned, no matter how many rows are queried. 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 output content is one 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 to debug the stored procedure. The information can be displayed on the screen only after the serveroutput variable is set to on.
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 length of the output line is greater than the length of the set line (set with the set linesize n command), when set wrap on, more than the characters of the output line will be displayed in another line, otherwise, the output line is removed from the output line and will not be 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 in a large table to a file, it takes a lot of time to output the content on the screen. After setting set termspool off, the output content is saved only in the output file and not displayed on the screen, greatly improving the spool speed.

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) When an empty row is encountered, the statement is not considered to have ended. The statement is read from 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, ename
From emp

Where empno = ''7788 '';
If it is copied to SQL * plus for execution, an error occurs. This command can solve this problem

15). Set the output of DBMS_OUTPUT
Set serveroutput on buffer 20000
Use dbms_output.put_line (''strin _ content''); you can output information in the stored procedure and debug the stored procedure.
If you want to display the output of dbms_output.put_line (''abc:
SQL> abc, instead of SQL> abc, add the format wrapped parameter after SET SERVEROUTPUT ON.

16). The output data is in html format.
Set markup html
In version 8.1.7 (maybe 816? Not sure) Later, SQL * plus has a set markup html command, which can display SQL * plus output in html format.
Note that when the spool on is output on the screen, we cannot see any difference with the spool on, but when we use the spool filename to output to the file, A tag is displayed in the spool file.

14. Modify the first string in the current row of SQL buffer
C [HANGE]/old_value/new_value
SQL> l
1 * select * from dept
SQL> c/dept/emp
1 * select * from emp

15. Edit the SQL statement in SQL buffer
EDI [T]

16. display the SQL statements in SQL buffer. list n displays the nth row in SQL buffer and makes the nth row 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
SQL> L
1 select deptno,
2 dname, loc
3 * from dept
SQL>/

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
RUN
Or
/

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
TTITLE

25. Set the end title of each report
BTITLE

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 some data from a database to another database (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:
HOST

SQL> host hostname
This command may be supported in windows.

31. In SQL * plus, switch to the operating system command prompt. After running the operating system command, you can switch back to SQL * plus again:
!

SQL>!
$ Hostname
$ Exit
SQL>

This command is not supported in windows.

32. display the help of the SQL * plus command
HELP
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
Syntax
SHO [W] option
Where option represents one of the following terms or clses:
System_variable
ALL
BTI [TLE]
ERR [ORS] [{FUNCTION | PROCEDURE | package body |
TRIGGER | VIEW | type body} [schema.] name]
LNO
PARAMETERS [parameter_name]
PNO
REL [statement]
REPF [OOTER]
REPH [EADER]
SGA
SPOO [L]
SQLCODE
TTI [TLE]
USER

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.
Show error
When an error occurs in creating a function or stored procedure, you can use this command to view the error and related error information at that place, modify the information, and compile the code again.

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

34. query objects under a user
SQL> select * from tab;
SQL> select * from user_objects;

35. query all tables under a user
SQL> select * from user_tables;

36. query all indexes of a user
SQL> select * from user_indexes;

37. Define a user variable
There are two methods:
A. define
B. COL [UMN] [{column | expr} NEW_V [ALUE] variable [NOPRI [NT] | PRI [NT]
OLD_V [ALUE] variable [NOPRI [NT] | PRI [NT]
The following describes each method:
A. Syntax
DEF [INE] [variable] | [variable = text]
Define a user variable and assign it a CHAR value.

Assign the value MANAGER to the variable POS, type:
SQL> DEFINE POS = MANAGER

Assign the CHAR value 20 to the variable DEPTNO, type:
SQL> DEFINE DEPTNO = 20

List the definition of DEPTNO, enter
SQL> DEFINE DEPTNO
―――――――――――――――
Define deptno = "20" (CHAR)

After defining the user variable POS, you can use & POS or & POS in SQL * plus to reference the value of this variable. SQL * plus will not prompt you to enter a value for the variable.

B. COL [UMN] [{column | expr} NEW_V [ALUE] variable [NOPRI [NT] | PRI [NT]
NEW_V [ALUE] variable
Specify a variable to accommodate the queried column values.
Example: column col_name new_value var_name noprint
Select col_name from table_name where ........
Assign the value of the col_name column queried below to the var_name variable.

A comprehensive example:
Get the difference between two queries with a column value (in this example, the number of transactions committed within 10 seconds ):
Column redo_writes new_value commit_count

Select sum (stat. value) redo_writes
From v $ sesstat stat, v $ statname sn
Where stat. statistic # = sn. statistic #
And sn. name = ''user commit '';

-- Wait for a while (10 seconds here );
Execute dbms_lock.sleep (10 );

Set veri off
Select sum (stat. value)-& commit_count commits_added
From v $ sesstat stat, v $ statname sn
Where stat. statistic # = sn. statistic #
And sn. name = ''user commit '';

38. Define a binding variable
VAR [IABLE] [variable [NUMBER | CHAR (n) | NCHAR (n) | VARCHAR2 (n) | NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR]
Defines a binding variable that can be referenced in pl/SQL.
You can use the print command to display information about the bound variable.
For example:
Column inst_num heading "Inst Num" new_value inst_num format 99999;
Column inst_name heading "Instance" new_value inst_name format a12;
Column db_name heading "DB Name" new_value db_name format a12;
Column dbid heading "DB Id" new_value dbid format 9999999999 just c;

Prompt
Prompt Current Instance
Prompt ~~~~~~~~~~~~~~~~

Select d. dbid
, D. name db_name
, I. instance_number inst_num
, I. instance_name inst_name
From v $ database d,
V $ instance I;

Variable dbid number;
Variable inst_num number;
Begin
: Dbid: = & dbid;
: Inst_num: = & inst_num;
End;
/
Note:
In SQL * plus, the bound variable can be used as a parameter of the stored procedure, or can be directly referenced in anonymous PL/SQL blocks. To display the value of the VARIABLE bound with the VARIABLE command, run the print command.

Note:
Variable binding is different from variable binding:
1. Different Definition Methods
2. Different reference methods
Bind Variable: variable_name
Variable: & variable_name or & variable_name
3. In SQL * plus, you can define bind variables with the same name as user variables, but the referenced methods are different.

39. & Differences
& Used to create a temporary variable. Whenever this temporary variable is encountered, you will be prompted to enter a value.
& Used to create a persistent variable, just like a persistent variable created using the define command or the column command with the new_vlaue words. When you use the & command to reference this variable, you will not be prompted to enter a value every time you encounter this variable, but will be prompted only once when you first encounter this variable.

For example, if you save the following three statements as a script file and run the script file, you will be prompted three times to enter the deptnoval value:
Select count (*) from emp where deptno = & deptnoval;
Select count (*) from emp where deptno = & deptnoval;
Select count (*) from emp where deptno = & deptnoval;

Save the following three statements as a script file. If you run the script file, you will only be prompted once to enter the value of deptnoval:
Select count (*) from emp where deptno = & amp; deptnoval;
Select count (*) from emp where deptno = & amp; deptnoval;
Select count (*) from emp where deptno = & amp; deptnoval;

40. Run an SQL * plus command (from www.itpub.com) when you enter an SQL statement)
#
Have you ever experienced this? After hitting a long command in SQL * plus, I suddenly found that I could not remember the name of a column. If I canceled the current command and re-typed it after the query, it would be too painful. of course, you can open another SQL * plus window for query, but the method provided here is simpler.

For example, if you want to query the information of employees whose salaries are greater than 4000, enter the following statement:

SQL> select deptno, empno, ename
2 from emp
3 where
At this moment, you find that you cannot remember the name of the salary column.

In this case, as long as the next line starts with #, You can execute an SQL * plus command. After the command is executed, you can continue to input

SQL> select deptno, empno, ename
2 from emp
3 where
6 # desc emp
Name Null? Type
---------------------------------------------------------------
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

41. Fast copy and paste techniques in SQLPlus (from www.cnoug.org)
1) move the cursor to the beginning of the content you want to copy
2) press the left mouse button with the right index finger
3) drag the mouse to the other corner of the content to be copied, just like the method used to select the content in Word.
4) after the content is selected (all selected content is reversed), press and hold the left mouse button, and right-click the right hand
5) The selected content is automatically copied to the last line of the SQL * Plus environment.