O'Reilly Media | Oracle SQL*Plus: The Definitive Guide

Content preview·Buy reprint rights for this chapter

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:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

SQL*Plus has been around for a long time, pretty much since the beginning of Oracle. In fact, the original author was Bruce Scott. Any DBA will recognize the name Scott. It lives on, immortalized as the owner of the demo tables that are installed with every version of Oracle. The original purpose of SQL*Plus can be summed up in the succinct words of Kirk Bradley, another early author of SQL*Plus, who told me, "We needed a way to enter statements into the database and get results."

This is still arguably the major reason most people use SQL*Plus today, over fifteen years after it was originally written. SQL*Plus certainly satisfies a compelling, and enduring, need.

The original name of the product was not SQL*Plus. The original name was UFI, which stands for User Friendly Interface. This name has its roots in one of the first relational database systems ever developed, IBM's System R. System R was the product of a research effort by IBM. Some of IBM's documents referred to the command-line interface as the User Friendly Interface, and that name was adopted by Oracle for their interactive SQL utility.

One of the more interesting uses Oracle had for UFI was as a tool to produce their documentation. The DOCUMENT command, now considered obsolete, was used for this purpose. Script files were created that contained the manual text, interspersed with the SQL statements needed for the examples. The DOCUMENT command was used to set off the manual text so that it would just be copied to the output file. When these scripts were run, the text was copied, the SQL statements were executed, and the result was documentation complete with examples.

UFI was used extensively in Oracle's internal testing and QA efforts. SQL*Plus still plays a significant role in Oracle's testing, even today.

SQL*Plus maintains a fascinating relic from the old days in the form of the SET TRIMOUT, SET TRIMSPOOL, and SET TAB commands. These commands control the printing of trailing spaces and the use of tabs to format columnar output. To understand why these commands even exist, you have to realize that when SQL*Plus first made its appearance, people thought a dial-up speed of 1200 bps was

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

SQL*Plus is a universal constant in the Oracle world. Every installation I have ever seen has this tool installed. For that reason alone it is worth learning. Even if you prefer to use other tools, such as Enterprise Manager or Clear Access, you may not always have them available. In my work as a consultant I frequently visit clients to help them with Oracle. Some clients use GUI-based query tools, some don't. Some clients use Oracle Enterprise Manager, some don't. The one universal constant I can count on is the availability of SQL*Plus. The last thing I want is to be at a client site needing to look up something mundane such as an index definition, and not be able to do it because I'm not familiar with their tools. SQL*Plus is always there.

If you are a database administrator, SQL*Plus is undoubtedly a tool you already use on a daily basis. Anything you use that often is worth learning and learning well. You undoubtedly use SQL*Plus to query Oracle's data dictionary tables in order to understand the structure of your database. SQL*Plus can be used to automate that task. Sometimes it's difficult to remember the specific data dictionary tables you need to join together in order to get the information you want. With SQL*Plus, you can figure this out once and encapsulate that query into a script. Next time you need the same information, you won't have all the stress of trying to remember how to get it, and you won't have to waste time rereading the manuals in order to relearn how to get it.

SQL*Plus is also very useful for automating some routine DBA tasks. I have several SQL*Plus scripts (a script is a file of SQL statements and SQL*Plus commands) that produce reports on users and the database and object privileges these users have. I use these scripts to run periodic security audits on our database. I have scripts that report on tablespace usage, to help me keep on top of free space or the lack thereof. I also have scripts that run nightly to perform various maintenance tasks.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Many of the examples in this book, particularly the reporting examples, have been developed against a sample time-tracking database. It's a fairly simplistic database, containing only three tables, but it's enough to illustrate everything I talk about in this book. You may or may not wish to create this database for yourself. Creating the database will allow you to try all the examples in this book exactly as they are shown. If you choose not to create and load the sample database, at least familiarize yourself with the data model. Also glance at the sample data itself, which is reproduced later in this section. If you have looked at the model and at the data, you shouldn't have any trouble following and understanding the examples in this book.

Figure 1.2 shows an Entity Relationship Diagram, or ERD, for the sample database.

Figure 1.2: The sample database

As you can see from the ERD, there are only three entities: EMPLOYEE, PROJECT, and PROJECT_HOURS. Table 1.2 gives a brief description of each entity.

Table 1.2: Entity Descriptions
Entity Name	Description
EMPLOYEE	Contains one record for each employee. This record contains the employee's name, hire date, termination date, and billing rate. The primary key is an arbitrary employee ID number. The termination date for current employees is set to NULL.
PROJECT

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This chapter shows you the rudiments of interacting with SQL*Plus. The first part of this chapter shows how to start SQL*Plus and connect to a database. You will then read about a few basic SQL*Plus commands you can use to get started. Following that are some examples showing how to enter SQL statements and PL/SQL blocks. The remainder of the chapter focuses on editing your SQL and PL/SQL. There is extensive coverage of the built-in line-editing commands, and you will also see how you can work with an external editor such as Windows' Notepad.

