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.
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.
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:
SELECT employee_id, employee_name, employee_billing_rate2
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
could then issue the following command from SQL*Plus whenever you
needed to add a user to your database:
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:
@CREATE_USER ashley justin 10old 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.
SET SERVEROUTPUT ONSQL>
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
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
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 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 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.
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.