SQL*Plus

SQL*Plus has been around as long as the Oracle RDBMS itself. It is the most basic tool available for connecting to the database and executing queries against the tables in a database. On Unix systems, it can be run in character-based mode, even on a dumb terminal connected to the Unix system via a serial port.

The “Plus” part of SQL*Plus defines some of the extra functionality available above and beyond executing SQL statements and returning the results. Some of this functionality is proprietary to SQL*Plus and may not be available in non-Oracle database environments. Here are some of the things you can do using SQL*Plus:

• Define headers and footers for reports.
• Rename columns in the report output.
• Prompt users for values to be substituted into the query.
• Retrieve the structure of a table.
• Save the results of the query to a file.
• Copy entire tables between databases using only one command.

While many other tools surpass SQL*Plus in functionality as well as in look and feel, those other tools don’t help much when the database is down and all you have is a character-based terminal emulator connection to your Unix server! No matter which environment you’re in—Unix, Windows, minicomputer, or mainframe—SQL*Plus will always be there and have the same look and feel across all of those environments.

Under the various versions of Microsoft Windows, SQL*Plus runs as a Windows application and as a command-line application. The Windows functionality available in the Windows SQL*Plus session includes those features normally available in a Windows text-based editor: cutting and pasting text strings, searching for text in the session window, and saving or loading the last command executed. The Windows version also allows you to change the SQL*Plus environment settings using a GUI dialog box or through the command line. The GUI dialog box is accessible from SQL*Plus by selecting Options Environment.

You’ll need to log on with a valid username and password to initiate a SQL*Plus session, as shown below. You’ll also need to enter a host string value. The Host string is an alias to a set of parameters, such as the name, address, protocol type, and port number of the Oracle database to which you want to connect. The database may be on the same machine that is running the SQL*Plus client tool, or it may be on a different host machine on the network. For the purposes of this book, all database connections will use the rac0 host string.

Host String:- A text string that represents a shortcut or reference to a set of parameters that provide the information needed to connect to a database host from the client application.

Note:- Your default Oracle installation may not have the user SCOTT enabled, or the password may have been changed from the default TIGER. Check with your local DBA to see if this is the case.

The user SCOTT owns a number of database tables, including the DEPT table, which contains a list of all the department numbers, department names, and department locations. The SQL SELECT statement allows you to extract information from a database. The example below shows a SELECT statement that retrieves all of the rows in the DEPT table (select * from dept;) and its results.

Notice that the case of the keywords and column names is important only for readability. In practice, you can enter them in any case. To enhance this sample query, let’s do the following:

• Add a report title of “Department Report” using the TTITLE SQL*Plus command.
• Change the headers for each of the columns to make them more readable using the COLUMN SQL*Plus command.
• Save the output from the query to a file using the SQL*Plus SPOOL command.

The sequence of SQL*Plus commands, the SQL statement, and the results from the command are as follows:

SQL> ttitle “Department Report”

SQL> column deptno heading “Department|Number”

SQL> column dname heading “Department|Name”

SQL> column loc heading “City|Location”

SQL> spool c:\temp\deptrept.txt

SQL> /