What is Oracle SQL Plus and SQL Plus Usage

Yusuf SEZER

5 years ago

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.

sqlplus

sqlplus

SQL Plus with Docker

The following command is used to use the SQL Plus tool with Docker.

docker exec -it oracle bash -c "source /home/oracle/.bashrc; sqlplus"

1	docker exec -it oracle bash -c "source /home/oracle/.bashrc; sqlplus"

Connect Database with SQL Plus

sqlplus Your_User_Name/Your_User_Password;

1	sqlplus Your_User_Name/Your_User_Password;

You can connect to the database using the connect command from the SQLPlus tool.

connect

connect

connect Your_User_Name

1	connect Your_User_Name

connect Your_User_Name/Your_User_Password

1	connect Your_User_Name/Your_User_Password

Connect Oracle Database With SQL Plus As SYSDBA

connect SYS / as SYSDBA

1	connect SYS / as SYSDBA

Check Connection in SQLPlus

The show user command is used to check the connection.

show user

show user

SQLPlus Disconnect From Database

The disconnect command is used to close the connection.

disconnect

1	disconnect

Clear Screen SQLPlus

The clear screen command is used to clear the screen.

clear screen

1	clear screen

List queries written in SQLPlus

The list command is used to list queries written in SQL Plus.

list

list

Clear Buffer in SQLPlus

The clear buffer and del command are used to delete previously written commands.

clear buffer

1	clear buffer

cl buffer

cl buffer

del

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.

append sql_to_be_appended

1	append sql_to_be_appended

input sql_to_be_appended

1	input sql_to_be_appended

Change Command in SQLPlus

The change command is used to change the SQL query.

change old_value/new_value

1	change old_value/new_value

Save query to file in SQLPlus

The save command is used to save SQL commands.

save file_name

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.

spool C:\codes.txt
spool OFF

1 2	spool C:\codes.txt spool OFF

SQLPlus run sql file

Get, start, @ is used to get the SQL commands in the file.

get file_name
start file_name
@file_name
run

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.

host

host

host command

1	host command

Temp Variable in SQLPLus

The ampersand (&) sign is used to use a temporary variable in SQL queries.

SELECT * FROM MyTable WHERE Column = &Column_Value

1	SELECT * FROM MyTable WHERE Column = &Column_Value

The SET command is used to change the variable mark.

SET DEFINE ON
SET DEFINE '#'
SELECT * FROM MyTable WHERE Column = &Column_Value

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.

SET WRAP ON/OFF
SET HEAD ON/OFF
SET LINESIZE 15

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.

help command

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.