Oracle SQL Plus Set Command

Oracle SQL* Plus is interactive batch query tool installed with Oracle Database. SQL* Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL.

sql plus work diagram
SQL*Plus is part of Oracle Database. Just to demonstrate how it works.

In this article, let’s see SQL* Plus SET command and how to use it with examples.

SET

SET commands sets system variables. It allows you to alter the SQL*Plus environment for your current session as per your preference.

Syntax

SET [SET OPTION FUNCTION] [ACTION]

Set Option Function

Below are some common options. We are going to cover most with examples.

SERVEROUTPUT
FEEDBACK
LINESIZE
HEADING

Action

On – Enable the option
Off – disable option

APPINFO

This command directs SQL plus to register scripts through the DBMS_APPLICATION_INFO package. This helps DBAs to analyze and monitor performance and resource usage of the script. V$SESSION and V$SQLAREA virtual tables stores this registered name in MODULE column.

Syntax

SET APPI[NFO]{ON | OFF | text}

ON - Enable registration. Whatever script runs using start,@, @@ gets register in Oracle Database.
OFF - Disable registration
text - It specifies text to register, default value is "SQL*Plus"

 

Example

Let’s connect to Oracle database using SQL* Plus to see how this command works. To demonstrate working created a SQL Script (appinfo_demo.sql)  which query dual table.

SELECT * 
FROM DUAL;

 

When APPINFO not set run the appinfo_demo script and check the content of  V$SESSION and V$SQLAREA

SQL> SHOW APPINFO
appinfo is OFF and set to "SQL*Plus"

SQL> @appinfo_demo.sql

D
-
X

SQL> SELECT SID,
 2 SERIAL#,
 3 STATUS,
 4 PROGRAM,
 5 MODULE
 6 FROM V$SESSION
 7 WHERE module = 'appinfo_demo';

no rows selected

SQL> SELECT sql_text,
 2 sql_fulltext,
 3 module
 4 FROM V$SQLAREA
 5 WHERE module = 'appinfo_demo';

no rows selected

 

When APPINFO not set run the appinfo_demo script and check the content of  V$SESSION and V$SQLAREA

SQL> SET APPINFO ON
SQL> SET APPINFO appinfo_demo
SQL> @appinfo_demo.sql

D
-
X

SQL> SELECT SID,
 2 SERIAL#,
 3 STATUS,
 4 PROGRAM,
 5 MODULE
 6 FROM V$SESSION
 7 WHERE module = 'appinfo_demo';

 SID SERIAL# STATUS PROGRAM MODULE
-------- – – ------ – – ---- – – -------------------------------------------- – – ------------------
 3007 46120 ACTIVE sqlplusw.exe appinfo_demo

SQL> SELECT sql_text,
 2 sql_fulltext,
 3 module
 4 FROM V$SQLAREA
 5 WHERE module = 'appinfo_demo';

SQL_TEXT
----------------------------------------------------------------------------------------------------
SQL_FULLTEXT MODULE
------------------------------------------------------------------------------ – – -----------------
select * From dual
select * From dual appinfo_demo


SQL>
Share This: