How to connect to oracle

3 Connecting to the Database

To interact with Oracle Database XE, you must typically connect to the database as a database user. The interaction might be through the SQL Command Line, through SQL Developer, or through utilities invoked from the system command line.

This chapter covers the following topics:

The following documents, for information on how to connect to the database from your appli cation:

About Local and Remote Connections

«Managing Network Connections» for information on how Oracle Database XE accepts connection requests over the network.

About Local Connections

Figure 3-1 Local Connection

Description of «Figure 3-1 Local Connection»

Before making a local connection on Linux, you must set environment variables. See «Setting Environment Variables» for more information.

Logging in to the Oracle Database XE host computer with an ssh (or telnet ) session and then starting the SQL Command Line (or other Oracle command-line utility) is considered a local connection, even though you start the ssh (or telnet ) application on a remote computer.

About Remote Connections

Connectin g remotely means running the SQL Command Line (or any other Oracle command-line utility) on a computer other than the Oracle Database XE host computer, and then initiating a database connection from the SQL Command Line (or other utility) over the network.

Oracle Client Software

The remote computer must have Oracle client software installed. It is through Oracle client software that Oracle command-line utilities (and your applications) connect to the database. Oracle Database XE accepts connections from all of the following types of Oracle clien t software:

Oracle Database Express Edition Client (Oracle Database XE)

When you install Oracle Database XE, Oracle Database Express Edition Client is also installed on the same computer. You can install Oracle Database XE separately on remote computers. It is available at

Instan t Clie nt

Instant Client is available at

Oracle client software for Oracle Database Enterprise Edition or Standard Edition (all supported releases of Oracle Database)

All Oracle client software includes Oracle Net, which is the Oracle network software that enables client applications on one computer to connect to databases on another computer over a network.

To connect remotely, you must supply not just a user name and password, but a complete Oracle Net conne ct string. In addition to the database user name and password, a connect string includes a host name or host IP address, an optional TCP port number, and an optional database service name. These additional parameters are required to help Oracle Net find the right host computer and connect to Oracle Database XE. An Oracle Net connect string has the following format:

host is the host name or IP address of the computer that is running Oracle Database XE

port (optional) is the TCP port number on which the Oracle Net listener is listening. If not specified, the default port number 1521 is assumed.

Only Oracle Database XE supports the notion of a default database service. If you connect remotely from any Oracle client software other than Oracle Database XE, you must include the XE service name.

Figure 3-2 Remote Connection

Description of «Figure 3-2 Remote Connection»

Before making a remote connection from Linux, you must set environment variables. See «Setting Environment Variables» for more information.

Remote Connection Examples

Example 1 This example initiates a remote connection from Oracle Database XE, using the default port number.

Example 2 This example initiates a remote connection from Oracle Database XE, using a nondefault port number (1522):

Example 3 This example initiates a remote connection using the default port number, and includes the optional service name.

Setting Environment Variables

the SQL Command Line (SQL*Plus) and other Oracle utilities retrieve configuration information from operating system environment variables. This section explains how to set these environment variables, and contains the following topics:

Setting Environment Variables on the Windows Platform

On the Win dows platform, environment variables are stored in the Windows registry, and are automatically set for you. You need not take any action involving environment variables before initiating a database connection.

Setting Environment Variables on the Linux Pla tform

On the Linux platform, before running the SQL Command Line or other Oracle utilities from a terminal session, you must set some environment variables for that session. The procedure for setting environment variables depends on whether you are connecting locally, or connecting remotely from Oracle Database XE.

Setting Environment Variables for a Local Connection

To set environment variables when connecting locally, enter one of the following commands in a terminal session.

For Bourne, Korn, or Bash shell:

There is no need to set environment variables before running the SQL Command Line from the K menu (KDE) or Applications menu (Gnome).

Setting Environment Variables for a Remote Connection

To set environment variables when connecting remotely from Oracle Database XE, enter one of the following commands in a terminal session at the remote computer:

Connect To Oracle Database

Summary: in this tutorial, you will learn how to connect to Oracle database server using SQL*plus and SQL developer tools.

Connect to Oracle Database Server using SQL*Plus

SQL*Plus is an interactive query tool installed automatically when you install Oracle Database Server or Client. SQL*Plus has a command-line interface that allows you to connect to Oracle Database server and execute statements interactively.

To launch the SQL*Plus program, from the Linux or Window terminal, you enter the sqlplus command:

You can also find the SQL*Plus program in the Program folder of the Start Menu on Windows as shown below:

When you launch the SQLPlus icon, it will prompt for username and password. Enter your username and password that you chose during the installation of the Oracle Database Server. If you don’t know which account to use, ask your Database Administrator.

Suppose you want to connect to the local Oracle Database Server using the sys account, you enter the following information:

After pressing enter, you should see a message followed by the SQL> command line as follows:

It means that you have connected to the Oracle Database Server.

If you want to switch to a pluggable database, use the ALTER SESSION statement to set the current database to the pluggable database e.g., PDBORDL as follows:

Now, you connected to the PDBORCL database.

To disconnect a user from the Oracle Database Server, you use the EXIT command:

To connect the OT user to the sample database located in the PDBORCL pluggable database, you enter the following command:

SQL*Plus prompts for the password of the OT user. Enter the password and you will be connected to the PDBORCL database in the Oracle Database Server.

Connect to Oracle Database Server using SQL Developer

SQL Developer is a free GUI tool for working with SQL in Oracle Databases. Like the SQL*Plus program, SQL Developer is installed automatically when you install the Oracle Database Server or Client.

To launch the SQL Developer program, you click the SQL Developer icon in the Oracle Program folder in Start Menu as shown in the following picture:

The following shows the SQL Developer program:

The following New / Select Database Connection dialog will display:

In this dialog, you need to enter the following information:

First, enter the following information:

Second, choose TNS as the connection type.

Third, the Network Alias list will be displayed after you choose the TNS connection type. Just select the PDBORCL as the network alias. By doing this, you explicitly specify the pluggable database PDBORCL to which you want to connect.

Fourth, click the Test button to test the database connection. If you see status: Success message, you are ready to connect to the Oracle Database Server.

To save time re-entering all the connection parameters again, click the Save button. The connection will be saved and displayed on the left side of the dialog. Whenever you want to reconnect, you just need to click the saved connection, all the parameters will be filled out automatically.

Now, you can click the Connect button to connect to the Oracle Database Server.

Double click the Connections > OT > Tables, you will see all tables in the OT sample database.

In this tutorial, you have learned how to connect to the Oracle Database Server using SQL*Plus and SQL Developer.

2 Connecting to Oracle Database and Exploring It

You can connect to Oracle Database only through a client program, such as SQL*Plus or SQL Developer.

After connecting to Oracle Database through a client program, you enter and run commands in that client program. For details, see the documentation for your client program.

This chapter contains:

Connecting to Oracle Database from SQL*Plus

SQL*Plus is a client program with which you can access Oracle Database. This section shows how to start SQL*Plus and connect to Oracle Database.

For steps 3 and 4 of the following procedure, you need a user name and password.

To connect to Oracle Database from SQL*Plus:

If you are on a Windows system, display a Windows command prompt.

For security, your password is not visible on your screen.

The system connects you to an Oracle Database instance.

You are in the SQL*Plus environment. At the SQL> prompt, you can enter and run SQL*Plus commands, SQL statements, PL/SQL statements, and operating system commands.

Exiting SQL*Plus ends the SQL*Plus session, but does not shut down the Oracle Database instance.

Example 2-1 Connecting to Oracle Database from SQL*Plus

«About SQL*Plus» for a brief description of SQL*Plus

SQL*Plus User’s Guide and Reference for more information about starting SQL*Plus and connecting to Oracle Database

Connecting to Oracle Database from SQL Developer

SQL Developer is a client program with which you can access Oracle Database. With Oracle Database 11 g Release 2 (11.2), Oracle recommends using SQL Developer version 4.0 or later, which you can download from:

This section assumes that SQL Developer is installed on your system, and shows how to start it and connect to Oracle Database. If SQL Developer is not installed on your system, then see Oracle Database SQL Developer User’s Guide for installation instructions.

For the following procedure:

For step 3, you need a user name and password.

To connect to Oracle Database from SQL Developer:

In the New/Select Database Connection window:

Type the appropriate values in the fields Connection Name, Username, and Password.

For security, the password characters that you type appear as asterisks.

Near the Password field is the check box Save Password. By default, it is deselected. Oracle recommends accepting the default.

In the Oracle pane, accept the default values.

(The default values are: Connection Type, Basic; Role, default, Hostname, localhost; Port, 1521; SID option, selected; SID field, xe.)

Description of the illustration success.gif

The New/Select Database Connection window closes. The Connections frame shows the connection whose name you entered in the Connection Name field in step 3.

You are in the SQL Developer environment.

To exit SQL Developer, select Exit from the File menu.

Exiting SQL Developer ends the SQL Developer session, but does not shut down the Oracle Database instance. The next time you start SQL Developer, the connection you created using the preceding procedure still exists. SQL Developer prompts you for the password that you supplied in step 3 (unless you selected the check box Save Password).