How you start SQL*Plus varies somewhat between operating systems, and also depends on whether you are operating in a GUI environment such as Windows NT or a command-line environment such as DOS or Unix. Generally speaking though, if you are using a command-line operating system, you use the SQLPLUS command. On case-sensitive operating systems, such as Unix, the command must be entered in lowercase as sqlplus . If you are using Windows, or another GUI-based operating system, you click or double-click on an icon.

There are two variations of SQL*Plus, command-line and GUI. The command-line implementation was the original, and is the one shipped with Oracle for Unix, VMS, and other command-line operating systems. A command-line version even ships with the Windows client software, in case you want to invoke SQL*Plus from a DOS prompt. For Microsoft Windows, Oracle has also developed a GUI implementation, which wraps the SQL*Plus command-line environment in a standard window complete with scrollbars, drop-down menus, a close box, and other GUI adornments.

At first glance, the GUI version doesn't appear to add anything extra, because it only implements a simple, scrolling window into which you type commands. But there are some advantages to using it. The GUI version implements cut and paste functionality, allows you to size the window any way you want, and implements a scrollback buffer so you never have to worry about your query results scrolling off the top of the screen. If you are running under Windows, I highly recommend using the GUI version. Once you get used to using SQL*Plus with a 1000-line scrollback buffer and a large vertical window size, you won't want to go back.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

How you start SQL*Plus varies somewhat between operating systems, and also depends on whether you are operating in a GUI environment such as Windows NT or a command-line environment such as DOS or Unix. Generally speaking though, if you are using a command-line operating system, you use the SQLPLUS command. On case-sensitive operating systems, such as Unix, the command must be entered in lowercase as sqlplus . If you are using Windows, or another GUI-based operating system, you click or double-click on an icon.

There are two variations of SQL*Plus, command-line and GUI. The command-line implementation was the original, and is the one shipped with Oracle for Unix, VMS, and other command-line operating systems. A command-line version even ships with the Windows client software, in case you want to invoke SQL*Plus from a DOS prompt. For Microsoft Windows, Oracle has also developed a GUI implementation, which wraps the SQL*Plus command-line environment in a standard window complete with scrollbars, drop-down menus, a close box, and other GUI adornments.

At first glance, the GUI version doesn't appear to add anything extra, because it only implements a simple, scrolling window into which you type commands. But there are some advantages to using it. The GUI version implements cut and paste functionality, allows you to size the window any way you want, and implements a scrollback buffer so you never have to worry about your query results scrolling off the top of the screen. If you are running under Windows, I highly recommend using the GUI version. Once you get used to using SQL*Plus with a 1000-line scrollback buffer and a large vertical window size, you won't want to go back.

Before talking about how to start SQL*Plus, it's important to understand how you authenticate yourself to Oracle in order to connect to a database. There are two options here:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Now that you know how to start SQL*Plus, it's time to learn a few basic commands. This section will show you how to exit SQL*Plus, how to switch your database connection to another database, how to get help, and how to view a database table definition.

All SQL*Plus commands are case-insensitive; you may enter them using either lowercase or uppercase. In this book, commands are shown in uppercase to make them stand out better in the examples. Be aware, however, that when a filename is used as an argument to a command, it may or may not be case-sensitive, depending on the specific operating system you are using. For example, under Windows NT, filenames are not case-sensitive, but under Unix, they are.

A good place to start, since you've just seen how to start SQL*Plus, might be with the EXIT command. The EXIT command terminates your SQL*Plus session, and either closes the SQL*Plus window (GUI version) or returns you to the operating system prompt. Used in its simplest form, the EXIT command looks like this:

SQL> EXIT
Disconnected from Personal Oracle8 Release 8.0.3.0.0 - Production
With the Partitioning option
PL/SQL Release 8.0.3.0.0 - Production

There are some optional arguments to the EXIT command, which may be used to return success or failure values to the operating system. These are useful when running scripts in batch mode, and are described fully in Chapter 7.

The PASSWORD command allows you to change your database password.

The PASSWORD command was introduced beginning with SQL*Plus version 8. In prior versions, you need to use the ALTER USER command to change a password. To change other people's passwords, you need the ALTER USER system privilege.

The syntax for the PASSWORD command is:

PASSWORD [username]

where:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Using SQL*Plus, you can execute any SQL query or command that you desire. This includes data manipulation commands such as INSERT, UPDATE, DELETE, and SELECT. This also includes data definition commands such as CREATE TABLE, CREATE INDEX, CREATE USER, etc. Essentially, you can execute any command listed in the Oracle SQL reference manual.

Here is an example of a simple SELECT statement against the PROJECT table:

SQL> SELECT *  /* All Columns */
 2    FROM project;

PROJECT_ID PROJECT_NAME                             PROJECT_BUDGET
---------- ---------------------------------------- --------------
      1001 Corporate Web Site                              1912000
      1002 Year 2000 Fixes                               999998000
      1003 Accounting System Implementation                 897000
      1004 Data Warehouse Maintenance                       294000
      1005 TCP/IP Implementation                            415000

