Installing the Sample Schemas and Establishing a Database Connection

Much of the Oracle collateral requires you to install the sample database schemas and establish a connection to them. You need to use these schemas with JDeveloper for the "How To" examples on OTN and for several of the OBE tutorials. This tutorial describes how to install and populate the sample schema tables and how to connect to them in JDeveloper.

Approximately 30 minutes

Topics

This tutorial covers the following topics:

Overview
Prerequisites
Creating and Populating the Sample Schema Tables

Creating a Database Connection

Examining the Sample Schemas
Summary

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so, depending on your Internet connection, may result in a slow response time.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

JDeveloper OBEs and other samples use the HR and OE sample schemas. These schemas are included with the Oracle9i or Oracle 10g database, but you can also install them into an Oracle8i database by using the 8i sample schema scripts.

Once the schemas exist in the database, you must create a connection to them in JDeveloper in order to be able to use them. After completing this tutorial, you will be able to use the sample schemas in JDeveloper.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

1.

Install Oracle JDeveloper (10.1.3.1 or 10.1.3.0).

2.

Have access to an Oracle8i, Oracle9i, or Oracle 10g database, and know the passwords for the system and sys users.

3. Have SQL*Plus installed on your local computer.

Back to Topic List

Creating and Populating the Sample Schema Tables

Warning: For security reasons, it may not be advisable to install the sample schemas into a production database. If you do install them, you should use passwords other than default passwords, although default passwords are used in the examples shown in tutorials provided by Oracle. When you are finished using the sample schemas for tutorial and demo purposes, you may drop them by issuing the following SQL*Plus command for each installed sample schema:

DROP USER <schema_name> CASCADE;

The actions you take to create and populate the sample schemas are dependent on whether you have access to an Oracle8i, Oracle9i, or Oracle 10g database. This topic discusses the following subtopics:

Installing the Oracle9i or Oracle 10g Sample Schemas
Installing the Oracle8i Sample Schemas
Dropping the Oracle8i Sample Schemas

Installing the Oracle9i or Oracle 10g Sample Schemas

1.

If you have dropped the sample schemas from Oracle9i or Oracle 10g, or if you did not install them in the first place, you should install them following the instructions in Oracle9i Sample Schemas, Oracle Database Sample Schemas 10g Release 1 (10.1), or Oracle Database Sample Schemas 10g Release 2 (10.2). For 10g the SQL scripts are on a separate companion CD.

 

2.

If you are using the sample schemas for the first time, you may find that you must unlock the HR and OE sample schema users, and then grant CONNECT and RESOURCE roles to them. You can do this by using Oracle Enterprise Manager, which is part of Oracle9i or Oracle 10g.

Alternatively, you can issue the following SQL*Plus commands:

ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK;
GRANT CONNECT, RESOURCE to hr;

ALTER USER oe IDENTIFIED BY OE ACCOUNT UNLOCK;
GRANT CONNECT, RESOURCE to OE;

To invoke SQL*Plus from JDeveloper, select a database connection in the Connection Navigator (see Creating a Database Connection), then choose Tools > SQL*Plus from the menu.

The first time you do this, a dialog appears asking you to specify the location of SQL*Plus. Browse to the location of the SQL*Plus executable (sqlplus.exe or sqlplusw.exe) on your computer.

Back to Topic

Installing the Oracle8i Sample Schemas

If you do not have access to the Oracle9i or 10g sample schemas in an Oracle9i or 10g database, you can install the schemas into an Oracle8i database. When you run the scripts to install the Oracle8i sample schemas, any previously installed schemas with the names HR8 or OE8 are destroyed. If you have JDeveloper connections to HR8 or OE8 schemas, you should close them prior to reinstalling.

1.

Download the 8i sample schema scripts and extract them to a directory on your computer.

 

2.

In the JDeveloper toolbar , click Open, or choose File | Open from the menu.


3.

In the Open dialog, browse to change the Location to the directory where you extracted the sample schema scripts. Select the mksample8.sql script. Click Open to open the script in the JDeveloper code editor.

 

4.

Log files are saved by default to C:\TEMP\. If you are using UNIX, or want to save the log files to another location, you must change the occurrence of C:\TEMP\ in mksample8.sql to a new location. In the code editor, change C:\TEMP\ to a different location if needed.

 