«About SQL Developer» for a brief description of SQL Developer

Oracle Database SQL Developer User’s Guide for more information about using SQL Developer to create connections to Oracle Database

Connecting to Oracle Database as User HR

This section contains:

Unlocking the HR Account

This section shows how to unlock the HR account and reset its password.

By default, when the HR schema is installed, the HR account is locked and its password is expired. You can connect to Oracle Database as the user HR only if the HR account is unlocked.

For the following procedure, you need the name and password of a user who has the ALTER USER system privilege.

To unlock the HR account and reset its password:

Using SQL*Plus, connect to Oracle Database as a user with the ALTER USER system privilege.

At the SQL> prompt, unlock the HR account and reset its password:

The system responds:

Oracle Database 2 Day + Security Guide for information about predefined user accounts provided by Oracle Database, including HR

Oracle Database SQL Developer User’s Guide for information about accessing SQL*Plus within SQL Developer

Connecting to Oracle Database as User HR from SQL*Plus

This section shows how to connect to Oracle Database as the user HR from SQL*Plus.

If the HR account is locked, see «Unlocking the HR Account» and then return to this section.

To connect to Oracle Database as user HR from SQL*Plus:

For this task, you need the password for the HR account.

If you are connected to Oracle Database, close your current connection.

Follow the directions in «Connecting to Oracle Database from SQL*Plus», entering the user name HR at step 3 and the password for the HR account at step 4.

SQL*Plus User’s Guide and Reference for an example of using SQL*Plus to create an HR connection

Connecting to Oracle Database as User HR from SQL Developer

This section shows how to connect to Oracle Database as the user HR from SQL Developer.

If the HR account is locked, see «Unlocking the HR Account» and then return to this section.

To connect to Oracle Database as user HR from SQL Developer:

For this task, you need the password for the HR account.

Follow the directions in «Connecting to Oracle Database from SQL Developer», entering the following values at steps 3:

For Password, enter the password for the HR account.

Exploring Oracle Database with SQL*Plus

This section contains:

If you are not connected to Oracle Database as user HR from SQL*Plus, see «Connecting to Oracle Database as User HR from SQL*Plus» and then return to this section.

Viewing HR Schema Objects with SQL*Plus

Example 2-2 shows how to view the names and data types of the objects that belong to the HR schema.

Example 2-2 Viewing HR Schema Objects with SQL*Plus

Result is similar to:

Oracle Database Reference for information about USER_OBJECTS

«Selecting Table Data» for information about using queries to view table data

«About Sample Schema HR» for general information about the schema HR

Viewing EMPLOYEES Table Properties and Data with SQL*Plus

Example 2-3 shows how to view the properties of the EMPLOYEES table in the HR schema.

Example 2-3 Viewing EMPLOYEES Table Properties with SQL*Plus

Example 2-4 shows how to view some data in the EMPLOYEES table in the HR schema.

Example 2-4 Viewing EMPLOYEES Table Data with SQL*Plus

Result is similar to:

«Selecting Table Data» for information about using queries to view table data

Oracle Database Reference for information about static data dictionary views

Exploring Oracle Database with SQL Developer

This section contains:

Tutorial: Viewing HR Schema Objects with SQL Developer

This tutorial shows how to use SQL Developer to view the objects that belong to the HR schema—that is, how to browse the HR schema.

If you are not connected to Oracle Database as user HR from SQL Developer, see «Connecting to Oracle Database as User HR from SQL Developer» and then return to this tutorial.

To browse the HR schema:

If you are not connected to the database, the Connection Information window opens. If you are connected to the database, the hr_conn information expands (see the information that follows «Click OK» in step 2).

If the Connection Information window opens:

The hr_conn information expands: The plus sign becomes a minus sign (-), and under the hr_conn icon, a list of schema object types appears—Tables, Views, Indexes, and so on. (If you click the minus sign, the hr_conn information collapses: The minus sign becomes a plus sign, and the list disappears.)

Oracle Database SQL Developer User’s Guide for more information about the SQL Developer user interface

«About Sample Schema HR» for general information about schema HR

Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer

This tutorial shows how to use SQL Developer to view the properties and data of the EMPLOYEES table in the HR schema.

If you are not browsing the HR schema, see «Tutorial: Viewing HR Schema Objects with SQL Developer» and then return to this tutorial.

To view the properties and data of the EMPLOYEES table:

Under Tables, a list of the tables in the HR schema appears.

In the right frame of the Oracle SQL Developer window, in the Columns pane, a list of all columns of this table appears. To the right of each column are its properties—name, data type, and so on. (To see all column properties, move the horizontal scroll bar to the right.)