Look again at the SELECT query shown above. Notice that the statement spans more than one line. Notice that it contains an embedded comment. Notice that it ends with a semicolon. All of these things are important because they illustrate the following rules for entering SQL statements:

SQL statements may span multiple lines.
Line breaks may occur anywhere SQL allows whitespace, but blank lines are not allowed.
Comments, delimited by /*...*/, may be embedded anywhere whitespace is allowed. A comment entered this way may span multiple lines.
SQL statements must be terminated in one of three ways:
- The statement may end with a trailing semicolon.
- The statement may end with a forward slash character, but the forward slash must be on a line by itself and it must be in column 1 of that line.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

PL/SQL is a programming language developed by Oracle as an extension to SQL in order to allow procedural logic to be implemented at the database level. PL/SQL is used to write stored procedures, stored functions, and triggers, and, beginning with Oracle8, to define object types. It can also be used to simply write a block of procedural code for the database to execute. SQL*Plus was originally one of the only front-ends that could be used to send PL/SQL code to the database, and even today it is still one of the most widely used.

This section explains the mechanics of entering and executing PL/SQL code with SQL*Plus. You'll learn what PL/SQL mode is, and you'll learn the differences between entering a PL/SQL block and a SQL query.

If you are unfamiliar with PL/SQL, you may want to pick up a copy of Steven Feuerstein and Bill Pribyl's book, Oracle PL/SQL Programming, second edition (O'Reilly & Associates, 1997). PL/SQL opens up a world of possibilities. You'll want to take advantage of it if you are doing serious work with Oracle.

The PL/SQL block is the fundamental unit of PL/SQL programming. The term block refers to a program unit that contains some or all of the following elements:

Variable and subprogram declarations
Procedural code, which may include nested PL/SQL blocks
An error handler

Here is an example of a reasonably simple, but complete, PL/SQL block:

DECLARE
	X	VARCHAR2(12) := 'Hello World!';
BEGIN
	DBMS_OUTPUT.PUT_LINE(X);
EXCEPTION
WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;

This code contains all the elements of a PL/SQL block, and is one implementation of the traditional "Hello World!" program. Using SQL*Plus, you can send it to the database for execution.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

SQL*Plus keeps a copy of the most recently entered SQL statement or PL/SQL block in an internal memory area known as the SQL buffer, often referred to simply as the buffer. The reason for this is simple. SQL*Plus needs a place to store your statement or block until you are finished entering it. SQL*Plus also provides you with the ability to edit the statement in the buffer. This can be a real convenience if you make a mistake halfway through typing a long, multiline query.

SQL*Plus buffers SQL statements and PL/SQL blocks, but not SQL*Plus commands. For example, the DESCRIBE command would not be buffered, but a SELECT statement would be. To help make the distinction, it may help to think in terms of where the command is executed. If you enter something to be executed by the database server, then it is buffered. If it's a command local to SQL*Plus, then it is not buffered.

SQL*Plus provides two ways to edit the statement currently in the buffer. The first method is to use the set of line-editing commands built into SQL*Plus. The second method is to use the EDIT command to invoke an operating system-specific text editor, such as Notepad in the Windows environment, or vi under Unix.

If you are editing a statement in the buffer, be sure you don't forget yourself and execute any other SQL command. Even a simple SQL command like COMMIT will overwrite the buffer. Commands to SQL*Plus, such as the editing commands, do not affect the buffer.

There are some other useful things you can do because of the buffer. If you have several similar SQL statements to execute, using the buffer can save you the effort of completely typing out each one. You may be able to enter the first statement, execute it, make a minor change, execute the new statement, and so on until you are done. SQL*Plus also allows you to save and load the buffer to and from a text file, allowing you to store frequently executed statements for later use.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The concept of line editing goes way back to the days when all many people had to work with were dumb terminals that didn't allow full-screen editing, and connection speeds were so slow that full-screen editing would have been very painful anyway. A good line editor will allow you to work productively at speeds as low as 300 bits per second. While this isn't much of a concern today, it accurately reflects the environment at the time SQL*Plus was first conceived.

The line-editing process in SQL*Plus follows these steps:

You enter a SQL statement or PL/SQL block, which SQL*Plus stores in the buffer.
You then list the contents of the buffer to the screen.
You enter SQL*Plus commands that tell SQL*Plus to make changes to the statement in the buffer.
You list the buffer again.
If you like what you see, you execute the statement; otherwise, you go back to step three and make some more changes.

I can remember that in my younger days my fellow programmers and I always took a great pride in the number of line-editing changes we could make and visualize in our heads before we had to break down and list our code again.

When working with the line editor in SQL*Plus, it's important to understand the concept of the current line. Simply put, the current line is the one that you have most recently "touched." When you are entering a statement, the most recently entered line is the current line. Consider the following SQL statement for example:

