SQL*Plus Command Reference
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).
Contact Us