The Data pane appears, showing a numbered list of all records in this table. (To see more records, move the vertical scroll bar down. To see more columns of the records, move the horizontal scroll bar to the right.)

The Constraints pane appears, showing a list of all constraints on this table. To the right of each constraint are its properties—name, type, search condition, and so on. (To see all constraint properties, move the horizontal scroll bar to the right.)

Explore the other properties by clicking on the appropriate tabs.

Oracle Database SQL Developer User’s Guide for more information about the SQL Developer user interface

2 Connecting to Oracle Database and Exploring It

You can connect to Oracle Database only through a client program, such as SQL*Plus or SQL Developer.

After connecting to Oracle Database through a client program, you enter and run commands in that client program. For details, see the documentation for your client program.

This chapter contains:

Connecting to Oracle Database from SQL*Plus

SQL*Plus is a client program with which you can access Oracle Database. This section shows how to start SQL*Plus and connect to Oracle Database.

For steps 3 and 4 of the following procedure, you need a user name and password.

To connect to Oracle Database from SQL*Plus:

If you are on a Windows system, display a Windows command prompt.

For security, your password is not visible on your screen.

The system connects you to an Oracle Database instance.

You are in the SQL*Plus environment. At the SQL> prompt, you can enter and run SQL*Plus commands, SQL statements, PL/SQL statements, and operating system commands.

Exiting SQL*Plus ends the SQL*Plus session, but does not shut down the Oracle Database instance.

Example 2-1 Connecting to Oracle Database from SQL*Plus

«About SQL*Plus» for a brief description of SQL*Plus

SQL*Plus User’s Guide and Reference for more information about starting SQL*Plus and connecting to Oracle Database

Connecting to Oracle Database from SQL Developer

SQL Developer is a client program with which you can access Oracle Database. With Oracle Database 12 c Release 1 (12.1), Oracle recommends using SQL Developer version 4.0 or later, which you can download from:

This section assumes that SQL Developer is installed on your system, and shows how to start it and connect to Oracle Database. If SQL Developer is not installed on your system, then see Oracle SQL Developer User’s Guide for installation instructions.

For the following procedure:

For step 3, you need a user name and password.

To connect to Oracle Database from SQL Developer:

In the New/Select Database Connection window:

Type the appropriate values in the fields Connection Name, Username, and Password.

For security, the password characters that you type appear as asterisks.

Near the Password field is the check box Save Password. By default, it is deselected. Oracle recommends accepting the default.

In the Oracle pane, accept the default values.

(The default values are: Connection Type, Basic; Role, default, Hostname, localhost; Port, 1521; SID option, selected; SID field, xe.)

Description of the illustration success.gif

The New/Select Database Connection window closes. The Connections frame shows the connection whose name you entered in the Connection Name field in step 3.

You are in the SQL Developer environment.

To exit SQL Developer, select Exit from the File menu.

Exiting SQL Developer ends the SQL Developer session, but does not shut down the Oracle Database instance. The next time you start SQL Developer, the connection you created using the preceding procedure still exists. SQL Developer prompts you for the password that you supplied in step 3 (unless you selected the check box Save Password).

«About SQL Developer» for a brief description of SQL Developer

Oracle SQL Developer User’s Guide for more information about using SQL Developer to create connections to Oracle Database

Connecting to Oracle Database as User HR

This section contains:

Unlocking the HR Account

This section shows how to unlock the HR account and reset its password.

By default, when the HR schema is installed, the HR account is locked and its password is expired. You can connect to Oracle Database as the user HR only if the HR account is unlocked.

For the following procedure, you need the name and password of a user who has the ALTER USER system privilege.

To unlock the HR account and reset its password:

Using SQL*Plus, connect to Oracle Database as a user with the ALTER USER system privilege.

At the SQL> prompt, unlock the HR account and reset its password:

The system responds:

Oracle Database 2 Day + Security Guide for information about predefined user accounts provided by Oracle Database, including HR

Oracle SQL Developer User’s Guide for information about accessing SQL*Plus within SQL Developer

Connecting to Oracle Database as User HR from SQL*Plus

This section shows how to connect to Oracle Database as the user HR from SQL*Plus.

If the HR account is locked, see «Unlocking the HR Account» and then return to this section.

To connect to Oracle Database as user HR from SQL*Plus:

For this task, you need the password for the HR account.

If you are connected to Oracle Database, close your current connection.

Follow the directions in «Connecting to Oracle Database from SQL*Plus», entering the user name HR at step 3 and the password for the HR account at step 4.

