Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
This article contains information about Oracle SQL Plus tool.
What is Oracle SQL Plus?
SQL Plus is a tool for managing the database management system in the command interpreter. When we install Oracle database management system, we can use SQL Plus tool without any further installation.
SQL Plus Usage
To use the SQLPlus tool, simply type the following command into the operating system command interpreter.
1 | sqlplus |
SQL Plus with Docker
The following command is used to use the SQL Plus tool with Docker.
1 | docker exec -it oracle bash -c "source /home/oracle/.bashrc; sqlplus" |
Connect Database with SQL Plus
1 | sqlplus Your_User_Name/Your_User_Password; |
You can connect to the database using the connect command from the SQLPlus tool.
1 | connect |
1 | connect Your_User_Name |
1 | connect Your_User_Name/Your_User_Password |
Connect Oracle Database With SQL Plus As SYSDBA
1 | connect SYS / as SYSDBA |
Check Connection in SQLPlus
The show user command is used to check the connection.
1 | show user |
SQLPlus Disconnect From Database
The disconnect command is used to close the connection.
1 | disconnect |
Clear Screen SQLPlus
The clear screen command is used to clear the screen.
1 | clear screen |
List queries written in SQLPlus
The list command is used to list queries written in SQL Plus.
1 | list |
Clear Buffer in SQLPlus
The clear buffer and del command are used to delete previously written commands.
1 | clear buffer |
1 | cl buffer |
1 | del |
Clear a Specific Command in SQL Plus History
To delete only a specific command, use the “del number” command. You can read the article “SQL Plus History” to find the number of the query. Also you can find the details about clearing all commands in SQL Plus History in this article.
Append and input Commands in SQLPlus
The append and input command is used to append to the SQL query.
1 | append sql_to_be_appended |
1 | input sql_to_be_appended |
Change Command in SQLPlus
The change command is used to change the SQL query.
1 | change old_value/new_value |
Save query to file in SQLPlus
The save command is used to save SQL commands.
1 | save file_name |
Save query result to file in SQLPlus
The spool command is used to save SQL commands and their results to a file.
1 2 | spool C:\codes.txt spool OFF |
SQLPlus run sql file
Get, start, @ is used to get the SQL commands in the file.
1 2 3 4 | get file_name start file_name @file_name run |
Host Command in SQLPlus
The host command is used to switch from the SQLPlus command line to the operating system command line.
1 | host |
1 | host command |
Temp Variable in SQLPLus
The ampersand (&) sign is used to use a temporary variable in SQL queries.
1 | SELECT * FROM MyTable WHERE Column = &Column_Value |
The SET command is used to change the variable mark.
1 2 3 | SET DEFINE ON SET DEFINE '#' SELECT * FROM MyTable WHERE Column = &Column_Value |
SQLPlus User Friendly Result
You can use below command to see results user friendly.
1 2 3 | SET WRAP ON/OFF SET HEAD ON/OFF SET LINESIZE 15 |
Get Information About Commands in SQLPlus
The help command is used to get information about the commands.
1 | help command |
Although the SQL Plus tool that we can use with Oracle installation is flexible compared to the tools in other database management systems, the use of tools such as APEX, Oracle SQL Developer will be useful against possible errors.
Have a good day.
