What Is SQL*Plus?

SQL*Plus is essentially an interactive query tool with some scripting capabilities. You can enter a SQL statement, such as a SELECT query, and view the results. You can execute data definition language (DDL) statements to create tables and other objects. DBAs can use SQL*Plus to start up, shut down, and otherwise administer a database. You can even enter and execute PL/SQL code.

SQL*Plus is primarily a command-line application, but, despite its lack of "flash," it is a workhorse tool used daily by database administrators, developers, and yes, even end users. As a DBA, 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.

1.1.1 Uses for SQL*Plus

Originally developed simply as a way to enter queries and see results, SQL*Plus has been enhanced with scripting and formatting capabilities and can be used for many different purposes. The basic functionality is 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 statements, such as those used to create, alter, or drop database objects (e.g., tables, indexes, and users), as well as any other types of SQL statements that Oracle supports.
• Execute SQL*Plus script files.
• Write output to a file.
• Execute procedures and functions that are stored in a database.

While these operations may not seem significant, they are the building blocks you can use to perform various useful functions.

Consider the ability to enter a SELECT statement and view the results. Example 1-1 shows how to do this using SQL*Plus.

Example 1-1. Executing a query in SQL*Plus

SQL>

SELECT employee_id, employee_name, employee_billing_rate

2

FROM employee;

EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_BILLING_RATE

----------- ---------------------------------------- ---------------------

 101 Marusia Churai 169

 102 Mykhailo Hrushevsky 135

 104 Pavlo Virsky 99

 105 Mykola Leontovych 121

 107 Lesia Ukrainka 45

 108 Pavlo Chubynsky 220

 110 Ivan Mazepa 84

 111 Taras Shevchenko 100

 112 Igor Sikorsky 70

 113 Mykhailo Verbytsky 300

Combine this capability with SQL*Plus's formatting abilities and you can turn these results into a credible-looking report, such as that shown in Example 1-2, complete with page titles, page numbers , column titles, and nicely formatted output.

Example 1-2. A SQL*Plus formatted report

Employee Listing Page 1



 Billing

 Emp ID Name Rate

---------- ------------------- --------

 101 Marusia Churai 9.00

 102 Mykhailo Hrushevsky 5.00

 104 Pavlo Virsky .00

 105 Mykola Leontovych 1.00

 107 Lesia Ukrainka .00

 108 Pavlo Chubynsky 0.00

 110 Ivan Mazepa .00

 111 Taras Shevchenko 0.00

 112 Igor Sikorsky .00

 113 Mykhailo Verbytsky 0.00

Another twist on the same theme is to format the output as a list of comma-separated values, such as that shown in Example 1-3.

Example 1-3. Comma-separated values from SQL*Plus

101,"Marusia Churai",169

102,"Mykhailo Hrushevsky",135

104,"Pavlo Virsky",99

105,"Mykola Leontovych",121

107,"Lesia Ukrainka",45

108,"Pavlo Chubynsky",220

110,"Ivan Mazepa",84

111,"Taras Shevchenko",100

112,"Igor Sikorsky",70

113,"Mykhailo Verbytsky",300

Using the SQL*Plus SPOOL command, which you'll read more about in Chapter 5, you can write this output to a .csv file easily readable by most, if not all, spreadsheet programs. In fact, if you are running Microsoft Windows with Microsoft Office installed, simply double-clicking on a .csv file will open that file in Microsoft Excel, where you can further manipulate the data.

Beginning with SQL*Plus in Oracle8 i Database, you can use the SET MARKUP HTML command to generate HMTL output, such as that shown in Example 1-4.

Example 1-4. A SQL*Plus report formatted in HTML