Installing the Sample Schemas and Establishing a Database Connection

Purpose

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 10g for the code samples on OTN and for several of the OBE lessons. This lesson describes how to install and populate the sample schema tables and how to connect to them in JDeveloper.

Topics

This lesson will discuss the following:

 Overview
 Prerequisites
 Step 1 - Creating and Populating the Sample Schema Tables

Step 2 - Creating a Database Connection

 Step 3 - Examining the Sample Schemas
 Summary

Time to complete

30 minutes

Viewing Screenshots

Move your mouse over this icon to see the image Move your mouse over this icon to show all screenshots. You can also move your mouse over each individual icon to see only the screenshot associated with 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 lesson, you will be able to use the sample schemas in JDeveloper.

Back to Topic List

Prerequisites

Back to Topic List

In order for this lesson to work successfully, you will need to have performed the following:

1.

Install Oracle JDeveloper 10g.

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

0&&parent.frames.length) { d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);} if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<(a.length-2);i+=3) if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];} } //-->

Step 1 - Creating and Populating the Sample Schema Tables

Back to Topic List

The actions you take to create and populate the sample schemas are dependent on whether you have access to an Oracle8i or an Oracle9i 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

Back to List

1.

If you have dropped the sample schemas from Oracle9i or Oracle 10g, or did not install them in the first place, you should install them following the instructions in Oracle9i Sample Schemas or Oracle Database Sample Schemas 10g Release 1 (10.1).

 

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 10 g .

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;

Move your mouse over this icon to see the image

To invoke SQL*Plus from JDeveloper, select a database connection in the Connection Navigator, then choose T ools > S QL*Plus from the menu. The first time you do this, a dialog appears asking you to specify the location of SQL*Plus.

Move your mouse over this icon to see the image

 

Installing the Oracle8i Sample Schemas

Back to List

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 using the user names HR8 or OE8 will be 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 to your machine and extract them to a directory on your machine.

Move your mouse over this icon to see the image

 

2.

Create a new workspace:
Right-click the Application node in the Application Navigator and select New A pplication Workspace from the context menu.

Move your mouse over this icon to see the image

 

3.

Name the application workspace Samples, then click OK.

Move your mouse over this icon to see the image

 

4.

With Project selected in the Application Navigator, click Open, or choose File | Open from the menu.

Move your mouse over this icon to see the image

In the Open dialog, browse to change the Location to the directory where you extracted the sample schema scripts. Select the mksample8.sq l script. Make sure that Add to Project is checked and then click Open to add the script to your project. The script is added to the project and opens automatically in the code editor.

Move your mouse over this icon to see the image

 

5.

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.

Move your mouse over this icon to see the image

 

6.

In the Application Navigator, right-click mksample8.sql and select Run in SQL*Plus, then select the database connection to use. This opens a SQL*Plus window where the script runs.

Move your mouse over this icon to see the image

 

7.

When prompted, enter the system password, the database name (preceded by @), the sys password, and passwords hr8 and oe8 for the hr8 and oe8 users.

Move your mouse over this icon to see the image

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 using the passwords you have defined for them. The tables and other database objects in the schemas are also created and populated with sample data.

 

8.

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 will show 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.

Move your mouse over this icon to see the image

 

Dropping the Oracle8i Sample Schemas

Back to List

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

1.

With Project selected in the Application Navigator, click Open, or choose File | Open from the menu.

Move your mouse over this icon to see the image


2.

In the Open dialog, browse to change the Location to the folder into which you extracted the sample schema scripts. Control-click to multi-select the hr8_drop.sq l and oe8_drop.sql scripts. Make sure that Add to Project is checked and then click Open to add the scripts to your project. The scripts are added to the project and opened automatically in the code editor.

Move your mouse over this icon to see the image

 

3.

In the Application Navigator, right-click the hr8_drop.sq l file select Run in SQL*Plus, then select the database connection to use. This opens a SQL*Plus window.

Move your mouse over this icon to see the image

 

4.

When prompted, enter the password for the hr8 user concatenated with the database name, separated by @; for example, hr8@mydb .

Move your mouse over this icon to see the image

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

 

5. Repeat steps 3 and 4 for the oe8_drop.sql script.

0&&parent.frames.length) { d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);} if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<(a.length-2);i+=3) if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];} } //-->

Step 2 - Creating a Database Connection

Back to Topic List

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.

Choose View | Co nnection Navigator .

Move your mouse over this icon to see the image


2.

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

Move your mouse over this icon to see the image

 

3.

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

Move your mouse over this icon to see the image

 

4.

In the C onnection Name field type a name for the connection.

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

Move your mouse over this icon to see the image

 

5.

On the Authentication page:

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 .

Move your mouse over this icon to see the image

 

6.

On the Connection page:

In the H ost name field, type the name (or IP address) of the computer where the database is located.

In the JDBC Port and SID fields, enter the information for the connection to the database. If you do not know these values, check with your database administrator.

Click Next .

Move your mouse over this icon to see the image

 

7.

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

Move your mouse over this icon to see the image

 

8.

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

Move your mouse over this icon to see the image

 

9.

Create two connections of different names to use in JDeveloper samples and 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.

0&&parent.frames.length) { d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);} if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<(a.length-2);i+=3) if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];} } //-->

Step 3 - Examining the Sample Schemas

Back to Topic List

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

1.

Choose View | Co nnection Navigator .

Move your mouse over this icon to see the image


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.

Move your mouse over this icon to see the image

Summary

Back to Topic List

In this lesson you accomplished the following:

 Installed or gained access to the HR and OE sample schemas in an Oracle8i, Oracle9i, or Oracle 10g database
 Created a connection to a sample schema in JDeveloper
 Examined the sample schema objects in JDeveloper


 Move your mouse over this icon to hide all screenshots

Left Curve
Popular Downloads
Right Curve