SQL> SELECT employee_name, project_name
  2    FROM employee, project, project_hours
  3   WHERE employee.employee_id = project_hours.employee_id
  4    AND project_hours.project_id = project.project_id
  5   GROUP BY employee_name, project_name
  6  
SQL>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Once you get a statement into the buffer and have edited it the way you want it, the next step is to execute that statement. That can be done with one of the following two methods:

Type a forward slash on a line by itself, then press ENTER.
Use the RUN command, which may be abbreviated to R.

The only difference between using RUN and / is that the RUN command lists the contents of the buffer before executing it, while the / command simply executes the command without relisting it. Assume that you have the SQL statement shown next in the buffer, which you will if you have followed through all the examples in this chapter.

SQL> L
  1    SELECT employee_name,  project_name
  2      FROM employee, project, project_hours
  3     WHERE employee.employee_id = project_hours.employee_id
  4     AND project_hours.project_id = project.project_id
  5*    GROUP BY employee_name, project_name

Here is how you would run it using the / command:

SQL> /

EMPLOYEE_NAME                            PROJECT_NAME
---------------------------------------- ----------------------------------
Bohdan Khmelnytsky                       Accounting System Implementation
Bohdan Khmelnytsky                       Corporate Web Site
Bohdan Khmelnytsky                       Data Warehouse Maintenance
Bohdan Khmelnytsky                       TCP/IP Implementation
Bohdan Khmelnytsky                       Year 2000 Fixes
...

Now, here is how you would execute it using the RUN command, which in the following example is abbreviated to R:

SQL> R
  1    SELECT employee_name,  project_name
  2      FROM employee, project, project_hours
  3     WHERE employee.employee_id = project_hours.employee_id
  4     AND project_hours.project_id = project.project_id
  5*    GROUP BY employee_name, project_name

EMPLOYEE_NAME                            PROJECT_NAME
---------------------------------------- -----------------------------------
Bohdan Khmelnytsky                       Accounting System Implementation
Bohdan Khmelnytsky                       Corporate Web Site
Bohdan Khmelnytsky                       Data Warehouse Maintenance
Bohdan Khmelnytsky                       TCP/IP Implementation
Bohdan Khmelnytsky                       Year 2000 Fixes
...

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

SQL*Plus allows you to save the contents of the buffer to a file and to read that file back again. If you have built up a long and complicated SQL statement, you can save it for reuse later, and save yourself the bother of figuring it all out again. Two commands, GET and SAVE, are provided for this purpose.

The SAVE command allows you to save the current contents of the buffer to a file. Here is the syntax for SAVE:

SAVE filename [CREATE|REPLACE|APPEND]

where:

SAV[E]: May be abbreviated SAV.
filename: Is the name of the file to which you want to save the buffer. The default extension is .SQL, but you may specify another if you like.
CRE[ATE]: Tells SQL*Plus that you want to create a new file. The save will fail if the file you've specified already exists. This is the default behavior.
REPLACE: Tells SQL*Plus to save the buffer to the file specified, regardless of whether or not that file already exists. If the file does exist, it will be overwritten.
APPEND: Tells SQL*Plus to add the contents of the buffer to an existing file.

The following example shows the SAVE command being used to save the contents of a rather long SQL query to a file. First the query is entered into the buffer without being executed; then the SAVE command is used to write the buffer to a file.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You don't like line editing? SQL*Plus does not have a built-in full-screen editor, but it does have the EDIT command. The SQL*Plus EDIT command allows you to invoke the text editor of your choice to use in editing SQL statements. The specific editor invoked depends on the operating system, and on whether or not you've changed the default. The default editor under Windows NT/95 is Notepad, while under Unix it is vi. You may, however, configure SQL*Plus to use another editor of your choice. Do this by defining the user variable named _EDITOR to point to executable of the editor you want to use.

You invoke the editor with the EDIT command. The syntax looks like this:

ED[IT] [filename]

where:

ED[IT]: May be abbreviated ED.
filename: Is an optional argument indicating a specific file you want to edit. The default extension is .SQL, but you may supply a different extension if you like.

The typical use of the EDIT command is to edit a statement currently contained in the buffer. The following example shows a query being entered and the editor being invoked:

SQL> SELECT project_name
  2    FROM projects
  3   WHERE project_id in (
  4           SELECT DISTINCT project_id
  5             FROM project_hours)
  6  
SQL> EDIT
Wrote file afiedt.buf

Upon entering the EDIT command, the contents of the buffer are written to a file named AFIEDT.BUF , and the editor is invoked. Figure 2.7 shows what your screen would now look like on a Windows 95 or NT system. On Unix systems, the filename is lowercase, and will be afiedt.buf.

