O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Structure of This Book

This book is divided into fourteen chapters and two appendixes. Many chapters are task-oriented and show you how you can use SQL*Plus to perform common tasks such as printing a report, or extracting data to a file. The appendixes contain reference material, such as a list of all SQL*Plus commands.

Chapter 1, goes into more detail about what SQL*Plus really is and why you should master it. It gives you a taste of the many different tasks SQL*Plus can be used for. A short history of SQL*Plus is included, as well as a description of the database used for many of the examples in this book, particularly the reporting examples.

Chapter 2, shows you how to start command-line SQL*Plus, and how to enter, execute, and edit commands. This is basic information you need to know.

Chapter 3, is all new, and shows you the basics of interacting with iSQL*Plus, a three-tier application you access through any standard web browser. Now you can access SQL*Plus from anywhere, without having to first install Oracle software on your client.

Chapter 4, is another new chapter, and one that you'll want to read if SQL is new to you. It provides a swift and fast-paced introduction to the basic SQL statements used in retrieving, modifying, and deleting data in a database. Once you've been introduced to the core statements, the chapter goes on to cover key points about transactions, walks you through the treacherous territory of the null value (which isn't a value at all), and explains common operations such as table joins and union queries.

Chapter 5, introduces the reporting features of SQL*Plus, and presents a step-by-step method for creating a report that has worked well for me.

Chapter 6, is an exciting new chapter, or at least it was for me to write. This chapter shows some of the incredible things you can do using SET MARKUP to invoke SQL*Plus's ability to generate HTML output. As I researched and wrote this chapter, I was pleasantly surprised at the great-looking web reports that I could produce using SQL*Plus. It was great fun watching my old friend SQL*Plus generate such good-looking HTML.

Chapter 7, picks up where Chapter 5 leaves off, and shows you how to use the more advanced reporting features of SQL*Plus to generate totals and subtotals, generate grand totals, place the date and other values into page headers, format object columns, and produce summary reports that show only totals, subtotals, and the like, but no detail. You'll see an interesting way in which you can use SQL's UNION operator to combine the results from several queries into one report.

Chapter 8, explains the basic scripting capabilities of SQL*Plus. Primarily, this chapter shows how SQL*Plus substitution variables work, and how you can use them to your advantage. In addition, it covers the subject of getting input from a user, and shows you how to control the output the user sees as the script is executed.

Chapter 9, shows how you can use SQL*Plus to extract data from an Oracle database and place it into a text file suitable for loading into another program such as a spreadsheet. This text file may be delimited—by commas, for example—or may consist of fixed-width columns. You'll also learn how to use SQL*Loader to reload the data. The chapter ends with an example showing the use of the relatively new external table feature introduced in Oracle9i Database.

Chapter 10, shows how you can query Oracle's data dictionary tables to see the structure of commonly used database objects, such as tables and indexes.

Chapter 11, builds on Chapter 8, but covers some advanced, and sometimes unusual, scripting techniques. This chapter introduces bind variables, and explains how they differ from user variables. This chapter also shows some creative techniques you can use to add some limited branching, and even some looping, to your SQL*Plus scripts.

Chapter 12, presents the SQL*Plus features that support the tuning of SQL statements. Also covered in this chapter is Oracle's EXPLAIN PLAN statement, which gives you a look at the execution strategy that the Oracle optimizer will use for any given SQL statement.

Chapter 13, introduces a security feature that a database administrator can use to limit what a user can do with SQL*Plus. The product user profile allows you to turn off specific SQL statements and SQL*Plus commands for one user or a group of users. It can be used to limit a user's access to certain roles while connected via SQL*Plus.

Chapter 14, shows a number of ways in which you may customize your SQL*Plus environment. The site and user profiles are explained, as well as several environment settings that affect SQL*Plus's behavior.

Appendix A, contains syntax diagrams for all SQL*Plus commands.

Appendix B, describes the various format elements that may be used to build up format strings to be used with commands, such as COLUMN and ACCEPT, that format output or accept user input.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required