Oracle SQL*Plus: The Definitive Guide

Why Master SQL*Plus?

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.

If you are a developer, you can use SQL*Plus to build up queries, to quickly develop ad-hoc reports, and to explore the data in your database. You can also use SQL*Plus to create and debug stored procedures, stored functions, packages, and object types. If you have queries that aren’t performing well, you may be able to find out why by using Oracle’s EXPLAIN PLAN command from SQL*Plus. EXPLAIN PLAN will tell you the execution strategy chosen by the optimizer for the query. Chapter 8, talks more about this.

Many modern GUI development tools, such as PowerBuilder, for example, provide GUI-based query generators. These typically let you drag and drop tables into your query and then draw lines between fields joining those tables together. This drag-and-drop functionality may be great for a simple query that just joins a few tables, but I find that it quickly becomes cumbersome as the query grows in complexity. It’s not unusual, when developing reports, to have queries that are a page or more long. Sometimes these queries consist of several SELECT statements unioned together, each query having one or more subqueries. When developing one of those mega-queries, I’ll take SQL*Plus and a good editor over a GUI query tool any day of the week. Why? Because with an editor I can keep bits and pieces of the query lying around. Using Windows copy and paste, I can pull out a subquery and execute it independently without losing track of the larger query I am trying to build. I can easily comment out part of a WHERE clause when debugging a query and then uncomment it later.

If you are developing stored procedures, functions, packages, or Oracle object types using PL/SQL, then SQL*Plus may be the only tool you have for creating those in the database. Other tools, such as Oracle’s Procedure Builder and Platinum Technology’s SQL Station, are on the market, but not everyone will have a license to use those. In addition, third-party tools in particular may be slightly behind the curve when it comes to keeping up with Oracle releases. I suspect it took awhile for the third-party vendors to catch up when Oracle8, with its new object types, was released.

Almost anything that you want to do with an Oracle database can be done using SQL*Plus. You can write scripts to automate routine maintenance tasks, report on the state of your database, or generate ad-hoc reports for end users. You can execute queries to explore your database, and you can use SQL*Plus to create and manage any schema or database object. Because of its universal availability, you will be able to perform these functions anywhere you go. If you manage an Oracle database or develop software to run against an Oracle database, you will greatly improve your productivity by mastering this tool.