The filename AFIEDT.BUF is simply a work file used by SQL*Plus to hold your command while it is being edited. The name is a throwback to the very early days of SQL*Plus when it was briefly known as AUFI, which stood for Advanced User Friendly Interface.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Most of this chapter has focused on what you need to know in order to enter a command directly into SQL*Plus and have it executed. Another option available to you is to have SQL*Plus execute a script. A script is simply a text file that contains one or more statements to execute. When SQL*Plus executes a script, the commands or statements in the file are executed just as if you had typed them in directly from the keyboard. A script file can contain any combination of valid SQL*Plus commands, SQL statements, or PL/SQL blocks.

The START command is used to execute a script. Here is the syntax to use:

START filename [arg1
            
                
            
            arg2
            
                
            
            arg3...]

where:

STA[RT]: May be abbreviated to STA.
filename: Is the name of the script file you want to execute. The default extension is .SQL.
arg1 arg2 arg3: Represent any command-line arguments you want to pass to the script file. These are delimited by spaces, and you may specify as many as are needed by the script. Arguments containing spaces should be enclosed in either single or double quotes.

Let's say you had a file named DESCRIBE_ALL.SQL, and it contained the following SQL*Plus commands:

DESCRIBE employee
DESCRIBE project
DESCRIBE project_hours

You could execute this file with the START command as shown here:

SQL> START C:\JONATHAN\SQL_PLUS_BOOK\XB_CH_2\DESCRIBE_ALL
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPLOYEE_ID                     NOT NULL NUMBER
 EMPLOYEE_NAME                            VARCHAR2(40)
 EMPLOYEE_HIRE_DATE                       DATE
 EMPLOYEE_TERMINATION_DATE                DATE
 EMPLOYEE_BILLING_RATE                    NUMBER

 Name                            Null?    Type
 ------------------------------- -------- ----
 PROJECT_ID                      NOT NULL NUMBER
 PROJECT_NAME                             VARCHAR2(40)
 PROJECT_BUDGET                           NUMBER

 Name                            Null?    Type
 ------------------------------- -------- ----
 PROJECT_ID                      NOT NULL NUMBER
 EMPLOYEE_ID                     NOT NULL NUMBER
 TIME_LOG_DATE                   NOT NULL DATE
 HOURS_LOGGED                             NUMBER
 DOLLARS_CHARGED                          NUMBER

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Whenever you work with files in SQL*Plus, it's important to understand the concept of a working directory. The working directory is simply the directory that is used whenever you specify a filename without also including a path. The working directory is also where SQL*Plus writes temporary files, such as the AFIEDT.BUF file created when you invoke the EDIT command.

If you work under an operating system like Unix, you are already familiar with the concept of a current working directory. You also very likely know how to move between directories, and are familiar with commands such as pwd that tell you what your current directory is. The working directory simply happens to be whatever directory you are in when you invoke SQL*Plus.

Users of the Microsoft Windows operating system tend to be a bit insulated from the concept of a current directory. SQL*Plus is typically invoked under Windows by clicking an icon, and the user often does not think of himself as being "in" any particular directory when this is done. In spite of this, some directory will be current when you run SQL*Plus under Windows. Take a look at Figure 2.8. It shows the properties for the SQL*Plus menu item under Windows 95.

Figure 2.8: The SQL*Plus shortcut properties under Windows 95

Notice the Start in setting shown in Figure 2.8. It's set to the C:\ORAWIN95\BIN directory, the same directory in which the executable sits. This is the default setting used when installing Oracle on any version of Windows. The implication is that whenever you run SQL*Plus under Windows and use the SAVE command without specifying a path for the file, the file will be created in the Oracle BIN directory. The same is true when you use the SPOOL command to send report output to a file. If you don't specify a path, the report output will go to the BIN directory. Also, when you use the EDIT command, the AFIEDT.BUF temporary file will be created in this directory.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

SQL*Plus is a very effective reporting tool. You can count on having SQL*Plus available in any Oracle environment, and it gets the job done well with a minimum of fuss. This chapter shows you how to leverage the power of SQL*Plus to produce reports, and presents a report development methodology that works well in most situations.

Designing a report is a development project, albeit a very small one. As with any development project, it helps to have a methodology, or sequence of steps, to follow that will lead you successfully through the process of designing a report with a minimum of fuss and backtracking. For example, when designing a report, it makes no sense to worry about formatting the data until you have first decided what data to include.

This chapter presents a micro-methodology for developing reports with SQL*Plus that has worked very well for me. It divides the design process for a simple report into the following steps:

Formulate the query.
Format the columns.
Add page headers and footers.
Format the page.
Print it.

Dividing the design process into discrete, logical steps reduces both the amount and the complexity of the information you must keep in your head at any given time.

For more advanced reports, such as a master-detail report, there are some additional tasks you may want to perform. These include:

Add page and line breaks
Add totals and subtotals

Try to work through report design in a methodical manner. Often there is a temptation just to start typing in commands. Resist this temptation!

