SQL Extensions   «Prev  Next»

Lesson 2Using the SQL*Plus Environment
ObjectiveDescribe SQL*Plus and identify when to use it.

Using SQL*Plus Environment

Oracle's SQL*Plus is a programming environment that is packaged with Oracle's database software on all platforms. It has been shipped with Oracle since the first release of the database. There are dozens of additional software packages that Oracle sells to interact with the database engine, but SQL*Plus still stands as a simple and easy-to-use tool for the programmer, developer, and database administrator.

What is SQL Plus
As you saw in a previous module, SQL*Plus has two flavors: a command line interface and a Windows-like interface. The two interfaces are very similar. Even though this course uses the windows-like interface, all the commands you learn here work identically in both interfaces.
A new tool called SQLPlus Worksheet is now shipped with the Enterprise Manager. It has a similar look and feel to SQL*Plus, but has the advantage of being capable of running within a Web browser. The next lesson describes the in-line editor that is included for use in SQL*Plus.

Enterprise Manager's SQLPlus Worksheet

Oracle is gradually going to wean its database administrators and developers off the old command line interfaces such as SVRMGR and SQL*Plus in later versions of their products. The new tools are packaged with Oracle's Enterprise Manager software. These tools are Java-based, Web-enabled, and generate and run SQL code automatically. Here you can still write and execute your own SQL and PL/SQL commands. Having used the Enterprise Manager tool set for some time now, I fully endorse this trend as a time-saver for all database administrators and developers. The only excuse left for using SQL*Plus at all is to generate reports.
Enterprise Manager's SQLPlus Worksheet is a half-way step between SQL*Plus and the set of Enterprise Manager tools that that create SQL for you. While SQLPlus Worksheet has a similar look and feel to SQL*Plus, it is missing the environment commands that make SQL*Plus a viable reporting tool. I suspect this is deliberate because there is much less demand for the report-writing capabilities of SQL*Plus today. Most people use other alternatives for report-writing, such as Oracle's Developer or one of the dozens of report-generating tools available from other vendors. Oracle is following its customers' lead by moving us away from writing our own SQL code and offering us better report-generating tools such as Oracle's WebDB, Explorer, and Developer.

Enterprise Manager Cloud Control 12c

Oracle Enterprise Manager is Oracle's integrated enterprise information technology (IT) management product line, which provides the industry's only complete, integrated, and business-driven enterprise cloud management solution. Oracle Enterprise Manager creates business value for IT by leveraging the built-in management capabilities of the Oracle stack for traditional and cloud environments, enabling customers to achieve unprecedented efficiency gains while dramatically increasing service levels. The key capabilities of Enterprise Manager include:
  1. A complete cloud lifecycle management solution enabling you to quickly set up, manage, and support enterprise clouds and traditional Oracle IT environments from applications to disk
  2. Maximum return on IT management investment through the best solutions for intelligent management of the Oracle stack and engineered systems with real-time integration of Oracle's knowledge base with each customer environment
  3. Best service levels for traditional and cloud applications through business-driven application management

Who Can Use SQL*Plus

The SQL*Plus, SQL, and PL/SQL command languages are powerful enough to serve the needs of users with some database experience, yet straightforward enough for new users who are just learning to work with the Oracle Database. The SQL*Plus language is easy to use. For example, to rename a column labelled
LAST_NAME with the heading "Family Name", enter the command:

Similarly, to list column definitions for the EMPLOYEES table, enter the command:

SQL*Plus Command-line Architecture

SQL*Plus command-line uses a two-tier model comprising:
  1. Client (command-line user interface).
  2. Database (Oracle Database).The two tiers may be on the same machine.
  3. SQL*Plus Client: The command-line user interface is the character-based terminal implementation.
  4. Oracle Database: Oracle Database Net components provide communication between the SQL*Plus Client and Oracle Database.