Introducing SQL and SQL*Plus

Learning objective

After completing this topic, you should be able to identify ways of sending SQL commands to a database and recognize how to start SQL*Plus and iSQL*Plus.

1. Sending SQL commands to a database

Structured Query Language (SQL) enables you to

query a database

add, delete, and move rows in a database table

create, replace, and make changes to database objects

manage access to databases and objects

Generally, a graphical user interface (GUI) writes your SQL queries for you according to the specifications you make using the interface controls.

However, when you need to execute SQL statements inside a script, you must provide the SQL code manually.

There are six SQL-statement categories that enable you to work with databases:

data definition language (DDL)

data manipulation language (DML)

transaction control

session control

system control

embedded

You can send SQL commands to a database using

SQL*Plus

iSQL*Plus

Oracle Enterprise Manager

Oracle Forms

Oracle Reports and Oracle Discoverer

third-party tools

SQL*Plus: SQL*Plus is a command-line tool that enables you to type SQL statements.
iSQL*Plus: iSQL*Plus is a browser-based interface to an Oracle database.
Oracle Enterprise Manager: Oracle Enterprise Manager is a GUI-based tool you can use to perform administrative tasks on database objects and structures.
Oracle Forms: Oracle Forms is a GUI environment that provides a forms-based way to work with databases. Behind the front end, the application generates SQL code that performs the actions you specify.
Oracle Reports and Oracle Discoverer: Oracle Reports and Oracle Discoverer are reporting tools that enable you to compose and execute complex database queries. Oracle Reports generates standard, noncustomizable reports. Oracle Discoverer is a useful data-mining tool that enables you to browse the database without knowing its relationships or structure.
third-party tools: A range of third-party tools are available that enable you to use SQL statements to work with databases.

Enterprise Manager generates SQL code according to the specifications you make.

You don't need to actually see this SQL code to use it.

However, if you want to view the code, you click the Show SQL button in the Enterprise Manager interface.

Suppose you want to use Oracle Enterprise Manager to enable logging for the EXAMPLE tablespace. You've already selected the Yes option in the Enable Logging section of the tablespace's Storage tabbed page.

To view the SQL code that has been generated, you click the Show SQL button.

The SQL code that implements the change displays.

You may find it useful to copy the code into a text file. This provides you with a record of the code you have used, and also makes the code available for use in scripts.

ALTER TABLESPACE "EXAMPLE" LOGGING

Question

Which of these are tools you can use to send SQL commands to a database?

Options:

An FTP connection

iSQL*Plus

Oracle Forms

Oracle Reports

Answer

iSQL*Plus, Oracle Forms, and Oracle Reports are tools you can use to send SQL commands to a database.

Option 1 is incorrect. A range of tools are available that can establish the correct type of connection to a database and then send SQL instructions to it.

Option 2 is correct. iSQL*Plus is a browser-based interface to an Oracle database.

Option 3 is correct. Oracle Forms provides a range of forms you can use to access and manage information in databases.

Option 4 is correct. Oracle reports provides a range of standard reports you can use to query a database.

2. Introducing SQL*Plus

The SQL*Plus command-line interface enables you to write, edit, store, and execute

SQL*Plus commands

SQL commands

PL/SQL blocks

The interface is commonly used by DBAs and developers to work with Oracle databases.

The SQL*Plus interface enables you to generate database query results.

You can use code to format the results, perform calculations within them, store them, and print them.

You can also generate lists of column definitions.

You can start SQL*Plus in a terminal window without connecting to a database.

You type sqlplus /nolog and press Enter.

The SQL> prompt displays.

Question

You wish to access the SQL*Plus command-line interface but do not wish to connect to the database.

Type the command that enables you to do this.


MISSING CODE

Answer

You type sqlplus /nolog.

To connect to the database, you type connect, followed by your username and password, which are separated by a forward slash.

Question

Identify the steps you can take when you are using the SQL*Plus interface to access the database.

