This Chapter Introduces You to SQL*Plus, Covering the Following Topics

1SQL*Plus Overview

This chapter introduces you to SQL*Plus, covering the following topics:

• What is SQL*Plus
• SQL*Plus Installation
• Who Can Use SQL*Plus
• How Can I Learn SQL*Plus
• How to Use the SQL*Plus Guide
• Oracle9i Sample Tables and SQL*Plus

What is SQL*Plus

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Server or Client installation. It has a command-line user interface, and more recently, a web-based user interface called iSQL*Plus.

On Windows platforms, context menu options accessed through the right mouse button enable local files to be sent to the iSQL*Plus user interface.

SQL*Plus has its own commands and environment, and it provides access to the Oracle RDBMS. It allows you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following:

• enter SQL*Plus commands to configure the SQL*Plus environment
• enter, edit, store, retrieve, and run SQL commands and PL/SQL blocks
• format, perform calculations on, store, and print from query results
• interact with an end user
• startup and shutdown a database
• connect to a database
• define variables
• capture errors
• list column definitions for any table
• perform database administration

You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility of SQL*Plus in combination with server side CGI scripts, or using the dynamic reporting capability of iSQL*Plus to run a script from a web page.

For example, to describe a database object using iSQL*Plus, enter

DESCRIBE EMP_DETAILS_VIEW

which produces the following output:

Text description of the illustration describe.gif

To select the associated data from the database, enter

COLUMN FIRST_NAME HEADING "First Name"

COLUMN LAST_NAME HEADING "Family Name"

SELECT FIRST_NAME, LAST_NAME

FROM EMP_DETAILS_VIEW

WHERE LAST_NAME LIKE 'K%'

which produces the following output with renamed column headings.

Text description of the illustration selectou.gif

SQL*Plus Command-line Architecture

SQL*Plus uses a two-tier model comprising:

• Client Tier (Command-line user interface).
• Database Tier (Oracle9i).

The two tiers may or may not be on the same machine.

Command-line User Interface

The command-line user interface is the character based terminal implementation.

Oracle9i

Oracle Net components provide communication between the SQL*Plus Client and Oracle9i.

iSQL*Plus Architecture

iSQL*Plus is a fast, browser-based interface which uses the SQL*Plus processing engine in a three-tier model comprising:

• Client Tier (iSQL*Plus user interface, typically a web browser).
• Middle Tier (iSQL*Plus Server, Oracle Net, and Oracle HTTP Server).
• Database Tier (Oracle9i).

Text description of the illustration arch.gif

The iSQL*Plus Server must be on the same machine as the Oracle HTTP Server. The client tier may or may not also be on this machine. The middle tier coordinates interactions and resources between the client tier and the database tier. The database tier is Oracle9i, which is accessed via Oracle Net.

Web Browser

The iSQL*Plus user interface runs in a web browser connected to the Internet or your intranet. There is no installation or configuration required for the iSQL*Plus user interface. You only need to know the URL of the Oracle HTTP Server to access Oracle9i.

iSQL*Plus Server

The iSQL*Plus Server is installed with the Oracle HTTP Server when Oracle9i is installed.

The middle tier contains the Oracle HTTP Server and the iSQL*Plus Server. The iSQL*Plus Server enables communication and authentication between the iSQL*Plus user interface and Oracle9i. Each iSQL*Plus session is uniquely identified, so you can have multiple concurrent sessions open to Oracle9i.

Oracle9i

Oracle Net components provide communication between the iSQL*Plus Server and Oracle9i in the same way as for a client server installation of Oracle9i.

SQL*Plus Installation

SQL*Plus is a component of the Oracle Database distribution. SQL*Plus, and its web-based user interface called iSQL*Plus, are installed by default when you install the Oracle Database.

A few aspects of Oracle and SQL*Plus differ from one host computer and operating system to another. These topics are discussed in the Oracle installation and user's guide, published in a separate version for each operating system that SQL*Plus supports.

Keep a copy of your Oracle installation and user's guide available for reference.

Who Can Use SQL*Plus

The SQL*Plus, SQL, and PL/SQL command languages are powerful enough to serve the needs of users with some database experience, yet straightforward enough for new users who are just learning to work with Oracle.

The design of the SQL*Plus command language makes it easy to use. For example, to give a column labelled LAST_NAME in the database the clearer heading "Family Name", you might enter the following command:

COLUMN LAST_NAME HEADING 'Family Name'

Similarly, to list the column definitions for a table called EMPLOYEES, you might enter this command:

DESCRIBE EMPLOYEES

How Can I Learn SQL*Plus

There are several sources available to assist you to learn SQL*Plus:

• Online Help for SQL*Plus

Command-line and iSQL*Plus online help

• Using SQL*Plus Section of this Guide

Part II of this Guide, Chapters 5 through 12.

• SQLA Course for SQL*Plus

A course run by Oracle with the course code of SQLA. A three day hands on course.

How to Use the SQL*Plus Guide