Consider saving all the commands to produce a report in a script file. I can't remember ever producing an ad-hoc report that was truly generated just once. Often there are formatting issues I don't see until after the first printing. Other times a client will look at a report and immediately think of one or two things to add. By saving your work in a file, you will be prepared when the request for that same report, or one similar to it, comes again.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Designing a report is a development project, albeit a very small one. As with any development project, it helps to have a methodology, or sequence of steps, to follow that will lead you successfully through the process of designing a report with a minimum of fuss and backtracking. For example, when designing a report, it makes no sense to worry about formatting the data until you have first decided what data to include.

This chapter presents a micro-methodology for developing reports with SQL*Plus that has worked very well for me. It divides the design process for a simple report into the following steps:

Formulate the query.
Format the columns.
Add page headers and footers.
Format the page.
Print it.

Dividing the design process into discrete, logical steps reduces both the amount and the complexity of the information you must keep in your head at any given time.

For more advanced reports, such as a master-detail report, there are some additional tasks you may want to perform. These include:

Add page and line breaks
Add totals and subtotals

Try to work through report design in a methodical manner. Often there is a temptation just to start typing in commands. Resist this temptation!

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Consider saving all the commands to produce a report in a script file. I can't remember ever producing an ad-hoc report that was truly generated just once. Often there are formatting issues I don't see until after the first printing. Other times a client will look at a report and immediately think of one or two things to add. By saving your work in a file, you will be prepared when the request for that same report, or one similar to it, comes again.

The only time I ever print a report by typing the commands interactively into SQL*Plus is when the query is very simple and I don't care how the output is formatted. In such a case, if I'm in a hurry, I may simply spool off the output from a SELECT statement, print it, and live with the incorrect pagination, lack of titles, and lack of column formatting.

Saving your work in a file also protects you from simple mistakes and minor catastrophes. If you've typed 20 formatting commands into SQL*Plus and your computer locks up, you've lost your work. You'll have to restart your computer and then attempt to remember all those commands. If you are working on a long SQL query, and that query exists only in the SQL buffer, a simple thing like typing COMMIT will clobber it — something I've done many times myself. Save yourself some work by saving your work in a file.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The first part of this chapter will lead you through the five steps involved in generating a simple, columnar report. This report will be complete with page headings, page footings, and column headings. In addition, you will learn about several settings, controlled by the SET command, that are useful when printing and formatting reports.

The very first step to designing a report is to formulate the underlying SQL query. There is little point in doing anything else until you have done this. The remaining steps all involve formatting and presentation. If you haven't defined your data, there is no reason to worry about how to format it.

For this chapter, let's look at developing a report that answers the following questions:

To what projects is each employee assigned?
How many hours have been charged to each project?
What is the cost of those hours?

One way to satisfy those requirements would be to develop a report based on the following query, which summarizes the hours and dollars charged by employee and project:

SELECT E.EMPLOYEE_NAME,
       P.PROJECT_NAME,
       SUM(PH.HOURS_LOGGED) ,
       SUM(PH.DOLLARS_CHARGED)
  FROM EMPLOYEE E,
       PROJECT P,
       PROJECT_HOURS PH
 WHERE E.EMPLOYEE_ID = PH.EMPLOYEE_ID
   AND P.PROJECT_ID = PH.PROJECT_ID
 GROUP BY E.EMPLOYEE_ID, E.EMPLOYEE_NAME,
          P.PROJECT_ID, P.PROJECT_NAME;

If you execute this query using SQL*Plus, here's what the output will look like:

EMPLOYEE_NAME                            PROJECT_NAME
---------------------------------------- ------------------------------------
SUM(PH.HOURS_LOGGED) SUM(PH.DOLLARS_CHARGED)
-------------------- -----------------------
Jonathan Gennick                         Corporate Web Site
                  20                    3380

Jonathan Gennick                         Year 2000 Fixes
                  24                    4056

Jonathan Gennick                         Accounting System Implementation
                  24                    4056

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You can add page breaks and line breaks to your reports with the BREAK command. BREAK is also commonly used to suppress repeating values in report columns. Take a look at the following script, which generates a detailed listing of all time charged to each project by each employee:

—Set up pagesize parameters
SET NEWPAGE 0
SET PAGESIZE 55

—Set the linesize, which must match the number of equals signs used
—for the ruling lines in the headers and footers.
SET LINESIZE 77

—Set up page headings and footings
TTITLE CENTER 'The Fictional Company' SKIP 3 -
       LEFT 'I.S. Department' -
       RIGHT 'Project Hours and Dollars Detail' SKIP 1 -
       LEFT '========================================' -
            '====================================' -
       SKIP 2

BTITLE LEFT '========================================' -
            '====================================' -
       SKIP 1 -
       RIGHT 'Page ' FORMAT 999 SQL.PNO

