2.2. Tools for Running SQL

Most Oracle database environments consist of two, three, or more tiers. In the simplest two-tier scenario, a database developer might be using SQL*Plus on a Windows PC connecting to an Oracle database on a Linux server. More complex environments may include a web server, application server, or authentication server on a number of other servers in between the client and the database server.

tiers

Locations where different components of an enterprise application system reside. In a typical three-tier environment, the client tier runs a thin application such as a web browser, which connects to a middleware server that is running a web server. The web server and its related components typically manage the business rules of the application. The third-tier database platform controls access to the data and manages the data itself. This approach partitions the application so that it is easier to maintain and segregates the tasks into tiers that are best equipped to handle a particular function.

Here, we will explore the various client-based tools that can be used to run SQL, including SQL*Plus, iSQL*Plus, SQL*Plus Worksheet, third-party tools, Open Database Connectivity (ODBC), Java Database Connectivity (JDBC), and Oracle Call Interface (OCI).

2.2.1. 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.

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. As you'll learn a little later in this chapter, 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:	empdeptrept.txt
SQL> /

Tue Aug 13                                   page    1
                 Department Report

Department Department     City
    Number Name           Location
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> spool off
SQL>

Notice that we didn't type in the entire SELECT statement again. Instead, we used the / SQL*Plus command, which reruns the last complete SQL statement executed.

SQL*Plus commands differ from SQL statements in that they don't need a semicolon at the end (although SQL*Plus commands can be terminated with a semicolon without SQL*Plus complaining about it). SQL statements can be written across many lines without any type of continuation character; they are complete whenever you type a semicolon or use the SQL*Plus / command. SQL*Plus commands must be contained entirely on one line, unless the - continuation character is used at the end of each line. The example below shows how the SQL*Plus continuation character is used:

SQL> column deptno heading -
> "Department|Number"
SQL>

2.2.2. iSQL*Plus

With iSQL*Plus, you connect to the database indirectly via a very "lightweight" middle tier. The iSQL*Plus tool is essentially the web-enabled version of SQL*Plus, with a few restrictions, which we will cover shortly. It is implemented as part of a three-tier Oracle environment, although iSQL*Plus could very well run on the same machine as either the client or the Oracle server itself.

iSQL*Plus offers a 100 percent web-enabled, thin client solution. From a DBA's or network administrator's point of view, the more clients that need only a web browser to get their work done, the better. No Oracle client software installation is required for iSQL*Plus!

thin client

A workstation or CPU with relatively low-powered components that can use a web interface (or other application with a small footprint) to connect to a middle-ware or a back-end database server where most of the processing occurs. iSQL*Plus is an example of a web application that runs on a thin client.

To start iSQL*Plus, use your favorite web browser (preferably any version of Mozilla, Microsoft Internet Explorer 5.0 or later, or Netscape Navigator 4.7 or later) and navigate to the URL http://<your_server_name>/isqlplus. The string <your_server_name> is the name of the middleware server that is running the iSQL*Plus web application.

SQL*Plus and iSQL*Plus are similar. In fact, iSQL*Plus requires that the SQL*Plus executable be accessible on the middleware server that is running the iSQL*Plus service. The iSQL*Plus login screen below shows the user SCOTT logging into the same server as he did with SQL*Plus earlier in this chapter. In this case, rac0 is specified as the connection identifier, rather than the host string as it is with SQL*Plus; they have different names but mean the same thing.

connection identifier

See host string.

Here is an example of running the same query in iSQL*Plus that you saw earlier under SQL*Plus.

Notice that with iSQL*Plus, if only one SQL statement is being run at a time, no semicolon is required. This would be the equivalent of typing / in a SQL*Plus session after entering a SQL statement without a terminating semicolon. Also notice that the area where commands are entered is a fixed size, regardless of how many commands you are entering. Rest assured, as in SQL*Plus, this is easily configurable. Just click the Preferences link in the upper-right corner of the browser to change the command area size and other iSQL*Plus environment settings.

All of the examples later in this chapter and throughout the book will use iSQL*Plus as the tool for executing SQL commands and reports.

2.2.3. SQL*Plus Worksheet

If Oracle Enterprise Manager (OEM) is installed, another variation of SQL*Plus, called SQL*Plus Worksheet, is available to the DBA. Here's the OEM Login dialog box:

Oracle Enterprise Manager (OEM)