This guide gives you information about SQL*Plus that applies to all operating systems. Some aspects of SQL*Plus, however, differ on each operating system. Such operating system specific details are covered in the Oracle installation and user's guide provided for your system. Use these operating system specific guides in conjunction with this SQL*Plus User's Guide and Reference.

Throughout this guide, examples showing how to enter commands use a common command syntax and a common set of sample tables. The tables are described in "Oracle9i Sample Tables and SQL*Plus".

You will find the "Conventions in Code Examples" particularly useful when referring to commands in this guide.

Oracle9i Sample Tables and SQL*Plus

Included with Oracle9i, are a number of sample schemas. The tutorial and examples in this guide use the EMP_DETAILS_VIEW view of the Human Resources (HR) sample schema. In using the HR sample schema you will come to understand the concepts and operations of this guide. This schema contains personnel records for a fictitious company. As you complete the exercises in this guide, imagine that you are the personnel director for this company.

Dates in the sample tables use four digit years. As the default date format in SQL*Plus is DD-MM-YY, dates displayed show only a two digit year. Use the SQL TO_CHAR function in your SELECT statements to control the way dates are displayed.

For further information about the sample schemas included with Oracle9i, see the Oracle9i Sample Schemas guide. Figure1-1 shows a description of the view, EMP_DETAILS_VIEW.

Figure 1-1 EMP_DETAILS_VIEW

Name Null? Type

------

EMPLOYEE_ID NOT NULL NUMBER(6)

JOB_ID NOT NULL VARCHAR2(10)

MANAGER_ID NUMBER(6)

DEPARTMENT_ID NUMBER(4)

LOCATION_ID NUMBER(4)

COUNTRY_ID CHAR(2)

FIRST_NAME VARCHAR2(20)

LAST_NAME NOT NULL VARCHAR2(25)

SALARY NUMBER(8,2)

COMMISSION_PCT NUMBER(2,2)

DEPARTMENT_NAME NOT NULL VARCHAR2(30)

JOB_TITLE NOT NULL VARCHAR2(35)

CITY NOT NULL VARCHAR2(30)

STATE_PROVINCE VARCHAR2(25)

COUNTRY_NAME VARCHAR2(40)

REGION_NAME VARCHAR2(25)

Unlocking the Sample Tables

The Human Resources (HR) Sample Schema is installed as part of the default Oracle9i installation. The HR user is locked by default.

You need to unlock the HR tables and user before you can use the HR sample schema. To unlock the HR tables and user, log in as the SYSTEM user and enter the following command:

ALTER USER HR IDENTIFIED BY your_password ACCOUNT UNLOCK;

For further information about unlocking the HR tables and login, see the Oracle9i Sample Schemas guide. The HR user is primarily to enable you to access the HR sample schema and is necessary to enable you to run the examples in this guide.

Each table in the database is "owned" by a particular user. You may wish to have your own copies of the sample tables to use as you try the examples in this guide. To get your own copies of the HR tables, see your DBA or see the Oracle9i Sample Schemas guide, or you can create the HR tables with the script HR_MAIN.SQL which is located in the following subdirectory on UNIX:

$ORACLE_HOME/DEMO/SCHEMA/HUMAN_RESOURCES/HR_MAIN.SQL

And on the following subdirectory on Windows:

%ORACLE_HOME%\DEMO\SCHEMA\HUMAN_RESOURCES\HR_MAIN.SQL

To create the HR tables from command-line SQL*Plus, do the following:

1. Ask your DBA for your Oracle9i account username and password.

1. Login to SQL*Plus.

1. On UNIX, enter the following command at the SQL*Plus prompt:
2. SQL> @?/DEMO/SCHEMA/HUMAN_RESOURCES/HR_MAIN.SQL

On Windows, enter the following command at the SQL*Plus prompt:

SQL> @?\DEMO\SCHEMA\HUMAN_RESOURCES\HR_MAIN.SQL

To remove the sample tables, perform the same steps but substitute HR_DROP.SQL for HR_MAIN.SQL.

For more information about the sample schemas, see the Oracle9i Sample Schemas guide.

2SQL*Plus User Interface

This chapter describes the SQL*Plus command-line and iSQL*Plus user interfaces, and the iSQL*Plus Extension for Windows. This chapter contains the following topics:

• SQL*Plus Command-line User Interface
• iSQL*Plus User Interface
• iSQL*Plus Extension for Windows

SQL*Plus Command-line User Interface

You can use the command-line interface to SQL*Plus to write SQL*Plus, SQL and PL/SQL commands to:

• Enter, edit, run store, retrieve, and save SQL commands and PL/SQL blocks.
• Format, Calculate, store, and print query results.
• List column definitions for any table.
• Send messages to and accept responses from an end user.
• Perform database administration.

The Command-line Screen

The SQL*Plus command-line interface is standard on all operating systems. The following shows SQL*Plus running in an X terminal.

Text description of the illustration cmdline.gif

When SQL*Plus starts, it displays the date and time, the SQL*Plus version and copyright information before the SQL*Plus prompt appears. The default prompt for command-line SQL*Plus is:

SQL>