—Format the columns
COLUMN employee_id HEADING 'Emp ID' FORMAT 9999
COLUMN employee_name HEADING 'Employee Name' FORMAT A16 WORD_WRAPPED
COLUMN project_id HEADING 'Proj ID' FORMAT 9999
COLUMN project_name HEADING 'Project Name' FORMAT A12 WORD_WRAPPED
COLUMN time_log_date HEADING 'Date' FORMAT A11
COLUMN hours_logged HEADING 'Hours' FORMAT 9,999
COLUMN dollars_charged HEADING 'Dollars|Charged' FORMAT $999,999.99

—Execute the query to generate the report.
SELECT E.EMPLOYEE_ID,
       E.EMPLOYEE_NAME,
       P.PROJECT_ID,
       P.PROJECT_NAME,
       TO_CHAR(PH.TIME_LOG_DATE,'dd-Mon-yyyy') time_log_date,
       PH.HOURS_LOGGED,
       PH.DOLLARS_CHARGED
  FROM EMPLOYEE E,
       PROJECT P,
       PROJECT_HOURS PH
 WHERE E.EMPLOYEE_ID = PH.EMPLOYEE_ID
   AND P.PROJECT_ID = PH.PROJECT_ID
ORDER BY E.EMPLOYEE_ID, P.PROJECT_ID, PH.TIME_LOG_DATE;

When you execute this script, here's what the output will look like:

The Fictional Company


I.S. Department                              Project Hours and Dollars Detail
=============================================================================

                                                                                 
                                                                      Dollars
Emp ID Employee Name    Proj ID Project Name     Date         Hours   Charged
------ ---------------- ------- ---------------- ----------- ------ ---------
   101 Jonathan Gennick    1001 Corporate Web    01-Jan-1998      1   $169.00
                                Site

   101 Jonathan Gennick    1001 Corporate Web    01-Mar-1998      3   $507.00
                                Site

   101 Jonathan Gennick    1001 Corporate Web    01-May-1998      5   $845.00
                                Site

   101 Jonathan Gennick    1001 Corporate Web    01-Jul-1998      7 $1,183.00
                                Site

   101 Jonathan Gennick    1001 Corporate Web    01-Sep-1998      1   $169.00
                                Site

   101 Jonathan Gennick    1001 Corporate Web    01-Nov-1998      3   $507.00
                                Site

   101 Jonathan Gennick    1002 Year 2000 Fixes  01-Feb-1998      7 $1,183.00
   101 Jonathan Gennick    1002 Year 2000 Fixes  01-Apr-1998      1   $169.00
   101 Jonathan Gennick    1002 Year 2000 Fixes  01-Jun-1998      3   $507.00
   101 Jonathan Gennick    1002 Year 2000 Fixes  01-Aug-1998      5   $845.00
   101 Jonathan Gennick    1002 Year 2000 Fixes  01-Oct-1998      7 $1,183.00
   101 Jonathan Gennick    1002 Year 2000 Fixes  01-Dec-1998      1   $169.00

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

SQL*Plus allows you to print totals and subtotals in a report. To do this, you use a combination of the BREAK command and one or more COMPUTE commands. This section continues where the previous section left off. It will show you how to modify the master/detail report created earlier so that it totals the hours and dollars by project and by employee. You will also see how to print grand totals for these columns at the end of the report.

The COMPUTE command defines summary calculations needed in a report. You can use COMPUTE in conjunction with BREAK to calculate and print column totals, averages, minimum and maximum values, and so on. These calculations are performed by SQL*Plus as the report runs.

Section 3.5.1.1: Syntax of the COMPUTE command

The syntax for the COMPUTE command looks like this:

COMP[UTE] [{AVG|COU[NT]|MAX[IMUM]|MIN[IMUM]|
          NUM[BER]|STD|SUM|VAR[IANCE]}... [LABEL label_text]
          OF column_name... 
          ON {group_column_name|ROW|REPORT}...]

where:

COMP[UTE]: May be abbreviated to COMP. Entering COMPUTE with no parameters causes SQL*Plus to list all currently defined computations.
AVG: Computes the average of all non-null values for a column. AVG only applies to columns of type NUMBER.
COU[NT]: Computes the total number of non-null values for a column. COUNT may be used with columns of any datatype, and may be abbreviated to COU.
MAX[IMUM]

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In addition to what you've read so far in this chapter, there are several other techniques and topics worth discussing. One is a method for getting the current date into your page titles. Considering how easy it is to do page numbers, it's surprisingly difficult to do dates. Report headers and footers function similarly to page headers and footers, except that they print just once, at the beginning and end of a report.

If you find yourself printing reports only because you need to look at computed values such as group totals and report totals, you will want to read the section on summary reports that follows. You can save yourself a lot of network and other overhead by summarizing your data on the server instead of returning all the detail records and making SQL*Plus do the work.

It's a very common practice to put the run date in the header of a report. It's such a common practice, in fact, that it's a wonder Oracle does not provide a convenient way to do it. Unlike the case with the page number, which SQL*Plus conveniently maintains in the SQL.PNO user variable, the date is not so easily available. How then, do you display the date in the page header? The answer is to retrieve it from the database, put it into a user variable, and display that variable in the header.

