Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Chapter 1. Introduction to SQL*Plus

SQL*Plus is the command-line interface to the Oracle database. It exists to let you enter and execute ad hoc SQL statements and PL/SQL code blocks, and, if you're a database administrator (DBA), to issue database administration commands such as STARTUP and SHUTDOWN. This chapter explains what SQL*Plus is, how it relates to the Oracle database, and why you should master it. At the end of the chapter, I'll introduce you to the sample data used for many of the examples in this book. You can load the data and follow along as you read.

What Is SQL*Plus?

SQL*Plus is essentially an interactive query tool with some scripting capabilities. You can enter a SQL statement, such as a SELECT query, and view the results. You can execute data definition language (DDL) statements to create tables and other objects. DBAs can use SQL*Plus to start up, shut down, and otherwise administer a database. You can even enter and execute PL/SQL code.

SQL*Plus is primarily a command-line application, but, despite its lack of "flash," it is a workhorse tool used daily by database administrators, developers, and yes, even end users. As a DBA, it is my tool of choice for managing the databases under my care. I use it to peek under the hood—to explore the physical implementation of my database, and to create and manage users, tables, and tablespaces. In my role as a developer, SQL*Plus is the first tool that I fire up when I need to develop a query. In spite of all the fancy, GUI-based SQL generators contained in products such as PowerBuilder, Clear Access, and Crystal Reports, I still find it quicker and easier to build up and test a complex query in SQL*Plus before transferring it to whatever development tool I am using.

Uses for SQL*Plus

Originally developed simply as a way to enter queries and see results, SQL*Plus has been enhanced with scripting and formatting capabilities and can be used for many different purposes. The basic functionality is simple. With SQL*Plus, you can do the following:

• Issue a SELECT query and view the results.

• Insert, update, and delete data from database tables.

• Submit PL/SQL blocks to the Oracle server for execution.

• Issue DDL statements, such as those used to create, alter, or drop database objects (e.g., tables, indexes, and users), as well as any other types of SQL statements that Oracle supports.

• Execute SQL*Plus script files.

• Write output to a file.

• Execute procedures and functions that are stored in a database.

While these operations may not seem significant, they are the building blocks you can use to perform various useful functions.

Consider the ability to enter a SELECT statement and view the results. Example 1-1 shows how to do this using SQL*Plus.

Example 1-1. Executing a query in SQL*Plus

SQL> SELECT employee_id, employee_name, employee_billing_rate
  2    FROM employee;
     
EMPLOYEE_ID EMPLOYEE_NAME                            EMPLOYEE_BILLING_RATE
----------- ---------------------------------------- ---------------------
        101 Marusia Churai                                             169
        102 Mykhailo Hrushevsky                                        135
        104 Pavlo Virsky                                                99
        105 Mykola Leontovych                                          121
        107 Lesia Ukrainka                                              45
        108 Pavlo Chubynsky                                            220
        110 Ivan Mazepa                                                 84
        111 Taras Shevchenko                                           100
        112 Igor Sikorsky                                               70
        113 Mykhailo Verbytsky                                         300

Combine this capability with SQL*Plus's formatting abilities and you can turn these results into a credible-looking report, such as that shown in Example 1-2, complete with page titles, page numbers, column titles, and nicely formatted output.

Example 1-2. A SQL*Plus formatted report

Employee Listing                Page  1

                                Billing
    Emp ID Name                    Rate
---------- ------------------- --------
       101 Marusia Churai       $169.00
       102 Mykhailo Hrushevsky  $135.00
       104 Pavlo Virsky          $99.00
       105 Mykola Leontovych    $121.00
       107 Lesia Ukrainka        $45.00
       108 Pavlo Chubynsky      $220.00
       110 Ivan Mazepa           $84.00
       111 Taras Shevchenko     $100.00
       112 Igor Sikorsky         $70.00
       113 Mykhailo Verbytsky   $300.00

Another twist on the same theme is to format the output as a list of comma-separated values, such as that shown in Example 1-3.

Example 1-3. Comma-separated values from SQL*Plus

101,"Marusia Churai",169
102,"Mykhailo Hrushevsky",135
104,"Pavlo Virsky",99
105,"Mykola Leontovych",121
107,"Lesia Ukrainka",45
108,"Pavlo Chubynsky",220
110,"Ivan Mazepa",84
111,"Taras Shevchenko",100
112,"Igor Sikorsky",70
113,"Mykhailo Verbytsky",300

Using the SQL*Plus SPOOL command, which you'll read more about in Chapter 5, you can write this output to a .csv file easily readable by most, if not all, spreadsheet programs. In fact, if you are running Microsoft Windows with Microsoft Office installed, simply double-clicking on a .csv file will open that file in Microsoft Excel, where you can further manipulate the data.

Beginning with SQL*Plus in Oracle8i Database, you can use the SET MARKUP HTML command to generate HMTL output, such as that shown in Example 1-4.