iSQL*Plus User Interface

iSQL*Plus is a browser-based interface to Oracle9i. It is a component of the SQL*Plus product.

iSQL*Plus enables you to use a web browser to connect to Oracle9i and perform the same tasks as you would through the command-line version of SQL*Plus.

Different web browsers, and the size of the web browser window, may affect the appearance and layout of iSQL*Plus screens.

iSQL*Plus Navigation

There are a number of ways to navigate in iSQL*Plus:

• Using the global navigation icons
• Using the breadcrumb links

Icons

Global navigation icons are displayed on each screen. Icons have three states:

• A white background when that functionality is available
• A light brown background when that functionality is unavailable
• A blue background when active (when you have navigated to that screen)

The following icons are displayed in iSQL*Plus screens (including online help):

Logout

Logs you out of the iSQL*Plus session and returns you to the Login screen.

New Session

Starts a new iSQL*Plus session in a separate web browser window. You can log in to the new session as the same user or as any other valid user.

History

Opens the iSQL*Plus History screen. You can select one or more previously executed scripts to reload into the Input area or to delete from the History list.

Preferences

Opens the iSQL*Plus Preferences screen where you can set interface options, system variables or change your password.

Help

Opens the iSQL*Plus Help in a separate web browser window.

Next

Takes you to the next page in iSQL*Plus Help.

Previous

Takes you to the previous page in iSQL*Plus Help.

Contents

Takes you to the contents in iSQL*Plus Help.

Index

Takes you to the index in iSQL*Plus Help.

Breadcrumbs

Breadcrumbs are navigation links showing the navigation path you have taken. They appear as text links displayed under the iSQL*Plus logo. Click on a breadcrumb link to return to any of the screens listed. For example, navigating to the Preferences screen using the Preferences icon displays a Work screen breadcrumb.

Work Screen > Preferences

Click the Work Screen link to return to the Work screen.

iSQL*Plus Login Screen

You connect to the Login screen from your web browser with a URL like:

machine_name.domain:port/isqlplus

The Login screen is displayed:

Text description of the illustration loginscn.gif

Username:

Enter a valid username to connect to Oracle9i.

Password:

Enter a valid password for the username.

Connection Identifier:

Leave this field blank to use the default Oracle database if one exists, otherwise enter an Oracle Net alias to specify a remote database you want to connect to. If you use an Oracle Net alias, it must be defined on the machine running the iSQL*Plus Server, which may not be the same machine from which you run your web browser.

You can also use the full connection identifier, for example:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=

(PORT=1521)))(CONNECT_DATA=((SERVICE_NAME=orashop.us.acme.com))

iSQL*Plus can be configured to restrict connections to specific databases. If restricted database access has been enabled, a dropdown list of available databases is displayed in place of the Connection Identifier text field. This allows greater security for iSQL*Plus Servers in hosted environments.

Login

Click the Login button to log in to iSQL*Plus with the supplied username, password and connection identifier.

iSQL*Plus Work Screen

After successfully logging in, the Work screen is displayed.

Text description of the illustration work.gif

File or URL:

Enter the path and filename or the URL of a file you want to load into the Input area for editing or execution.

Browse...

Click the Browse... button to search for a script file that you want to load for editing or execution. When you select the file, its path and name are entered in the File or URL: field.

Load Script

Click the Load Script button to load the script specified in the File or URL: field into the iSQL*Plus Input area for editing or execution.

Enter statements:

Enter SQL statements, PL/SQL blocks, or iSQL*Plus commands. This area is also referred to as the Input area. You can resize the Input area in the Interface Options screen which you access from the Preferences screen.

Execute

Click the Execute button to execute the contents of the Input area. Depending on your preference settings, the results are displayed in the Work screen, in a new web browser window, or saved to a file.

Save Script

Click the Save Script button to save the contents of the Input area to a file. You are prompted to enter the name of the file. The file extension you choose is for your convenience. It may be useful to identify scripts with an extension of .SQL.

Clear Screen

Click the Clear Screen button to clear all statements in the Input area, and all displayed output.

Cancel

Click the Cancel button to interrupt the script that is currently running.

iSQL*Plus History Screen

Click the History icon to display the History screen. The History screen allows you to reload scripts that you have previously executed in the same session.

A History entry is created each time you execute a script in the Work screen if it is not the same as the most recently executed script. The History screen shows the leading 80 characters of the script.

Once you load a script from History, it is moved to the top of the History list, and when the History limit is reached, the earliest scripts are removed. When you exit a session the History is lost, and History is not shared between sessions.

You can change the default number of entries stored in the History list in the Interface Options screen which you access from the Preferences screen.

Text description of the illustration histscn.gif

Script

Shows the current list of scripts in History. They are in most recently executed order, with the most recent at the top. You click the script text to load it into the Input area.

Scripts are displayed verbatim, so be careful if you have included items like CONNECT commands which include passwords.

Load

Loads the selected scripts into the Input area of the Work screen.

Delete

Deletes the selected scripts from History.

Cancel

Cancels the History screen without making further changes or loading a script from History.