Options:

Open Enterprise Manager

Provide a username and password

Use the connect command

Use the sqlplus command

Answer

To access a database using the SQL*Plus interface, you need to
use the sqlplus command
use the connect command
provide a username and password

Option 1 is incorrect. Opening Enterprise Manager is another way of accessing a database.

Option 2 is correct. To access a database, you need to provide a username and password following the connect command.

Option 3 is correct. At the SQL> prompt, you use the connect command to connect to the database, followed by your username and password.

Option 4 is correct. You use the sqlplus command to access the SQL> prompt.

3. Introducing iSQL*Plus

iSQL*Plus is a browser-based component of SQL*Plus. It provides a user interface that enables you to work with databases.

Before you can start iSQL*Plus, you need to start the server-side listener process.

You type isqlplusctl start and then press Enter to do this.

The command has started the server-side listener process.

When the listener process is started, you can connect to iSQL*Plus.

In your browser, you specify the iSQL*Plus URL and then you press Enter to access the iSQL*Plus login page.

Note

The default port number is 5560. This is used unless the Oracle Universal Installer (OUI) determines that this port is already in use.

To log into iSQL*Plus, you enter a username and password.

You use the Connect Identifier field to specify the Oracle Net database alias of the database you want to work with. In this case, you type orcl2.

Now you are ready to make the connection to the database.

You click the Login button.

The iSQL*Plus user interface displays in your browser.

Question

You have started the server-side listener process. You now need to access the orcl2 database using iSQL*Plus. You have already specified the iSQL*Plus URL in your browser.

Given that your username is scott and your password is password, which of these best describes how you log into the interface, accessing the orcl2 database?

Options:

Enter your username and password, leave the Connection Identifier field blank, and click Login.

Enter your username and password, type orcl2 in the Connection Identifier field, and click Login.

Answer

You enter your username and password, type orcl2 in the Connection Identifier field, and click Login.

These are some of the key elements of the iSQL*Plus interface.

Workspace tabbed page

Execute button

Output pane

History tab

Preferences button

Workspace tabbed page: The Workspace tabbed page enables you to enter SQL and SQL*Plus commands.
Execute button: The Execute button runs all the SQL commands listed in the Workspace tabbed page.
Output pane: The Output pane displays the results of SQL commands that are executed.
History tab: The History tabbed page lists the commands that have been used previously during the current session. By default, up to ten commands are listed. These are available for reuse. You can also delete commands from the list.
Preferences button: The Preferences button enables you to customize the appearance and functionality of the iSQL*Plus interface. For example, you can change the number of commands displayed in the History tabbed page.

Question

Match each element of the iSQL*Plus interface with the correct description of its function.

Options:

History tabbed page

Output pane

Workspace tabbed page

Targets:

Displays SQL command results

Enables you to enter SQL commands

Makes SQL commands available for reuse

Answer

The History tabbed page makes SQL commands available for reuse. The Output pane displays SQL command results. The Workspace tabbed page enables you to enter SQL commands.

By default, the History tabbed page lists the previous ten commands executed during the current session.

The Output pane displays the results of the commands executed during the current session.

You can run both SQL and SQL*Plus commands in the Workspace tabbed page.

Summary

SQL enables you to query and manage databases and objects. There are several tools you can use to send SQL commands to a database. These include SQL*Plus, iSQL*Plus, Oracle Enterprise Manager, Oracle Forms, Oracle Reports, and Oracle Discoverer.

SQL*Plus is a command-line interface that enables you to use SQL and SQL*Plus commands, as well as PL/SQL blocks. You use the sqlplus command to access the SQL> command prompt. Then you can use the connect command to connect to a database.

iSQL*Plus is a GUI tool that simplifies the use of SQL*Plus. It provides a range of interface features including the Workspace and History tabbed pages, the Output pane, the Preferences button, and the Execute button.

Oracle 10g

Wednesday, September 5, 2007