SQL*Plus User’s Guide and Reference for an example of using SQL*Plus to create an HR connection

Connecting to Oracle Database as User HR from SQL Developer

This section shows how to connect to Oracle Database as the user HR from SQL Developer.

If the HR account is locked, see «Unlocking the HR Account» and then return to this section.

To connect to Oracle Database as user HR from SQL Developer:

For this task, you need the password for the HR account.

Follow the directions in «Connecting to Oracle Database from SQL Developer», entering the following values at steps 3:

For Password, enter the password for the HR account.

Exploring Oracle Database with SQL*Plus

This section contains:

If you are not connected to Oracle Database as user HR from SQL*Plus, see «Connecting to Oracle Database as User HR from SQL*Plus» and then return to this section.

Viewing HR Schema Objects with SQL*Plus

Example 2-2 shows how to view the names and data types of the objects that belong to the HR schema.

Example 2-2 Viewing HR Schema Objects with SQL*Plus

Result is similar to:

Oracle Database Reference for information about USER_OBJECTS

«Selecting Table Data» for information about using queries to view table data

«About Sample Schema HR» for general information about the schema HR

Viewing EMPLOYEES Table Properties and Data with SQL*Plus

Example 2-3 shows how to view the properties of the EMPLOYEES table in the HR schema.

Example 2-3 Viewing EMPLOYEES Table Properties with SQL*Plus

Example 2-4 shows how to view some data in the EMPLOYEES table in the HR schema.

Example 2-4 Viewing EMPLOYEES Table Data with SQL*Plus

Result is similar to:

«Selecting Table Data» for information about using queries to view table data

Oracle Database Reference for information about static data dictionary views

Exploring Oracle Database with SQL Developer

This section contains:

Tutorial: Viewing HR Schema Objects with SQL Developer

This tutorial shows how to use SQL Developer to view the objects that belong to the HR schema—that is, how to browse the HR schema.

If you are not connected to Oracle Database as user HR from SQL Developer, see «Connecting to Oracle Database as User HR from SQL Developer» and then return to this tutorial.

To browse the HR schema:

If you are not connected to the database, the Connection Information window opens. If you are connected to the database, the hr_conn information expands (see the information that follows «Click OK» in step 2).

If the Connection Information window opens:

The hr_conn information expands: The plus sign becomes a minus sign (-), and under the hr_conn icon, a list of schema object types appears—Tables, Views, Indexes, and so on. (If you click the minus sign, the hr_conn information collapses: The minus sign becomes a plus sign, and the list disappears.)

Oracle SQL Developer User’s Guide for more information about the SQL Developer user interface

«About Sample Schema HR» for general information about schema HR

Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer

This tutorial shows how to use SQL Developer to view the properties and data of the EMPLOYEES table in the HR schema.

If you are not browsing the HR schema, see «Tutorial: Viewing HR Schema Objects with SQL Developer» and then return to this tutorial.

To view the properties and data of the EMPLOYEES table:

Under Tables, a list of the tables in the HR schema appears.

In the right frame of the Oracle SQL Developer window, in the Columns pane, a list of all columns of this table appears. To the right of each column are its properties—name, data type, and so on. (To see all column properties, move the horizontal scroll bar to the right.)

The Data pane appears, showing a numbered list of all records in this table. (To see more records, move the vertical scroll bar down. To see more columns of the records, move the horizontal scroll bar to the right.)

The Constraints pane appears, showing a list of all constraints on this table. To the right of each constraint are its properties—name, type, search condition, and so on. (To see all constraint properties, move the horizontal scroll bar to the right.)

Explore the other properties by clicking on the appropriate tabs.

Oracle SQL Developer User’s Guide for more information about the SQL Developer user interface

How to connect to oracle

You can connect to Oracle Database only through a client program, such as SQL*Plus or SQL Developer. When connected to the database, you can view schema objects, view the properties and data of Oracle Database tables, and use queries to retrieve data from Oracle Database tables.

After connecting to Oracle Database through a client program, you enter and run commands in that client program. For details, see the documentation for your client program.

2.1 Connecting to Oracle Database from SQL*Plus

SQL*Plus is a client program from which you can access Oracle Database. This topic shows how to start SQL*Plus and connect to Oracle Database.

For steps 3 and 4 of the following procedure, you need a user name and password.

To connect to Oracle Database from SQL*Plus:

If you are on a Windows system, display a Windows command prompt.

For security, your password is not visible on your screen.

The system connects you to an Oracle Database instance.

You are in the SQL*Plus environment. At the SQL> prompt, you can enter and run SQL*Plus commands, SQL statements, PL/SQL statements, and operating system commands.

