Oracle SQL*Plus: The Definitive Guide

Chapter 1. Introduction to SQL*Plus

SQL*Plus is the command-line interface to the Oracle database. Its fundamental reason for existence is to allow you to enter and execute ad hoc SQL statements and PL/SQL code blocks. This chapter explains what SQL*Plus is, how it relates to other Oracle tools (as well as the database), and why you should master it. At the end of the chapter I’ll introduce you to the sample data, which is used for many of the examples in this book. If you like, you can load that data into your database and test out each example as you go through this book.

What Is SQL*Plus?

SQL*Plus is essentially an interactive query tool, with some scripting capabilities. It is a non-GUI, character-based tool that has been around since the dawn of the Oracle age. Using SQL*Plus, you can enter an SQL statement, such as a SELECT query, and view the results. You can also execute Data Definition Language (DDL) commands that allow you to maintain and modify your database. You can even enter and execute PL/SQL code. In spite of SQL*Plus’s age and lack of “flash,” it is a workhorse tool used day in and day out by database administrators, developers, and yes, even end users. As a database administrator, 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 now be used for many different purposes. The basic functionality is very 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 commands, such as those used to create, alter, or drop database objects such as tables, indexes, and users

• Execute SQL*Plus script files

• Write output to a file

• Execute procedures and functions that are stored in a database

While they might not seem like much, these things are the building blocks you can use to perform a variety of useful functions. Consider the ability to enter a SELECT statement and view the results. The following example shows how to do this using SQL*Plus:

SQL> SELECT employee_id, employee_name, employee_billing_rate
  2    FROM employee;

EMPLOYEE_ID EMPLOYEE_NAME                            EMPLOYEE_BILLING_RATE
----------- ---------------------------------------- ---------------------
        101 Jonathan Gennick                                           169
        102 Jenny Gennick                                              135
        104 Jeff Gennick                                                99
        105 Horace Walker                                              121
        107 Bohdan Khmelnytsky                                          45
        108 Pavlo Chubynsky                                            220
        110 Ivan Mazepa                                                 84
        111 Taras Shevchenko                                           100
        112 Hermon Goche                                                70
        113 Jacob Marley                                               300

10 rows selected.

Combine this capability with SQL*Plus’s formatting abilities and you can turn the above query into a very credible looking report, complete with page titles, page numbers, column titles, and nicely formatted output. That report might look something like this:

Employee Listing               Page  1

                               Billing
 Emp ID Name                      Rate
------- -------------------- ---------
    101 Jonathan Gennick           169
    102 Jenny Gennick              135
    104 Jeff Gennick                99
    105 Horace Walker              121
    107 Bohdan Khmelnytsky          45
    108 Pavlo Chubynsky            220
    110 Ivan Mazepa                 84
    111 Taras Shevchenko           100
    112 Hermon Goche                70
    113 Jacob Marley               300

Another twist on the same theme is to format the output so you get a comma-delimited list of values. That output could look like this:

101,"Jonathan Gennick",169
102,"Jenny Gennick",135
104,"Jeff Gennick",99
105,"Horace Walker",121
107,"Bohdan Khmelnytsky",45
108,"Pavlo Chubynsky",220
110,"Ivan Mazepa",84
111,"Taras Shevchenko",100
112,"Hermon Goche",70
113,"Jacob Marley",300

Using the SQL*Plus SPOOL command, you could write this output to a file, and later load it into a program such as Microsoft Excel for further manipulation. 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. Creating a new user for example, can be accomplished by the following statement:

CREATE USER ashley IDENTIFIED BY some_password;

Of course, it’s rare that you would only issue one command when you add a new user. Usually you want to assign a default tablespace and a quota on that tablespace. You may also want to grant the privileges needed to connect to the database. Whenever you have a task that requires a sequence of commands to be executed, you can simplify things by taking advantage of SQL*Plus’s scripting capabilities. The following commands, when placed in a script file, allow you to add a new user with just one command:

CREATE USER &&1 IDENTIFIED BY &&2
   DEFAULT TABLESPACE USER_DATA
   TEMPORARY TABLESPACE TEMPORARY_DATA
   QUOTA UNLIMITED ON TEMPORARY_DATA
   QUOTA &&3.m ON USER_DATA;

GRANT CONNECT TO &&1;

Assuming you named the file CREATE_USER.SQL, you could then issue the following command from SQL*Plus whenever you needed to add a user to your database:

@CREATE_USER username password quota

The following example shows how this works, by creating a user named ASHLEY, with a password of JUSTIN, and a quota of 10 megabytes in the USER_DATA tablespace:

SQL> @CREATE_USER ashley justin 10
old   1: CREATE USER &&1 IDENTIFIED BY &&2
new   1: CREATE USER ashley IDENTIFIED BY justin
old   5:    QUOTA &&3.m ON USER_DATA
new   5:    QUOTA 10m ON USER_DATA

User created.

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

Grant succeeded.

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

To write really complicated scripts, you can take advantage of Oracle’s built-in procedural language, PL/SQL. The following example shows how a PL/SQL block can be executed using SQL*Plus:

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

Once you know how to use SQL*Plus to perform the basic functions just described, you can leverage them to do the things described in this book. This includes:

• Producing reports with SQL*Plus

• Writing scripts that can be executed with SQL*Plus

• Using SQL*Plus to extract data to a text file

• Examining the structure of your database by querying the data dictionary tables

• Tuning queries using the EXPLAIN PLAN command

There are chapters in this book covering each of the above topics in detail. A lot can be accomplished with SQL*Plus. This book will show you how.

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 gives 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, which stands for Structured Query Language, is an ANSI (and ISO) standard language used for querying, modifying, and managing relational databases. It is used to insert, delete, update, and retrieve data.
PL/SQL	PL/SQL is a proprietary procedural language developed by Oracle as an extension to SQL. Like SQL, it also executes inside the database. It was created as a tool for coding business rules and procedures at the database level.
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 relationship between them and about which commands get executed where. SQL*Plus does have its own set of commands it recognizes and executes, but any SQL queries, DDL commands, and PL/SQL blocks are sent to the database server for execution. Figure 1.1 illustrates this relationship.

Figure 1-1. Relationship between 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.