sqlplus set commands

Introduction to sqlplus set commands

The following articles provide an outline for sqlplus set commands. SQL*Plus is an interactive tool to execute a batch query that is installed on client installation or Oracle server. The SQL*plus commands configure the environment such as to edit, store, retrieve, enter or run SQL commands. It is a command-line tool that offers access to organize the Relational database of Oracle. It is used to update and delete the database tables and helps the user to submit the SQL blocks for execution in the Oracle server. In addition, the DDL commands play a major role in creating, edit and dropping objects from the database. The different commands used in SQL*plus are briefly explained in this article.

Overview of sqlplus set commands

SQL*plus is the common utility on database operations which is mostly used by programmers, admins, and users and has its special commands in the environment. It enables the user to perform computations, stores, print queries, format, and SQL/PL blocks. The SQL commands are explained in this article.

All sqlplus set commands

@ symbol represents to execute the SQL*plus queries in the script. It can be fetched from the webserver or local file system.

@@ is mentioned to execute the script, which is useful to execute the nested scripts as it opts for the suitable script in the same location from which it is fetched.

/ represents to execute PL\SQL block or SQL command.

ACCEPT terms to read an input line and save it in an allocated user variable.

APPEND is used to insert text to stop the currently executed line in the buffer area.

ARCHIVE LOG is used to stop or start the automated archive of online redo transaction logs and displays the information in redo transaction logs

ATTRIBUTE is used to view the characters of an object type column and explain every single character of the object

BREAK is used to change the formatting for the specified title at the bottom of every report page and list the current definition also.

BTITLE formats the suitable title at every bottom of the report page and lists the title page’s current definition.

CHANGE is used to change the text on every current line in the buffer

CLEAR is used to erase and reset the current clause for setting the specific option similar to column and break

COLUMN is used to specify the display characters for the selected column and list the current characteristic of every column.

COMPUTE is used to print and calculate the summary lines with different standard computation or the subset of a given row and lists all the definitions related to the computing

CONNECT is used to connect the provided user to the Oracle database.

COPY is used to copy the result from the input query to the table in a remote or local database.

DEFINE is used to specify user variables and allots them to every CHAR value, and list every variable type

DEL is used to delete one or multiple lines of the buffer area

DESCRIBE is used to list the definition of column for the specific table, synonym and view the specification for the given procedure or function

DISCONNECT is used to pledge the pending change to logs and database for the current user in Oracle, but it is not present in SQL*plus.

EDIT is used to invoke the operating system of the host’s text editor on the suitable file content of the buffer area.

EXECUTE is used to run the single SQL/PL statement

EXIT is used to stop the SQL*plus and call the control of the operating system

GET is used to load the file from the operating system of the host into the SQL buffer.

HELP is used to access the system of SQL*plus.

HOST is used to execute the operating system of the host without executing the SQL*plus command

INPUT is used to insert one or multiple lines after the current line in the buffer area.

LIST is used to view one or many lines from SQL buffer

PASSWORD enables the password to be changed without any echo of the password of an input device.

PAUSE is used to view the suitable text and then wait for the user to return to the home page.

PRINT is used to view the current character of the bind variable

PROMPT is used to transfer the suitable messages to the screen of the user

QUIT is used to stop the SQL*plus and returns the control to the operating system, which is the same as exit.

Examples of sqlplus set commands

RECOVER is used to do recovery of media in one or multiple tablespaces and perform recovery of one or multiple data files and possibly in the entire database.

REMARK is used to insert a comment in a script.

REPHEADER is used to format the chosen report header at the beginning of every report and view the definition of REPHEADER

RUN is used to execute and list the SQL command and SQL block saved in the buffer area of SQL.

SAVE is used to save the information of SQL buffer in the operating system file in the host.

SET is used to fix the system variable to edit the SQL*plus environment in the current session.

SHOW is used to display the character of SQL*plus system environment

SHUTDOWN is used to shut down the present executing Oracle instances.

SPOOL is used to save the result queries in the operating system’s file and transfers the file to the printer.

START is used to initiate the content of the suitable script.

STARTUP is used to initiate the Oracle instances and can open a database, and there is an option to mount it.

STORE is used to save the attributes in the existing SQL*plus an operating system of the host.

TIMING is used to save the timing data for elapsed time and view the count of active timers.

UNDEFINE is used to delete one or multiple variables, which defines the command as implicit or explicit.

WHENEVER OSERROR is used to execute the specific action if an os command produces any error.

WHENEVER SQLERROR is used to execute the specified action when SQL command blocks any occurred error.

Conclusion

Hence these are some of the commands executed in the SQL*plus environment and help beginners to work in the SQL*plus environment as proficient.

Recommended Articles

This is a guide to sqlplus set commands. Here we discuss some of the commands executed in the SQL*plus environment and help beginners. You may also have a look at the following articles to learn more –

1. SQL if then else
2. SQL extensions
3. SQL Column Alias
4. SQL SUM()