A GUI tool that allows access, maintenance, and monitoring of multiple databases or services within a single application.

SQL*Plus Worksheet supports all the commands that standard SQL*Plus supports, in a two-pane query/result format, as shown below. It's a slightly more graphical application; in other words, it needs an operating system such as Microsoft Windows or a similar GUI client to run. Beyond that, it's really just SQL*Plus with a slightly better front end!

2.2.4. Third-Party Tools

Basic network client connectivity is provided during an Oracle client installation. Starting with release 9, Oracle's network connectivity package is known as Oracle Net Services. Third-party developers can leverage this functionality in their own applications to provide tools customized for a more specific audience and to provide an additional layer of functionality that may not be available in Oracle's offerings.

An example of a third-party tool is TOAD, which stands for Tool for Oracle Application Developers. TOAD is not just for developers; it has a lot of functionality that DBAs can use also. There are both a freeware version (that can even be used as freeware in a corporate environment) and a licensed version. The licensed version has many more DBA-friendly features and SQL debugging tools available. (Visit www.toadsoft.com or www.quest.com/toad for more information.) Shown below is the DEPT table query executed using the freeware version of the TOAD browser. Notice the other database navigational capabilities in this pane.

2.2.5. ODBC/JDBC

Many tools in the Windows (and Unix) environment can take advantage of a common framework known as ODBC, which stands for Open Database Connectivity. In a nutshell, ODBC allows applications that are ODBC-compliant to connect to virtually any database without knowing the details of how to connect directly to the database. All of the details are hidden in the ODBC driver itself. The driver may be written by the database vendor or by a third-party developer that specializes in ODBC connectivity. Here is an example of the Oracle ODBC Driver Configuration dialog box for setting up an ODBC connection to a database.

ODBC (Open Database Connectivity)

A set of standards that allow applications that are not dependent on any one specific database to process SQL statements against any database that supports SQL.

After the ODBC connection is made, you can run queries. Shown below are the results of the DEPT table query from a Microsoft Access session.

ODBC driver

An interface, usually at the operating-system level, that supports the connection of an ODBC-compliant application to a specific database platform.

Applications that use ODBC are not limited to tools such as Microsoft Access, which also has its own client-based database engine in addition to the capability to connect to other databases. Spreadsheets, financial applications, and statistical analysis packages are among the many types of applications that need to connect to a database for their source data. ODBC gives the end user the freedom to choose which external database to use and frees the application vendor from needing to develop a special connection routine for every possible database source.

JDBC, which stands for Java Database Connectivity, is very similar to ODBC in that JDBC provides a common set of routines to allow a Java developer to connect to any SQL-compliant database without knowing the specifics of the target database. The key difference between ODBC and JDBC is that JDBC is specifically for Java applications and ODBC is application-neutral.

JDBC (Java Database Connectivity)

A set of library routines specific to the Java language that allows a Java application to easily connect to and process SQL statements against an Oracle database.

2.2.6. OCI

Finally, we have OCI, which stands for Oracle Call Interface. OCI is a set of library routines for C developers (on any operating system platform) that can provide all the functionality available from a SQL command-line session and more. Below are some code fragments in the C language that include OCI calls:

text *username = (text *) "SCOTT";
text *password = (text *) "TIGER";
...
text *insert = (text *) "INSERT INTO emp(empno, 
    ename, job, sal, deptno)
    VALUES (:empno, :ename, :job, :sal, :deptno)";
...
/*
 *  Connect to ORACLE and open two cursors.
 *  Exit on any error.
 */
    if (olog(&lda, (ub1 *)hda, username, −1, password, −1,
             (text *) 0, −1, (ub4)OCI_LM_DEF))
    {
        err_report(&lda);
        exit(EXIT_FAILURE);
    }
    printf("Connected to ORACLE as %s
", username);
...
/* Parse the INSERT statement. */
    if (oparse(&cda1, insert, (sb4) −1, FALSE, (ub4) VERSION_
7))
    {
        err_report(&cda1);
        do_exit(EXIT_FAILURE);
    }
...

For more OCI code samples, check the ORACLE_BASEORACLE_HOMEoci directory under Microsoft Windows Oracle installations.

OCI (Oracle Call Interface)

A set of library routines that allows a C application on virtually any development platform to easily connect to and process SQL statements against an Oracle database. The OCI routines are called as native C library functions; therefore, no preprocessor is necessary when compiling a C application using OCI.