SQL*Plus Command Reference
Last Updated :
06 Jun, 2024
SQL*Plus is a command-line tool for Oracle Database that allows users to interact with the database using SQL and PL/SQL commands.
Here, we will discuss the SQL*Plus commands, and understand how to use the SQL*Plus command-line argument.
SQL*Plus Commands
Here is a list of some essential SQLPlus Commands:
| Command | Syntax | Description |
|---|
| CONNECT | CONNECT username/password@hostname:port/service_name; | Connects to a database using the specified credentials and connection details. |
|---|
| SELECT | SELECT * FROM tablename; | Executes an SQL query to retrieve data from a table. |
|---|
| DESCRIBE | DESCRIBE tablename; | Displays the structure of a table (columns, data types, etc.). |
|---|
| SET LINESIZE | SET LINESIZE n; | Sets the maximum line width for query output. |
|---|
| SET PAGESIZE | SET PAGESIZE n; | Sets the number of lines per page for query output. |
|---|
| SPOOL | SPOOL filename; | Redirects query output to a file. |
|---|
| START | START script.sql; | Executes a script file. |
|---|
| EDIT | EDIT; | Opens the default text editor to edit the current command buffer. |
|---|
| HOST | HOST command; | Executes an operating system command. |
|---|
| QUIT or EXIT | QUIT; or EXIT; | Disconnects from the database and exits SQL*Plus. |
|---|
| VARIABLE | VARIABLE my_var NUMBER; | Defines and uses variables in SQL*Plus. |
|---|
| COLUMN | COLUMN column_name FORMAT A20; | Formats query output for a specific column. |
|---|
| SET ECHO | SET ECHO OFF; | Suppresses command echoing. |
|---|
| SET TIMING | SET TIMING ON; | Displays execution time for SQL statements. |
|---|
Command Line Arguments in SQL*Plus
Command line arguments in SQLPlus are the parameters that control the behavior of the tool. These arguments can include script names, connection details, and other options. Command-line arguments are used to streamline workflows and automate tasks.
Syntax
SQLPlus command-line argument syntax is:
sqlplus [username]/[password]@[database] @script.sql
Here,
- sqlplus: Command to start SQL*Plus.
- [username]/[password]@[database]: Connection details to log in to the Oracle Database.
- @script.sql: The SQL script file to be executed.
Maximum Length of Command Line Argument
The maximum length of a command line argument is determined by the operating system. In Linux, the maximum length is typically 131,072 bytes (or 128 KB). This includes the length of the SQL*Plus command, SQL query, and any additional parameters.
SQL*Plus Command Line Argument Limits
Item
| Limit
|
|---|
filename length
| system dependent
|
username length
| 128 bytes
|
substitution variable name length
| 128 bytes
|
substitution variable value length
| 240 bytes
|
command-line length
| 5000 characters
|
LONG
| 2,000,000,000 bytes
|
LINESIZE
| system dependent
|
LONGCHUNKSIZE value
| system dependent
|
output line size
| system dependent
|
SQL or PL/SQL command- line size after variable substitution
| 3,000 characters (internal only)
|
number of characters in a COMPUTE command label
| 500 characters
|
number of lines per SQL command
| 500 (assuming 80 characters per line)
|
maximum PAGESIZE
| 50,000 lines
|
total row width
| 32,767 characters
|
maximum ARRAYSIZE
| 5000 rows
|
maximum number of nested scripts
|
20
|
maximum page number
|
99,999
|
maximum PL/SQL error message size
| 2K
|
maximum ACCEPT character string length
| 240 bytes
|
maximum number of substitution variables
|
2048
|
SQL*Plus Example
Exceeding Maximum Length Limit
Imagine you have a more complex SQL script file named long_script.sql with a large query:
long_script.sql File
SELECT * FROM employees WHERE salary > 50000 AND department_id IN
(SELECT department_id FROM departments WHERE location_id = 'XYZ')
AND
hire_date < TO_DATE('2022-01-01', 'YYYY-MM-DD')
AND ... (continued)
And you attempt to run the following SQL*Plus command:
sqlplus system/manager@orcl @long_script.sql
In this case, the total length of the SQL*Plus command, along with the connection details, and the lengthy SQL script, might exceed the maximum limit imposed by the operating system. If the length surpasses the limit, you could encounter an error similar to:
bash: /bin/sqlplus: Argument list too long
This error indicates that the total length of the command line argument has exceeded the maximum allowed, and the command cannot be executed.
To avoid this issue, it's recommended to store complex and lengthy SQL commands or queries in separate script files and execute them using SQL*Plus.
Conclusion
SQL*Plus is a command-line tool provided by Oracle for interacting with Oracle Database. It allows you to perform various tasks related to querying, data manipulation, and database administration. SQL*Plus commands are case-insensitive, and can be abbreviated (e.g., DESCRIBE as DESC).
Similar Reads
PL/SQL Comments
Oracle PL/SQL is an advanced procedural language designed to enhance SQL functionalities in Oracle Database environments. Comments can be used to make code more human-readable. It is a kind of note that programmers usually add while writing the code. In this article, we will learn about Comments in
4 min read
PL/SQL SELECT FROM
PL/SQL is Oracle procedural extension for SQL which allows for more powerful and flexible database manipulation. The SELECT statement is one of the most fundamental and widely used commands in SQL. It allows users to retrieve data from one or more tables in a database. In this article, we will learn
5 min read
SQLite Dump Command
SQL stands for Structured Query Language, which is the common language (with minor changes) used to manipulate relational databases. It is used to create, store, retrieve, and manipulate databases and tables. SQLite is a lightweight version of SQL with some major changes, it doesn't have a separate
8 min read
SQL SELECT COUNT()
In SQL, the SELECT statement is a fundamental tool for retrieving data from a database. When paired with the COUNT() function, it becomes even more powerful, enabling efficient data aggregation and analysis. This article provides a detailed explanation of SELECT and COUNT() in SQL, including syntax,
4 min read
Basic SQL Commands
Structured Query Language (SQL) is the standard language used for managing and interacting with relational databases. Whether we are retrieving data, updating records, or defining the structure of our data, SQL commands provide a powerful and flexible way to handle these tasks. This article will exp
6 min read
Difference between SQL and T-SQL
SQL (Structured Query Language) is the standard language for managing and manipulating relational databases, enabling operations like querying, updating, and deleting data. T-SQL (Transact-SQL), an extension of SQL developed by Microsoft, adds advanced features and procedural capabilities specifical
4 min read
SQL Comments
SQL comments play an essential role in enhancing the readability, maintainability, and documentation of our SQL code. By using comments effectively, developers can provide context, clarify complex logic, and temporarily disable parts of the code. Whether we're working alone or collaborating with a t
4 min read
Basic Query in PL/SQL procedure
PL/SQL (Procedural Language/Structured Query Language) is a powerful extension to SQL, designed to combine the robustness of SQL with procedural constructs like loops, conditions, and more. It plays a crucial role in writing complex database interactions in Oracle databases. This article will cover
3 min read
SQL | Intersect & Except clause
1. INTERSECT clause : As the name suggests, the intersect clause is used to provide the result of the intersection of two select statements. This implies the result contains all the rows which are common to both the SELECT statements. Syntax : SELECT column-1, column-2 …… FROM table 1 WHERE….. INTER
1 min read
SQL Server AND Operator
Logical operators are used for combining multiple boolean expressions which are combinations of results of multiple conditions which are formed by the comparators. Some of the logical operators are AND, OR, EXISTS, IN, LIKE, BETWEEN, etc, Logical operators are frequently used and very handy for test
3 min read