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.

SQL*Plus is a programming environment for writing SQL and PL/SQL code. You can use SQL*Plus to:

1. Run interactively in a window
2. Run in the background to execute predefined scripts
3. Execute just about any SQL command, including commands to create users, tables, tablespaces, and indexes
4. Adjust a long list of environment settings to produce formatted reports that include headers, footers, page breaks, and calculated summaries
5. Debug SQL and PL/SQL scripts by executing them, thus gathering instant feedback on the syntax

As a Database Administrator, I use SQL*Plus to generate customized SQL scripts to grant table privileges and roles to users. I also use SQL*Plus to generate Web pages that are refreshed periodically by re-running the SQL*Plus script. My favorite accomplishment is the SQL*Plus script I created that generates a complex replication trigger for any table in my database.

SQL*Plus has added the set errorlogging command to provide additional methods of trapping errors. When enabled, set errorlogging will cause errors generated by SQL, PL/SQL, and SQL*Plus to be written to a logging table (SPERRORLOG by default) for you to review. Oracle will create the SPERRORLOG table if it is not present when you enable error logging. Here is an example of the use of the set errorlogging command:

-- enable error logging
SQL> set errorlogging on
-- bad query

SQL> select * from dud;
ORA-00942: table or view does not exist
-- Here is a desc of the sperrorlog that the error messages are written to.

SQL> desc sperrorlog
Name Null? Type
----------------------------------------- -------- --------------
USERNAME VARCHAR2(256)
TIMESTAMP TIMESTAMP(6)
SCRIPT VARCHAR2(1024)
IDENTIFIER VARCHAR2(256)
MESSAGE CLOB
STATEMENT CLOB
-- Query the sperrorlog table.
SQL> select username, timestamp, statement, message from sperrorlog;
USERNAME TIMESTAMP STATEMENT
---------- ------------------------------ -----------------------------

MESSAGE
---------------------------------------
SYS 11-AUG-07 09.54.47.000000 PM select * from dud
ORA-00942: table or view does not exist

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.

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.

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

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:

COLUMN LAST_NAME HEADING 'Family Name'

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

DESCRIBE EMPLOYEES