SQL Plus
SQL*Plus is the most basic Oracle Database utility, with a basic command-line interface, commonly used by users, administrators, and programmers.
Command types
SQL*Plus understands five categories of text:[1]
- SQL statements
- PL/SQL blocks
- SQL*Plus internal commands, for example:
- environment control commands such as SET
- environment monitoring commands such as SHOW
- Comments
- External commands prefixed by the ! char
Scripts can include all of these components.
An Oracle programmer in the appropriately configured software environment can launch SQL*Plus, for example, by entering:
$ sqlplus scott/tiger
where the Oracle user scott
has the password tiger
. SQL*Plus then presents a prompt with the default form of:
SQL>
Interactive use can then start by entering a SQL statement (terminated by a semicolon), a PL/SQL block, or another command. For example:
SQL> select 'Hello world' as example from dual;
EXAMPLE
--------------------------------
Hello world
History
The first version of SQL*Plus was called UFI ("User Friendly Interface"). UFI appeared in Oracle database releases up to Version 4.
After Oracle programmers had added new features to UFI, its name became Advanced UFI. The name "Advanced UFI" changed to "SQL*Plus" with the release of the version 5 of Oracle.[2]
Usage
Graphical interfaces from Oracle or third parties have diminished the proportion of Oracle database end-users who depend on the SQL*Plus environment. Oracle shops typically continue to use SQL*Plus scripts for batch updating or simple reports.[citation needed]
Oracle Corporation's wrappers/gui-fications/replacements for SQL*Plus include:
- Oracle SQL*Plus Worksheet, a component of OEM[3]
- iSQL*Plus or iSQLPlus, a web-based utility[4]
- SQL Worksheet,[5] a component of Oracle SQL Developer
- SQL Workshop (part of Oracle Application Express)[6]
Oracle 11g
Starting from Oracle database 11g, iSqlplus (web based) and sqlplus GUI no longer ship with Oracle database software.[7] The command-line SQL*Plus interface continues in use, mostly[citation needed] for non-interactive scripting or for administrative purposes. The Server Manager Command Line -a replacement of SQL*DBA- is obsolete and SQL*plus 8i and later allows the user to issue statements like STARTUP and SHUTDOWN when connected as SYSDBA. Server Manager 7.1 introduced the command CONNECT / AS SYSDBA to replace CONNECT INTERNAL.[8] SQL*plus 8i and later allows the use of CONNECT / AS SYSDBA
Compatibility
Other vendors have made their software somewhat compatible with SQL*Plus script commands or offer a SQL*Plus mode of operation. Relevant products include TOAD from Quest Software.[citation needed]
Integration
![]() | This section needs expansion. You can help by adding to it. (July 2009) |
Variables
SQL*Plus-internal variables, accessible within an SQL*Plus session, include:
- user variables, displayable with the
DEFINE
command and referenceable with one or two cases of a prefixed character (default prefixes: '&' and '&&'). Oracle Corporation calls these variables "substitution variables". Programmers can use them anywhere in a SQL or PL/SQL statement or in SQL*Plus commands. They can be populated by a literal usingDEFINE
or from the database using thecolumn
command. - bind variables, prefixed by a colon (':'), which can interact with the PL/SQL environment. Displayable with the VARIABLE and PRINT commands[11]
Error trapping
The WHENEVER
command specifies an action to perform in the event of the system detecting an SQL error[12]
or an operating-system error[13]
while running a subsequent SQL*Plus command.
Supplementary software
- SQL Assistant SQL Assistant add-on for SQL*Plus Windows version extends SQL*Plus with SQL automatic word completion, in-line Oracle SQL Reference, data export/import, code unit testing, data browsing, and code development functions.
- rlwrap rlwrap does GNU Readline like command completion for SQL*Plus on UNIX and Linux.
See also
References
- ^ "SQL*Plus at orafaq.com". Archived from the original on 2007-11-09. Retrieved 2007-11-26.
- ^ Oracle Insights (1st ed.). 2004. p. 35. ISBN 978-1590593875. Retrieved March 18, 2014.
- ^ Evans, Robert (2008-10-01). "The SQL*Plus Worksheet". Cardiff University. Retrieved 2008-11-21.
Oracle's SQL*Plus Worksheet is a straight-forward, easy-to-use, graphical user interface for SQL.
{{cite web}}
: Cite has empty unknown parameters:|month=
and|coauthors=
(help) - ^ "ISQLPlus". Oracle FAQ. 2008-02-29. Retrieved 2008-11-21.
iSQLPlus (iSQL*Plus) is a web-based utility similar to the SQL*Plus command line utility for executing SQL and PL/SQL commands (available up to Oracle 10gR2).
{{cite web}}
: Cite has empty unknown parameters:|month=
and|coauthors=
(help) - ^ "Oracle SQL Developer 1.5: Feature List". Oracle Corporation. Retrieved 2008-11-21.
The SQL*Plus commands supported by Oracle SQL Developer SQL Worksheet are listed [...]
{{cite web}}
: Cite has empty unknown parameters:|month=
and|coauthors=
(help) - ^ "Oracle Application Express". Oracle Corporation. Retrieved 2008-11-21.
SQL Workshop provides tools to enable you to view and manage database objects from a Web browser. Use SQL Commands to run SQL and PL/SQL statements. ...
{{cite web}}
: Cite has empty unknown parameters:|month=
and|coauthors=
(help) - ^ Deprecated Components in Oracle Database 11g Release 1 (11.1), retrieve by 25-Jun-2009
- ^ [1] Oracle 7 doc, 1994
- ^
Alapati, Sam R. (2008). "4". Expert Oracle Database 11g Administration. Apress. pp. 118–119. ISBN 978-1-4302-1015-3. Retrieved 2009-07-29.
{{cite book}}
: Cite has empty unknown parameters:|laydate=
,|coauthors=
,|nopp=
,|month=
,|separator=
,|laysummary=
, and|lastauthoramp=
(help); External link in
(help); Unknown parameter|chapterurl=
|chapterurl=
ignored (|chapter-url=
suggested) (help) - ^
Roshak, Natalka (2005-11-06). "Spice up your SQL Scripts with Variables". Oracle FAQ. Retrieved 2009-07-29.
& and && indicate substitution variables in SQL*Plus scripts or commands.
- ^
Nyffenegger, René. "Using bind variables in SQL*Plus". René Nyffenegger's collection of things on the web. Retrieved 2009-07-29.
In SQL*Plus, a bind variable is declared with
variable
[...] The value of the bind variable can then be printed withprint
{{cite web}}
: Cite has empty unknown parameters:|month=
and|coauthors=
(help) - ^
Watt, Simon. "SQL*Plus User's Guide and Reference". Oracle Help Center. Oracle Database Online Documentation, 10g Release 2 (10.2) / Administration. Oracle. p. 74. Retrieved 2015-11-04.
WHENEVER SQLERROR [...] [d]irects SQL*Plus to perform the specified action as soon as it detects a SQL command or PL/SQL block error (but after printing the error message).
- ^
Watt, Simon. "SQL*Plus User's Guide and Reference". Oracle Help Center. Oracle Database Online Documentation, 10g Release 2 (10.2) / Administration. Oracle. p. 73. Retrieved 2015-11-04.
WHENEVER OSERROR [...] [d]irects SQL*Plus to perform the specified action as soon as an operating system error is detected.