Exiting SQL*Plus ends the SQL*Plus session, but does not shut down the Oracle Database instance.

Example 2-1 Connecting to Oracle Database from SQL*Plus

«About SQL*Plus» for a brief description of SQL*Plus

SQL*Plus User’s Guide and Reference for more information about starting SQL*Plus and connecting to Oracle Database

2.2 Connecting to Oracle Database from SQL Developer

SQL Developer is a client program with which you can access Oracle Database.

You are encouraged to use the currently available release of SQL Developer, which you can download from:

This section assumes that SQL Developer is installed on your system, and shows how to start it and connect to Oracle Database. If SQL Developer is not installed on your system, then see Oracle SQL Developer User’s Guide for installation instructions.

For the following procedure:

If you’re using a SQL Developer kit that does not include the JDK, then the first time you start SQL Developer on your system, you must provide the path to the Java Development Kit.

When prompted, you need to enter a user name and password.

To connect to Oracle Database from SQL Developer:

Start SQL Developer.

In the New/Select Database Connection window:

For security, the password characters that you type appear as asterisks.

In the Oracle pane, accept the default values.

The New/Select Database Connection window closes. The Connections frame shows the connection whose name you entered in the Connection Name field in step 3.

You are in the SQL Developer environment.

To exit SQL Developer, select Exit from the File menu.

Exiting SQL Developer ends the SQL Developer session, but does not shut down the Oracle Database instance. The next time you start SQL Developer, the connection you created using the preceding procedure still exists. SQL Developer prompts you for the password that you supplied in step 3 (unless you selected the check box Save Password ).

«About SQL Developer» for a brief description of SQL Developer

Oracle SQL Developer User’s Guide for more information about using SQL Developer to create connections to Oracle Database

2.3 Connecting to Oracle Database as User HR

The user HR owns the HR sample schema that the examples and tutorials in this document use.

2.3.1 Unlocking the HR Account

You must unlock the HR account and reset its password before you can connect to Oracle Database as the user HR.

By default, when the HR schema is installed, the HR account is locked and its password is expired.

For the following procedure, you need the name and password of a user who has the ALTER USER system privilege.

To unlock the HR account and reset its password:

Using SQL*Plus, connect to Oracle Database as a user with the ALTER USER system privilege.

At the SQL> prompt, unlock the HR account and reset its password:

The system responds:

Oracle SQL Developer User’s Guide for information about accessing SQL*Plus within SQL Developer

2.3.2 Connecting to Oracle Database as User HR from SQL*Plus

You can use SQL*Plus to connect to Oracle Database as the HR user.

If the HR account is locked, see «Unlocking the HR Account» and then return to this section.

To connect to Oracle Database as user HR from SQL*Plus:

For this task, you need the password for the HR account.

If you are connected to Oracle Database, close your current connection.

You are now connected to Oracle Database as the user HR.

SQL*Plus User’s Guide and Reference for an example of using SQL*Plus to create an HR connection

2.3.3 Connecting to Oracle Database as User HR from SQL Developer

You can use SQL Developer to connect to Oracle Database as the HR user.

If the HR account is locked, see «Unlocking the HR Account» and then return to this section.

To connect to Oracle Database as user HR from SQL Developer:

For this task, you need the password for the HR account.

You are now connected to Oracle Database as the user HR.

2.4 Exploring Oracle Database with SQL*Plus

If you are connected to Oracle Database from SQL*Plus as the user HR, you can view HR schema objects and the properties of the EMPLOYEES table.

If you are not connected to Oracle Database as user HR from SQL*Plus, see «Connecting to Oracle Database as User HR from SQL*Plus» and then return to this section.

2.4.1 Viewing HR Schema Objects with SQL*Plus

Example 2-2 shows how to view the names and data types of the objects that belong to the HR schema.

Example 2-2 Viewing HR Schema Objects with SQL*Plus

Result is similar to:

Oracle Database Reference for information about USER_OBJECTS

«Selecting Table Data» for information about using queries to view table data

«About Sample Schema HR» for general information about the schema HR

2.4.2 Viewing EMPLOYEES Table Properties and Data with SQL*Plus

You can a SQL*Plus command, the SQL SELECT statement, and static data dictionary views to view the properties and data of the HR.EMPLOYEES table.

Example 2-3 shows how to view the properties of the EMPLOYEES table in the HR schema.

Example 2-3 Viewing EMPLOYEES Table Properties with SQL*Plus

Example 2-4 shows how to view some data in the EMPLOYEES table in the HR schema.

Example 2-4 Viewing EMPLOYEES Table Data with SQL*Plus

Result is similar to:

«Selecting Table Data» for information about using queries to view table data

Oracle Database Reference for information about static data dictionary views

2.5 Exploring Oracle Database with SQL Developer

If you are connected to Oracle Database from SQL Developer as the user HR, you can view HR schema objects and the properties of the EMPLOYEES table.

2.5.1 Tutorial: Viewing HR Schema Objects with SQL Developer

This tutorial shows how to use SQL Developer to view the objects that belong to the HR schema—that is, how to browse the HR schema.

If you are not connected to Oracle Database as user HR from SQL Developer, see «Connecting to Oracle Database as User HR from SQL Developer» and then return to this tutorial.

To browse the HR schema:

If you are not connected to the database, the Connection Information window opens. If you are connected to the database, the hr_conn information expands (see the information that follows «Click OK» in step 2).

If the Connection Information window opens:

In the Password field, enter the password for the user HR.

The hr_conn information expands: The plus sign becomes a minus sign (-), and under the hr_conn icon, a list of schema object types appears—Tables, Views, Indexes, and so on. (If you click the minus sign, the hr_conn information collapses: The minus sign becomes a plus sign, and the list disappears.)

Oracle SQL Developer User’s Guide for more information about the SQL Developer user interface

«About Sample Schema HR» for general information about schema HR

2.5.2 Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer

This tutorial shows how to use SQL Developer to view the properties and data of the EMPLOYEES table in the HR schema.

If you are not browsing the HR schema, see «Tutorial: Viewing HR Schema Objects with SQL Developer» and then return to this tutorial.

To view the properties and data of the EMPLOYEES table:

In the right frame of the Oracle SQL Developer window, in the Columns pane, a list of all columns of this table appears. To the right of each column are its properties—name, data type, and so on. (To see all column properties, move the horizontal scroll bar to the right.)

The Data pane appears, showing a numbered list of all records in this table. (To see more records, move the vertical scroll bar down. To see more columns of the records, move the horizontal scroll bar to the right.)

The Constraints pane appears, showing a list of all constraints on this table. To the right of each constraint are its properties—name, type, search condition, and so on. (To see all constraint properties, move the horizontal scroll bar to the right.)

Oracle SQL Developer User’s Guide for more information about the SQL Developer user interface

2.6 Selecting Table Data

2.6.1 About Queries

A query, or SQL SELECT statement, selects data from one or more tables or views.

The simplest form of query has this syntax:

The select_list specifies the columns from which the data is to be selected, and the source_list specifies the tables or views that have these columns.

A query nested within another SQL statement is called a subquery.

In the SQL*Plus environment, you can enter a query (or any other SQL statement) after the SQL> prompt.

In the SQL Developer environment, you can enter a query (or any other SQL statement) in the Worksheet.

Oracle Database SQL Language Reference for more information about queries and subqueries

Oracle Database SQL Language Reference for more information about the SELECT statement

SQL*Plus User’s Guide and Reference for more information about the SQL*Plus command line interface

Oracle SQL Developer User’s Guide for information about using the Worksheet in SQL Developer

2.6.2 Running Queries in SQL Developer

The Worksheet is not limited to queries; you can use it to run any SQL statement.

To run queries in SQL Developer:

If the right frame of SQL Developer shows the hr_conn pane:

If the Select Connection window opens:

Oracle SQL Developer User’s Guide for information about using the Worksheet in SQL Developer

2.6.3 Tutorial: Selecting All Columns of a Table

This tutorial shows how to select all columns of the EMPLOYEES table.

To select all columns of the EMPLOYEES Table:

Be very careful about using SELECT * on tables with columns that store sensitive data, such as passwords or credit card information.

«Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer» for information about another way to view table data with SQL Developer

2.6.4 Tutorial: Selecting Specific Columns of a Table

This tutorial shows how to select only the columns FIRST_NAME, LAST_NAME, and DEPARTMENT_ID of the EMPLOYEES table.

To select only FIRST_NAME, LAST_NAME, and DEPARTMENT_ID:

2.6.5 Displaying Selected Columns Under New Headings

In displayed query results, default column headings are column names. To display a column under a new heading, specify the new heading ( alias) immediately after the column name. The alias renames the column for the duration of the query, but does not change its name in the database.

If you enclose column aliases in double quotation marks, case is preserved, and the aliases can include spaces, as in Example 2-6.

Oracle Database SQL Language Reference for more information about the SELECT statement, including the column alias ( c_alias )

Example 2-5 Displaying Selected Columns Under New Headings

Result is similar to:

Example 2-6 Preserving Case and Including Spaces in Column Aliases

Result is similar to:

2.6.6 Selecting Data that Satisfies Specified Conditions

To select only data that matches a specified condition, include the WHERE clause in the SELECT statement.

The condition in the WHERE clause can be any SQL condition (for information about SQL conditions, see Oracle Database SQL Language Reference ).

The query in Example 2-7 selects data only for employees in department 90.

To select data only for employees in departments 100, 110, and 120, use this WHERE clause:

The query in Example 2-8 selects data only for employees whose last names start with «Ma».

To select data only for employees whose last names include «ma», use this WHERE clause:

The query in Example 2-9 tests for two conditions—whether the salary is at least 11000, and whether the commission percentage is not null.

Oracle Database SQL Language Reference for more information about the SELECT statement, including the WHERE clause

Oracle Database SQL Language Reference for more information about SQL conditions

Example 2-7 Selecting Data from One Department

Result is similar to:

Example 2-8 Selecting Data for Last Names that Start with the Same Substring

Result is similar to:

Example 2-9 Selecting Data that Satisfies Two Conditions

Result is similar to:

2.6.7 Sorting Selected Data

When query results are displayed, records can be in any order, unless you specify their order with the ORDER BY clause.

Alternatively, in SQL Developer, you can omit the ORDER BY clause and double-click the name of the column to sort.

The sort criterion need not be included in the select list, as Example 2-11 shows.

Oracle Database SQL Language Reference for more information about the SELECT statement, including the ORDER BY clause

Example 2-10 Sorting Selected Data by LAST_NAME

Example 2-11 Sorting Selected Data by an Unselected Column

2.6.8 Selecting Data from Multiple Tables

To select data from multiple tables, you use a query that is called a join. The tables in a join must share at least one column name.

Suppose that you want to select the FIRST_NAME, LAST_NAME, and DEPARTMENT_NAME of every employee. FIRST_NAME and LAST_NAME are in the EMPLOYEES table, and DEPARTMENT_NAME is in the DEPARTMENTS table. Both tables have DEPARTMENT_ID. You can use the query in Example 2-12.

Table-name qualifiers are optional for column names that appear in only one table of a join, but are required for column names that appear in both tables. The following query is equivalent to the query in Example 2-12:

To make queries that use qualified column names more readable, use table aliases, as in the following example:

Although you create the aliases in the FROM clause, you can use them earlier in the query, as in the following example:

Example 2-12 Selecting Data from Two Tables (Joining Two Tables)

2.6.9 Using Operators and Functions in Queries

The select_list of a query can include SQL expressions, which can include SQL operators and SQL functions. These operators and functions can have table data as operands and arguments. The SQL expressions are evaluated, and their values appear in the results of the query.

Oracle Database SQL Language Reference for more information about SQL operators

Oracle Database SQL Language Reference for more information about SQL functions

2.6.9.1 Using Arithmetic Operators in Queries

The query in Example 2-13 displays LAST_NAME, SALARY (monthly pay), and annual pay for each employee in department 90, in descending order of SALARY.

Example 2-13 Using an Arithmetic Expression in a Query

2.6.9.2 Using Numeric Functions in Queries

Numeric functions accept numeric input and return numeric values. Each numeric function returns a single value for each row that is evaluated.

The query in Example 2-14 uses the numeric function ROUND to display the daily pay of each employee in department 100, rounded to the nearest cent.

The query in Example 2-15 uses the numeric function TRUNC to display the daily pay of each employee in department 100, truncated to the nearest dollar.

Oracle Database SQL Language Reference for more information about SQL numeric functions

Example 2-14 Rounding Numeric Data

Example 2-15 Truncating Numeric Data

2.6.9.3 Using the Concatenation Operator in Queries

The query in Example 2-16 concatenates the first name, a space, and the last name of each selected employee.

Oracle Database SQL Language Reference for more information about the concatenation operator

Example 2-16 Concatenating Character Data

2.6.9.4 Using Character Functions in Queries

Character functions accept character input. Most return character values, but some return numeric values. Each character function returns a single value for each row that is evaluated.

The query in Example 2-17 displays LAST_NAME in uppercase, FIRST_NAME with the first character in uppercase and all others in lowercase, and EMAIL in lowercase.

Oracle Database SQL Language Reference for more information about SQL character functions

Example 2-17 Changing the Case of Character Data

2.6.9.5 Using Datetime Functions in Queries

For each DATE and time stamp value, Oracle Database stores this information:

Oracle Database SQL Language Reference for more information about SQL datetime functions

Example 2-18 Displaying the Number of Years Between Dates

Example 2-19 Displaying System Date and Time

Results depend on current SYSTIMESTAMP value, but have this format: