SQL Plus

Quality 0.50 · 1 view · Updated 2 months ago

Interactive and batch query tool

title: "SQL Plus" type: doc version: 1 created: 2026-02-28 author: "Wikipedia contributors" status: active scope: public tags: ["oracle-database", "pl/sql-editors", "sql-clients"] description: "Interactive and batch query tool" topic_path: "technology/databases" source: "https://en.wikipedia.org/wiki/SQL_Plus" license: "CC BY-SA 4.0" wikipedia_page_id: 0 wikipedia_revision_id: 0

::summary Interactive and batch query tool ::

::figure[src="https://upload.wikimedia.org/wikipedia/commons/5/5a/SqlPlus_connected.png" caption="Command line"] ::

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
  2. PL/SQL blocks
  3. SQL Plus internal commands, for example:
  • environment control commands such as SET
  • environment monitoring commands such as SHOW
  1. Comments
  2. 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:

::code[lang=psql] 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.

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.

Oracle Corporation's wrappers/gui-fications/replacements for SQL Plus include:

  • Oracle SQL Plus Worksheet, a component of OEM{{cite web |url = http://docs.cs.cf.ac.uk/html/601/node4.html |title = The SQL Plus Worksheet |access-date = 2008-11-21 |last = Evans |first = Robert |date = 2008-10-01 |publisher = Cardiff University |archive-url = https://web.archive.org/web/20081201065932/http://docs.cs.cf.ac.uk/html/601/node4.html |archive-date = 2008-12-01 |quote = Oracle's SQL Plus Worksheet is a straight-forward, easy-to-use, graphical user interface for SQL. |url-status = dead
  • iSQL Plus or iSQLPlus, a web-based utility{{cite web | url = http://www.orafaq.com/wiki/ISQLPlus | title = ISQLPlus | access-date = 2008-11-21 | date = 2008-02-29 | publisher = Oracle FAQ | quote = 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).
  • SQL Worksheet,{{cite web | url = http://www.oracle.com/technology/products/database/sql_developer/files/featurelist_1_5.htm#nf_sql | title = Oracle SQL Developer 1.5: Feature List | access-date = 2008-11-21 | publisher = Oracle Corporation | archive-url = https://web.archive.org/web/20080724133233/http://www.oracle.com/technology/products/database/sql_developer/files/featurelist_1_5.htm#nf_sql | archive-date = 2008-07-24 | url-status = dead | quote = The SQL Plus commands supported by Oracle SQL Developer SQL Worksheet are listed [...] a component of Oracle SQL Developer
  • SQL Workshop (part of Oracle Application Express){{cite web | url = http://www.oracle.com/technology/products/database/application_express/html/what_is_apex.html | title = Oracle Application Express | access-date = 2008-11-21 | publisher = Oracle Corporation | archive-url = https://web.archive.org/web/20061021062241/http://www.oracle.com/technology/products/database/application_express/html/what_is_apex.html | archive-date = 2006-10-21 | url-status = dead | quote = 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. ...

Oracle 11g

Starting from Oracle database 11g, iSqlplus (web based) and sqlplus GUI no longer ship with Oracle database software. The command-line SQL Plus interface continues in use, mostly 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 and when connected as SYSDBA. Server Manager 7.1 introduced the command to replace . 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.

Integration

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 using DEFINE or from the database using the column command.
    • predefined variables, prefixed with an underscore ('_') |last= Alapati |first= Sam R. |title= Expert Oracle Database 11g Administration |url= https://books.google.com/books?id=tdRes4IdLiIC |access-date= 2009-07-29 |year= 2008 |publisher= Apress |isbn= 978-1-4302-1015-3 |pages= 118–119 |chapter= 4 |chapter-url= https://books.google.com/books?id=tdRes4IdLiIC&q=sqlplus+predefined+variable&pg=PA118
    • substitution variables, useful for interacting with user-input |first = Natalka |last = Roshak |title = Spice up your SQL Scripts with Variables |url = http://www.orafaq.com/node/515 |work = Oracle FAQ |date = 2005-11-06 |access-date = 2009-07-29 |quote = & and && indicate substitution variables in SQL Plus scripts or commands.
  • bind variables, prefixed by a colon (':'), which can interact with the PL/SQL environment. Displayable with the VARIABLE and PRINT commands |first = René |last = Nyffenegger |title = Using bind variables in SQL Plus |url = http://www.adp-gmbh.ch/ora/sqlplus/use_vars.html |work = René Nyffenegger's collection of things on the web |access-date = 2009-07-29 |quote = In SQL Plus, a bind variable is declared with variable [...] The value of the bind variable can then be printed with print

Error trapping

The WHENEVER command specifies an action to perform in the event of the system detecting an SQL error | url = https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12052.htm | title = SQL Plus User's Guide and Reference | last1 = Watt | first1 = Simon | website = Oracle Help Center | series = Oracle Database Online Documentation, 10g Release 2 (10.2) / Administration | publisher = Oracle | page = 74 | access-date = 2015-11-04 | quote = 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). or an operating-system error | url = https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12051.htm | title = SQL Plus User's Guide and Reference | last1 = Watt | first1 = Simon | website = Oracle Help Center | series = Oracle Database Online Documentation, 10g Release 2 (10.2) / Administration | publisher = Oracle | page = 73 | access-date = 2015-11-04 | quote = WHENEVER OSERROR [...] [d]irects SQL Plus to perform the specified action as soon as an operating system error is detected. while running a subsequent SQL Plus command.

Installation and configuration

SQL*Plus needs to be installed and configured on the machine where you intend to use it. This usually involves setting up Oracle client software and configuring network connections to Oracle databases.

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.

References

References

  1. "SQL Plus at orafaq.com".
  2. (2004). "Oracle Insights". Apress.
  3. [http://download.oracle.com/docs/cd/B28359_01/install.111/b32006/whatsnew.htm#BGGBABGJ Deprecated Components in Oracle Database 11g Release 1 (11.1)], retrieve by 25-Jun-2009
  4. [http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SAD73/ch1.htm#selectmethod] Oracle 7 doc, 1994
  5. (2003-04-18). "TOAD SQL Editor".
  6. "Oracle database services and products offer customers cost-optimized and high-performance versions of Oracle Database, the world's leading converged, multi-model database management system".

::callout[type=info title="Wikipedia Source"] This article was imported from Wikipedia and is available under the Creative Commons Attribution-ShareAlike 4.0 License. Content has been adapted to SurfDoc format. Original contributors can be found on the article history page. ::

oracle-databasepl/sql-editorssql-clients