Example 1-4. A SQL*Plus report formatted in HTML

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; 
charset=US-ASCII">
<meta name="generator" content="SQL*Plus 10.1.0">
<style type='text/css'> body {font:10pt Arial,Helvetica,
sans-serif; color:black; background:White;}
...
<tr>
<td align="right">
       101
</td>
<td>
Marusia Churai
</td>
<td align="right">
 $169.00
</td>
</tr>
<tr>
<td align="right">
       102
</td>
<td>
Mykhailo Hrushevsky
</td>
...

By writing such HTML output to a file, you can easily generate ad hoc reports for users to view from a corporate intranet. One DBA whom I spoke with regularly refreshes the phone list on his departmental intranet using this mechanism. Figure 1-1 shows the output in Example 1-4 as you would see it rendered in a browser.

Figure 1-1. A SQL*Plus-generated HTML report rendered in a browser

It's a small leap from executing only queries to executing any other SQL statement. In fact, SQL*Plus will let you execute any valid SQL statement and is frequently used during database maintenance tasks. For example, you can create a new user with the following statement:

CREATE USER sql_dude IDENTIFIED BY some_password;

Of course, it's rare that you would issue such a simple statement, or just one statement, when you add a new user. Usually, you also want to assign a default tablespace and often a quota on that tablespace. You may also want to grant the privilege needed to connect to the database. Whenever you have a task that requires a sequence of statements to be executed, you can simplify things by taking advantage of SQL*Plus's scripting capabilities. The statements in Example 1-5, when placed in a script file, allow you to add a new user with just one command.

Example 1-5. Script to create a new database user

CREATE USER &&1 IDENTIFIED BY &&2
   DEFAULT TABLESPACE users
   TEMPORARY TABLESPACE temp
   QUOTA &&3.M ON users;
     
GRANT CONNECT TO &&1;

The &&1, &&2, and &&3 in Example 1-5 are SQL*Plus user variables marking the locations at which to insert parameters that you pass to the script. Assuming that you give the name create_user.s ql to the file shown in Example 1-5, and assuming that you are the DBA, you can issue the following command from SQL*Plus whenever you need to add a user to your database:

@create_user username password quota

Example 1-6 shows how this works, by creating a user named sql_dude with a password of yooper and a quota of 10 megabytes.

Example 1-6. Running a script to create a new database user

SQL> @ex1-5 sql_dude yooper 10
old   1: CREATE USER &&1 IDENTIFIED BY &&2
new   1: CREATE USER sql_dude IDENTIFIED BY yooper
old   4:    QUOTA &&3.M ON users
new   4:    QUOTA 10M ON users

User created.

old   1: GRANT CONNECT TO &&1
new   1: GRANT CONNECT TO sql_dude

Grant succeeded.

The output you see is SQL*Plus showing you the before and after version of each line containing a SQL*Plus user variable. You will read more about user variables and the subject of scripting in Chapter 8.

To write complicated scripts, you can take advantage of Oracle's built-in procedural language, PL/SQL. Example 1-7 shows a simple PL/SQL block being executed from SQL*Plus.

Example 1-7. "Hello World" written as a PL/SQL block and executed from SQL*Plus

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE('Hello World!');
  3  END;
  4  /
Hello World!

You've just seen several examples of what can be done using SQL*Plus to generate simple text reports, perform database administration tasks, extract data, generate HTML reports, run automated scripts, and otherwise make your life easier. Subsequent chapters delve deeply into each of these areas and more.

SQL*Plus's Relation to SQL, PL/SQL, and the Oracle Database

SQL*Plus is often used in conjunction with two other products, both of which have the letters "SQL" in their names. The first is SQL itself. Without a doubt, the most common use of SQL*Plus is to submit SQL statements to the database for execution. The second product is Oracle's PL/SQL procedural language. Table 1-1 provides a short summary of each of these three products.

Table 1-1. The three SQLs: SQL, PL/SQL, and SQL*Plus

Product	Description
SQL	SQL is an ANSI and ISO standard language used to insert, delete, update, and retrieve data from relational databases. SQL is also used to manage relational databases.
PL/SQL	PL/SQL is a proprietary procedural language developed by Oracle as an extension to SQL, for use in coding business rules and other procedural logic at the database level. Like SQL, PL/SQL executes inside the database engine.
SQL*Plus	SQL*Plus is an Oracle-developed tool that allows you to interactively enter and execute SQL commands and PL/SQL blocks.

Because these three products all have "SQL" as part of their names, people occasionally get confused about the relationships among them and about which statements get executed where. SQL*Plus does have its own set of commands that it recognizes and executes (for example, SET SERVEROUTPUT ON from Example 1-7), but any SQL statements and PL/SQL blocks are sent to the database server for execution. Figure 1-2 illustrates this relationship.

Figure 1-2. Relationships among SQL*Plus, SQL, and PL/SQL

Think of SQL*Plus as kind of a middleman, standing between you and Oracle and helping you to communicate with your database. You type in a SQL query, SQL*Plus takes it and sends it to the database, the database returns the results to SQL*Plus, and SQL*Plus displays those results in a format you can understand.