5.

In the code editor, right-click mksample8.sql and select Run in SQL*Plus, then select the database connection to use (see Creating a Database Connection). This opens a SQL*Plus window where the script runs.

 

6.

When prompted, enter the system password, the database alias (preceded by @), the sys password, and passwords hr8 and oe8 for the hr8 and oe8 users. Important: Be sure to precede the database alias name with @.

After the script has completely run, type exit to close the SQL*Plus window.

The scripts create the users oe8 and hr8 in the database with the passwords that you have defined for them. The tables and other database objects in the schemas are also created and populated with sample data.

 

7.

You can examine the installation process in the log files mksample8hr8_main.log and mksample8oe8_main.log which are either in C:\TEMP\ or another location that you have defined. Note that the log shows an error if the user did not previously exist, because the first action of the script is to drop the user. You can ignore this error.

Back to Topic

Dropping the Oracle8i Sample Schemas

If you no longer need the Oracle8i Sample Schemas, you may choose to drop them. To do so, perform the following steps:

1.

In the JDeveloper toolbar , click Open, or choose File | Open from the menu.

 

2.

In the Open dialog, change the Location to the folder into which you extracted the sample schema scripts. Control-click to multi-select the hr8_drop.sql and oe8_drop.sql scripts. Click Open to open the scripts in the code editor.


3.

In the code editor, click the hr8_drop.sql tab. Right-click the file in the code editor and select Run in SQL*Plus from the context menu, then select the database connection to use. This opens a SQL*Plus window.

 

4.

When prompted, enter the password for the hr8 user and the database alias (preceded by @). Important: Be sure to precede the database alias name with @.

When the script finishes running, type exit to close the SQL*Plus window.

 

5.

Repeat steps 3 and 4 for the oe8_drop.sqlscript.

Back to Topic

Back to Topic List

Creating a Database Connection

This topic describes how to create a JDBC connection to the sample schemas to use with the JDeveloper OBEs and samples. To define a connection in JDeveloper, perform the following steps:

1.

Click the Connnections Navigator tab, or if it is not visible, choose View | Connection Navigator.

 

2.

Right-click the Database node and choose New Database Connection from the context menu.


3.

In the Create Database Connection wizard, review the information on the Welcome page and click Next.

 

4.

On the Type page of the wizard, enter a name for the connection In the Connection Name field.

The example uses the name tutorial_jdbc_connection, but you can use any name. Click Next.

 

5.

On the Authentication page of the wizard:

In the Username and Password fields, type the user name and password.

If you are using the sample schema scripts on an Oracle9i or 10g database, use the HR user for the Human Resources schema or the OE user for the Order Entry schema.

If you are using the Oracle8i sample schemas installed following the instructions in Creating and Populating the Sample Schema Tables, use the HR8 user for the Human Resources schema or the OE8 user for the Order Entry schema.

Select Deploy password.

Click Next.

 

6.

On the Connection page of the wizard:

In the Host name field, type the name or IP address of the computer where the database is located.

Enter the JDBC Port and then select either the SID or the Service Name option. Enter either the SID or the Service Name as appropriate. If you do not know these values, check with your database administrator.

Click Next.

 

7.

On the Test page of the wizard, c lick Test Connection.

If the database is available and the connection details are correct, you will see Success! in the Status window.

If an error occurs, verify the settings with your database administrator, click Back to make any changes necessary, and then retest the connection.

 

8.

Click Finish. The connection now appears below the Database connection node in the Connection Navigator.

 

9.

Create two connections of different names to use in JDeveloper samples and in other OBEs:

A connection named hr_conn to the HR or HR8 schema

A connection named oe_conn to the OE or OE8 schema

You can reuse these connections any time that you need JDBC access to the HR/HR8 or OE/OE8 schemas in this database.

Back to Topic List

Examining the Sample Schemas

Once you have created a connection to one of the sample schemas, you can examine the schemas from JDeveloper.

1.

Click the Connnections Navigator tab, or if it is not visible, choose View | Connection Navigator.

 

2.

Expand Database, then the node for the connection you have just made, then the node for the hr or OE schema. You can browse the database elements for this schema and use them in the JDeveloper OBEs.


Back to Topic List

In this tutorial you accomplished the following:

Back to Topic List

Place the cursor over this icon to hide all screenshots.