Welcome to our guide on Basic SQL plus commands with examples. You’ll find valuable insights here whether you’re a beginner or an expert. In this post, we’ll cover everything you need to know about Basic SQL plus commands. Let’s get started.
Login as an Oracle user and set the database environment. After that please go through the examples below to check out the basic SQL plus commands:
SQLPLUS basic commands:
1.Connect to sqlplus without connecting to Oracle database:
[oracle@srv1 ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 1 12:43:45 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> show user
USER is ""
SQL> select name from v$database;
SP2-0640: Not connected
2. Connect to the local database as sysdba and check the current database user:
From SQLPLUS:
SQL> connect / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> select name from v$database;
NAME
------
ORADB
From OS:
[oracle@srv1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 1 13:03:32 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user
USER is "SYS"
SQL> select name from v$database;
NAME
---------
ORADB
You can use conn instead of connect. CONN is an abbreviation of the CONNECT. Here we connect to the database as SYS without providing the password using the Operating System Authentication method. Please note that the OS user (here Oracle) should be a member of the dba group in Linux and the ORA_DBA group in Windows.
To check the associated groups of an OS user in Linux:
[oracle@srv1 ~]$ id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),982(vboxsf),54322(dba)
3. Connect to the local database with the username & password. Also, check the current database user:
[oracle@srv1 ~]$ sqlplus system/system123
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 1 23:35:15 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Wed Jun 19 2024 17:30:16 +04:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user
USER is "SYSTEM"
SQL> select name from v$database;
NAME
---------
ORADB
4. Exit from SQL*Plus:
You can use the exit or quit command to exit from the SQLplus.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
OR,
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
5. Display the structure of any table:
Use the describe or desc command to check the structure of any table.
SQL> desc HR.EMPLOYEES
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
6. Find the contents of the SQL*Plus buffer:
SQL*Plus buffer contains the latest SQL statement (not SQLPlus command) you issue in SQLPlus. Use LIST or l to check the same.
SQL> list
1* select table_name from dba_tables where owner='HR'
or,
SQL> l
1* select table_name from dba_tables where owner='HR'
7. Save the contents of the buffer into a file:
Use the save or sav command to save the contents of the buffer into the list file:
SQL> SAVE list
Created file list.sql
8. Edit the statement in the SQL*Plus buffer:
Use EDIT or ed command to edit the SQL statement in SQL*Plus buffer:
SQL> edit
Wrote file afiedt.buf
1* select table_name from dba_tables where owner='HR'
OR,
SQL> ed
Wrote file afiedt.buf
1* select table_name from dba_tables where owner='HR'
9. Run the statement from the SQL*Plus buffer:
You can use any of the (/ + ENTER) or run or r command to run the statement from the SQL*Plus buffer:
SQL> /
TABLE_NAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
2 rows selected.
OR,
SQL> run
1* select table_name from dba_tables where owner='HR'
TABLE_NAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
2 rows selected.
OR,
SQL> r
1* select table_name from dba_tables where owner='HR'
TABLE_NAME
--------------------------------------------------------------------------------
REGIONS
COUNTRIES
2 rows selected.
10. Set the maximum characters for any columns:
Here we set the maximum characters of the FIRST_NAME to 6 characters and that is why the FIRST_NAME value is split in two rows when we have set column FIRST_NAME format 6.
SQL> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=193 ORDER BY 1;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
193 Britney Everett 3900
SQL> column FIRST_NAME format a6
SQL> /
EMPLOYEE_ID FIRST_ LAST_NAME SALARY
----------- ------ ------------------------- ----------
193 Britne Everett 3900
y
11. Add a separator in the column value:
Here we are adding a thousand separators in the SALARY column. If the salary column value is less than 10 characters then the column value is displayed as # symbols.
SQL> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=193 ORDER BY 1;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
193 Britney Everett 3900
SQL> column SALARY format 999
SQL> /
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ------
193 Britney Everett ####
SQL> column SALARY format 999,999
SQL> /
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- --------
193 Britney Everett 3,900
12. Remove multiple headers from the query output:
Here you will get headers only one time.
SQL> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES ORDER BY 1;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- --------
100 Steven King 24,000
101 Neena Kochhar 17,000
102 Lex De Haan 17,000
103 Alexander Hunold 9,000
104 Bruce Ernst 6,000
105 David Austin 4,800
106 Valli Pataballa 4,800
107 Diana Lorentz 4,200
108 Nancy Greenberg 12,008
109 Daniel Faviet 9,000
110 John Chen 8,200
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- --------
111 Ismael Sciarra 7,700
112 Jose Manuel Urman 7,800
113 Luis Popp 6,900
114 Den Raphaely 11,000
115 Alexander Khoo 3,100
116 Shelli Baida 2,900
117 Sigal Tobias 2,800
118 Guy Himuro 2,600
119 Karen Colmenares 2,500
120 Matthew Weiss 8,000
121 Adam Fripp 8,200
22 rows selected.
SQL> SET HEADING OFF
SQL> /
100 Steven King 24,000
101 Neena Kochhar 17,000
102 Lex De Haan 17,000
103 Alexander Hunold 9,000
104 Bruce Ernst 6,000
105 David Austin 4,800
106 Valli Pataballa 4,800
107 Diana Lorentz 4,200
108 Nancy Greenberg 12,008
109 Daniel Faviet 9,000
110 John Chen 8,200
111 Ismael Sciarra 7,700
112 Jose Manuel Urman 7,800
113 Luis Popp 6,900
114 Den Raphaely 11,000
115 Alexander Khoo 3,100
116 Shelli Baida 2,900
117 Sigal Tobias 2,800
118 Guy Himuro 2,600
119 Karen Colmenares 2,500
120 Matthew Weiss 8,000
121 Adam Fripp 8,200
122 Payam Kaufling 7,900
123 Shanta Vollman 6,500
124 Kevin Mourgos 5,800
125 Julia Nayer 3,200
22 rows selected.
13. List the contents of the current folder from SQL*Plus:
SQL> host dir *
afiedt.buf list.sql oradb_noncdb.rsp oradb.rsp query-output.log
Desktop:
org.gnome.Terminal.desktop
Documents:
Downloads:
Music:
Pictures:
Public:
Templates:
Videos:
List the file/folder that starts with the list in the current folder
SQL>host dir list*
list.sql
14. Execute SQL script that contains statements and commands:
It executes all the statements and commands present in the script sequentially. If one of the statements fails, it goes to the next statement and executes it. You do not need to include the extension in the file name.
SQL> @list
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- --------
193 Britney Everett 3,900
OR,
SQL> @list.sql
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- --------
193 Britney Everett 3,900
If you want to execute the list script from the OS, you can follow the below method:
[oracle@srv1 ~]$ sqlplus hr/hr123 @list
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 2 08:38:51 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Jun 22 2024 15:13:01 +04:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
193 Britney Everett 3900
Here we are connecting to the database as HR user and running the list script in one command.
15. Save SQL*Plus output to the given file:
Here the output of the below SQL statement/query will be written into the file query-output.log. Please note that SQ*Plus output is not saved into the external file until the command “spool off;” is issued.
SQL> spool query-output.log
SQL> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE EMPLOYEE_ID=193 ORDER BY 1;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
193 Britney Everett 3900
SQL> spool off;
16. Check file contents from SQL*Plus:
SQL> host cat query-output.log
SQL> /
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
----------- -------------------- ------------------------- ----------
193 Britney Everett 3900
SQL> spool off;
If you want to edit the file, you can run the command host vi query-output.log
17. Change the prompt characters for SQL*Plus:
SQL> set sqlprompt "_user'@'_connect_identifier'> '"
HR@oradb>
We can see the SQL*Plus prompt characters have been changed from SQL to HR@oradb.
I hope you found this sqlplus basics article helpful. Feel free to reach out if you have any questions. If you want to create your VirtualBox environment and practice this then you can follow my other article to Install Oracle VirtualBox.