SQL Plus: Difference between revisions

Revision as of 23:10, 2 November 2020

Latest revision as of 06:51, 15 August 2025

(18 intermediate revisions by 13 users not shown)

Line 1:

{{Lead too short|date=November 2022}}

[[File:SqlPlus connected.png|thumb|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:<ref name="CommandRef">{{cite web | url=http://www.orafaq.com/faq/sqlplus | title=SQL Plus at orafaq.com | accessdateaccess-date=2007-11-26 |archiveurlarchive-url = https://web.archive.org/web/20071109104758/http://www.orafaq.com/faq/sqlplus <!-- Bot retrieved archive --> |archivedatearchive-date = 2007-11-09}}</ref>

# SQL statements

Line 36 ⟶ 39:

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

Line 50 ⟶ 53:

|last = Evans

|first = Robert

|authorlink =

|date = 2008-10-01

|publisher = Cardiff University

|___location =

|pages =

|doi =

|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

|df =

}}

</ref>

Line 66 ⟶ 64:

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

| title = ISQLPlus

| accessdateaccess-date = 2008-11-21

| author =

| last =

| first =

| authorlink =

| date = 2008-02-29

| publisher = Oracle FAQ

| ___location =

| pages =

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

}}

Line 84 ⟶ 74:

| title = Oracle SQL Developer 1.5: Feature List

| access-date = 2008-11-21

| author =

| last =

| first =

| authorlink =

| date =

| year =

| publisher = Oracle Corporation

| ___location =

| pages =

| language =

| doi =

| 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

Line 105 ⟶ 85:

| title = Oracle Application Express

| access-date = 2008-11-21

| author =

| last =

| first =

| authorlink =

| date =

| year =

| publisher = Oracle Corporation

| ___location =

| pages =

| language =

| doi =

| 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

Line 125 ⟶ 95:

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

Line 144 ⟶ 114:

|title= Expert Oracle Database 11g Administration

|url= https://books.google.com/books?id=tdRes4IdLiIC

|accessdateaccess-date= 2009-07-29

|year= 2008

|publisher= Apress

Line 150 ⟶ 120:

|pages= 118–119

|chapter= 4

|chapterurlchapter-url= https://books.google.com/books?id=tdRes4IdLiIC&q=sqlplus+predefined+variable&pg=PA118

}}

</ref>

Line 157 ⟶ 127:

|first = Natalka

|last = Roshak

|authorlink =

|title = Spice up your SQL Scripts with Variables

|url = http://www.orafaq.com/node/515

|work = Oracle FAQ

|publisher =

|___location =

|doi =

|date = 2005-11-06

|accessdateaccess-date = 2009-07-29

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

}}

Line 173 ⟶ 139:

|first = René

|last = Nyffenegger

|authorlink =

|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

|publisher access-date = 2009-07-29

|___location =

|doi =

|date =

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

}}

Line 217 ⟶ 177:

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