You saw how to display the contents of a user variable in the header when the Project Hours and Dollars Detail report was converted to a master/detail style. You also saw how to use the COLUMN command to tell SQL*Plus to continuously update the contents of a user variable with the value of a column in the query. Getting the system date to display in the header involves a little trick that takes advantage of this use of the COLUMN command. The trick is to execute a query that returns the current date, and use the NEW_VALUE clause of the COLUMN command to get that date into a user variable. That user variable sticks around for the duration of the session and can be used in a subsequent report.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In the previous chapter, you saw how to write a script to produce a report. This chapter delves more deeply into the subject of scripting, and shows you how to write interactive scripts. You will learn how to use substitution variables, which allow the user to dynamically supply values to a script at runtime. You will learn how to prompt the user for those values, and how to display other messages for the user to see. Finally, you will learn how to package your script for easy access when you need it.

The most compelling reason to write scripts, in my mind, is to encapsulate knowledge. Say, for example, that you have developed a query that returns index definitions for a table. You certainly don't want to have to think through the entire process of developing that query each time you need to see an index. If you have a good script available, you just run it. Likewise, if someone asks you how to see index definitions for a table, just give them a copy of the script.

A second reason for developing scripts is that they save time. Look at the script to produce the first report in Chapter 3. It contains 17 separate commands, some quite long. By placing those commands in a script, you save yourself the time and effort involved in retyping all of them each time you run the report.

Lastly, scripts can simplify tasks both for you and for others. When you know you have a good, reliable script, you can just run it, answer the questions, then sit back while it does all the work. You don't need to worry, thinking "did I enter the correct command?", "did I log on as the correct user?", or "did I get that query just right?"

Anytime you find yourself performing a task over and over, think about writing a script to do it for you. You'll save yourself time. You'll save yourself stress. You'll be able to share your knowledge more easily.

A good source of ready-to-run scripts for Unix users is Oracle Scripts by Brian Lomasky and David C. Kreines, O'Reilly & Associates, 1998.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The most compelling reason to write scripts, in my mind, is to encapsulate knowledge. Say, for example, that you have developed a query that returns index definitions for a table. You certainly don't want to have to think through the entire process of developing that query each time you need to see an index. If you have a good script available, you just run it. Likewise, if someone asks you how to see index definitions for a table, just give them a copy of the script.

A second reason for developing scripts is that they save time. Look at the script to produce the first report in Chapter 3. It contains 17 separate commands, some quite long. By placing those commands in a script, you save yourself the time and effort involved in retyping all of them each time you run the report.

Lastly, scripts can simplify tasks both for you and for others. When you know you have a good, reliable script, you can just run it, answer the questions, then sit back while it does all the work. You don't need to worry, thinking "did I enter the correct command?", "did I log on as the correct user?", or "did I get that query just right?"

Anytime you find yourself performing a task over and over, think about writing a script to do it for you. You'll save yourself time. You'll save yourself stress. You'll be able to share your knowledge more easily.

A good source of ready-to-run scripts for Unix users is Oracle Scripts by Brian Lomasky and David C. Kreines, O'Reilly & Associates, 1998.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Substitution variables allow you to write generic SQL*Plus scripts. They allow you to mark places in a script where you want to substitute values at runtime.

A substitution variable is the same thing as a user variable. In the previous chapter, you saw how to get the contents of a database column into a user variable and how to place the contents of that user variable into the page header of a report. SQL*Plus also allows you to place user variables in your script to mark places where you want to supply a value at runtime. When you use them this way, they are called substitution variables.

A substitution variable is not like a true variable used in a programming language. Instead, a substitution variable marks places in the text where SQL*Plus does the equivalent of a search and replace at runtime, replacing the reference to a substitution variable with its value.

Substitution variables are set off in the text of a script by preceding them with either one or two ampersand characters. Say, for example, that you had this query to list all projects to which employee #107 had charged time:

SELECT DISTINCT p.project_id, p.project_name
  FROM project p,
       project_hours ph
 WHERE ph.employee_id = 107
   AND p.project_id = ph.project_id;

As you can see, this query is specific to employee number 107. To run the query for a different employee, you would need to edit your script file, change the ID number, save the file, then execute it. That's a pain. You don't want to do that. Instead, you can generalize the script by rewriting the SELECT statement with a substitution variable in place of the employee ID number. It would look like this:

SELECT DISTINCT p.project_id, p.project_name
  FROM project p,
       project_hours ph
 WHERE ph.employee_id = &employee_id
   AND p.project_id = ph.project_id;

The ampersand in front of the word "employee_id" marks it as a variable. At run-time, when it reads the statement, SQL*Plus will see the substitution variable and replace it with the current value of the specified user variable. If the employee_id user variable contained a value of 104, then "&employee_id" would be replaced by "104", and the resulting line would look like this:

Additional content appearing in this section has been removed.
Purchase this book now or

Table of Contents

Section 3.5.1.1: Syntax of the COMPUTE command