Cover | Table of Contents | Colophon

Table of Contents

Chapter 1: Introduction to SQL*Plus

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!

What Is SQL*Plus?

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!

History of SQL*Plus

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!

Why Master SQL*Plus?

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!

Creating and Loading the Sample Tables

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!

Chapter 2: Interacting with SQL*Plus

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!

Starting SQL*Plus

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!

Some Basic SQL*Plus Commands

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!

Running SQL Queries

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!

Working with PL/SQL

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!

The SQL Buffer

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!

Line Editing

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:

1. You enter a SQL statement or PL/SQL block, which SQL*Plus stores in the buffer.
2. You then list the contents of the buffer to the screen.
3. You enter SQL*Plus commands that tell SQL*Plus to make changes to the statement in the buffer.
4. You list the buffer again.
5. 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!

Executing the Statement in the Buffer

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:

1. Type a forward slash on a line by itself, then press ENTER.
2. 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!

Saving and Retrieving the Buffer

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!

The EDIT Command

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!

Executing a Script

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!

The Working Directory

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.

Purchase this book now or read it online at Safari to get the whole thing!

Chapter 3: Generating Reports with SQL*Plus

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:

1. Formulate the query.
2. Format the columns.
3. Add page headers and footers.
4. Format the page.
5. 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

Return to Oracle SQL*Plus: The Definitive Guide

© 2008, O'Reilly Media, Inc. (707) 827-7000 / (800) 998-9938 All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.

About O'Reilly Privacy Policy Contacts Authors Press Room Jobs User Groups Academic Solutions Newsletters Writing for O'Reilly RSS Feeds

Other O'Reilly Sites O'Reilly Radar Ignite Tools of Change for Publishing Digital Media Inside iPhone O'Reilly FYI makezine.com craftzine.com hackszine.com perl.com xml.com

Sponsored Sites Inside Aperture Inside Lightroom Inside Port 25 InsideRIA