SQL Plus: Difference between revisions

Revision as of 08:13, 28 March 2019

Revision as of 08:20, 28 March 2019

Line 1:

'''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:<ref name="CommandRef">{{cite web | url=http://www.orafaq.com/faq/sqlplus | title=SQL* Plus at orafaq.com | accessdate=2007-11-26 |archiveurl = https://web.archive.org/web/20071109104758/http://www.orafaq.com/faq/sqlplus <!-- Bot retrieved archive --> |archivedate = 2007-11-09}}</ref>

# SQL statements

# [[PL/SQL]] blocks

# SQL* Plus internal commands, for example:

#* environment control commands such as SET

#* environment monitoring commands such as SHOW

Line 14:

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 <code>scott</code> has the password <code>tiger</code>. SQL* Plus then presents a prompt with the default form of:

SQL&gt;

Line 34:

== 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.<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|accessdate=March 18, 2014}}</ref>

==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|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

|accessdate = 2008-11-21

|author =

Line 63:

|archiveurl = https://web.archive.org/web/20081201065932/http://docs.cs.cf.ac.uk/html/601/node4.html

|archivedate = 2008-12-01

|quote = Oracle's SQL* Plus Worksheet is a straight-forward, easy-to-use, graphical user interface for SQL.

|deadurl = yes

|df =

}}

</ref>

* iSQL* Plus or iSQLPlus, a web-based utility<ref>{{cite web

| url = http://www.orafaq.com/wiki/ISQLPlus

| title = ISQLPlus

Line 86:

| language =

| doi =

| 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>

Line 110:

| archivedate = 2008-07-24

| dead-url = yes

| quote = The SQL* Plus commands supported by Oracle SQL Developer SQL Worksheet are listed [...]

}}

</ref> a component of [[Oracle SQL Developer]]

Line 140:

=== 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 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.<ref>[http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SAD73/ch1.htm#selectmethod] Oracle 7 doc, 1994</ref> SQL*plus 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 (software)|TOAD]] from [[Quest Software]].<ref>{{Cite book|url=http://www.informit.com/articles/article.aspx?p=31544&seqNum=17|title=TOAD SQL Editor|last=Hotka|first=Dan|last2=Scalzo|first2=Bert|date=2003-04-18}}</ref>

== Integration ==

Line 150:

=== 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

Line 205:

|date = 2005-11-06

|accessdate = 2009-07-29

|quote = & and && indicate substitution variables in SQL* Plus scripts or commands.

}}

</ref>

Line 215:

|authorlink =

|coauthors =

|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

Line 225:

|year =

|accessdate = 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>

Line 233:

{{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

Line 241:

| 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>

Line 247:

{{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

Line 255:

| 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.

== 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 ==

Line 271:

==External links==

* [http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/toc.htm SQL* Plus documentation]

* [http://www.orafaq.com/wiki/SQL*Plus_FAQ Plus FAQ SQL* Plus FAQ]

* [http://www.sql-plus.com/ SQL* Plus tutorial]

{{Authority control}}