SQL*Plus Command Reference - Coding

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).

Reffered: https://www.geeksforgeeks.org

Databases

Related
PL/SQL Arrays
SQL Show Indexes
MySQL CREATE INDEX Statement
SQL Server UPDATE JOIN
MySQL Temporary Table

Type:	Geek
Category:	Coding
Sub Category:	Tutorial
Uploaded by:	Admin
Views:	13