Wikipedia

SQL Plus: Difference between revisions

Browse history interactively
← Previous edit
Content deleted Content added
VisualWikitext
Link suggestions feature: 2 links added.
Tags: Visual edit Mobile edit Mobile web edit Newcomer task Suggested: add links
 
(131 intermediate revisions by more than 100 users not shown)
Line 1:
{{Lead too short|date=November 2022}}
'''SQL*Plus''' is an [[Oracle database|Oracle]] command line utility which allows users to run [[SQL]] and [[PL/SQL]] commands interactively. Complete documentation for the SQL*Plus interpreter can be found on the Oracle website.
[[File:SqlPlus connected.png|thumb|command line]]
 
'''SQL* Plus''' is athe simplemost toolbasic [[Oracle Database]] utility, with a basic [[command -line interface]], however it is commonly used asby itusers, is almost always available in any Oracleadministrators, softwareand installationprogrammers.
 
==VersionsCommand types==
SQL Plus understands five categories of text:<ref name="CommandRef">{{cite web | url=http://www.orafaq.com/faq/sqlplus | title=SQL Plus at orafaq.com | access-date=2007-11-26 |archive-url = https://web.archive.org/web/20071109104758/http://www.orafaq.com/faq/sqlplus <!-- Bot retrieved archive --> |archive-date = 2007-11-09}}</ref>
 
# SQL statements
===UFI===
# [[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.
SQL*Plus's predecessor was called UFI (User Friendly Interface). UFI was included in the first Oracle database releases up to Oracle v4.
 
An Oracle programmer in the appropriately configured software environment can launch SQL Plus, for example, by entering:
The UFI interface was extremely primitive and, in today's terms, anything but user friendly. If a statement was entered incorrectly, UFI issued an error and rolled back the entire transaction.
 
$ sqlplus scott/tiger
===Advanced UFI===
 
where the Oracle user <code>scott</code> has the password <code>tiger</code>. SQL Plus then presents a prompt with the default form of:
After new features were added to UFI, it was renamed internally to Advanced UFI. The name "Advanced UFI" was never used officially, as the name was changed to SQL*Plus before this version was released.
 
=== SQL*Plus===&gt;
SQL*Plus is the current name of the product.
 
Interactive use can then start by entering a SQL statement (terminated by a semicolon), a PL/SQL block, or another command. For example:
==afiedt.buf file==
 
<syntaxhighlight lang="psql">
AFIEDT.BUF is the SQL*Plus default edit save file. When you issue the "edit" command, the last SQL or PL/SQL command will be saved to a file called AFIEDT.BUF and opened in the default editor.
SQL> select 'Hello world' as example from dual;
 
EXAMPLE
In the early days when SQL*Plus was called UFI (User Friendly Interface) this file was initially named "ufiedt.buf", short for UFI editing buffer.
--------------------------------
Hello world
</syntaxhighlight>
 
== History ==
When the product was renamed to Advanced UIF The file name was changed to "aufiedt.buf" and then to "afiedt.buf" to keep the name short enough for compatibility with some of the odd operating systems that Oracle supported in those days.
 
The first version of SQL Plus was called ''UFI'' ("User Friendly Interface"). UFI appeared in Oracle database releases up to Version 4.
Although the product has long been renamed to SQL*Plus, the old file is still in use.
 
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.<ref>{{cite book |year=2004 |title=Oracle Insights |edition=1st |url=https://books.google.com/books?id=QE5UntUi-4oC&pg=PA35 |page=35| isbn = 978-1590593875|access-date=March 18, 2014|last1=Millsap |first1=Cary |last2=Kolk |first2=Anjo |last3=McDonald |first3=Connor |last4=Gorman |first4=Tim |last5=Hailey |first5=Kyle |last6=Ensor |first6=David |last7=Lewis |first7=Jonathan |last8=Vaidyanatha |first8=Gaja Krishna |last9=Ruthven |first9=David |last10=Morle |first10=James |publisher=Apress }}</ref>
 
==Usage==
 
Graphical interfaces from Oracle or third parties have diminished the proportion of [[Oracle Database|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|date=November 2008}}
 
[[Oracle Corporation]]'s wrappers/gui-fications/replacements for SQL Plus include:
 
* Oracle SQL Plus Worksheet, a component of [[Oracle Enterprise Manager|OEM]]<ref>{{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
}}
</ref>
* iSQL Plus or iSQLPlus, a web-based utility<ref>{{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).
}}
</ref>
* SQL Worksheet,<ref>{{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 [...]
}}
</ref> a component of [[Oracle SQL Developer]]
* SQL Workshop (part of [[Oracle Application Express]])<ref>{{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. ...
}}
</ref>
 
=== Oracle 11g ===
Starting from Oracle database 11g, iSqlplus (web based) and sqlplus GUI no longer ship with Oracle database software.<ref>[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</ref>
The command-line SQL Plus interface continues in use, mostly{{Citation needed|date=July 2009}} 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 {{mono|STARTUP}} and {{mono|SHUTDOWN}} when connected as SYSDBA. Server Manager 7.1 introduced the command {{code|2=sql|CONNECT / AS SYSDBA}} to replace {{code|2=sql|CONNECT INTERNAL}}.<ref>[http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SAD73/ch1.htm#selectmethod] Oracle 7 doc, 1994</ref> SQL Plus 8i and later allows the use of CONNECT / AS SYSDBA
 
==Compatibility==
VariousOther softwarevendors ishave knownmade totheir besoftware somewhat compatible with SQL* Plus script commands or offer a SQL* Plus mode of operation. Relevant Theseproducts include [[TOAD (software)|TOAD]] from [[Quest andSoftware]].<ref>{{Cite othersbook|url=http://www.informit.com/articles/article.aspx?p=31544&seqNum=17|title=TOAD SQL Editor|last1=Hotka|first1=Dan|last2=Scalzo|first2=Bert|date=2003-04-18}}</ref>
 
== Integration ==
{{Expand section|date=July 2009}}
 
=== Variables ===
 
SQL Plus-internal variables, accessible within an SQL Plus session, include:
 
* user variables, displayable with the <code>DEFINE</code> 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 <code>DEFINE</code> or from the database using the <code>column</code> command.
** predefined variables, prefixed with an underscore ('_')<ref>
{{cite book
|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
}}
</ref>
** substitution variables, useful for interacting with user-input<ref>
{{cite web
|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.
}}
</ref>
* bind variables, prefixed by a colon (':'), which can interact with the PL/SQL environment. Displayable with the VARIABLE and PRINT commands<ref>
{{cite web
|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 <code>variable</code> [...] The value of the bind variable can then be printed with <code>print</code>
}}
</ref>
 
=== Error trapping ===
The <code>WHENEVER</code> command specifies an action to perform in the event of the system detecting an SQL error<ref>
{{cite web
| 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).
}}
</ref>
or an operating-system error<ref>
{{cite web
| 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.
}}
</ref>
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.<ref>{{Cite web |title=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 |url=https://www.oracle.com/database/}}</ref>
 
== Supplementary software ==
* [http://www.softtree.com 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.
* [https://web.archive.org/web/20101128023958/http://utopia.knoware.nl/~hlub/uck/rlwrap/ rlwrap] rlwrap does [[GNU]] [[Readline]] like command completion for SQL Plus on [[UNIX]] and [[Linux]].
 
== See also ==
* [[Oracle SQL Developer]]
* [[Oracle SQL/PLSQL Developer]]
* [[techaid71/most-common-sqlplus-commands]]
 
==References==
{{Reflist}}
* [http://www.oracle.com/technology/docs/tech/sql_plus/index.html SQL*Plus]
* [http://orafaq.com/faqplus.htm SQL*Plus FAQ]
 
==External links==
{{database-software-stub}}
* [http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/toc.htm SQL Plus documentation]
* [http://www.orafaq.com/wiki/SQL Plus FAQ SQL Plus FAQ]
* [http://www.sql-plus.com/ SQL Plus tutorial]
 
{{Authority control}}
[[Category:Oracle software]]
 
[[es{{DEFAULTSORT:SQL*Sql Plus]]}}
[[Category:Oracle Database]]
[[fr:SQL*Plus]]
[[Category:PL/SQL editors]]
[[pl:Sqlplus]]
[[ptCategory:SQL*Plus clients]]
Retrieved from "https://en.wikipedia.org/wiki/SQL_Plus"