Installing the Sample Schemas and Establishing a Database Connection

Purpose

Several collateral items require the Oracle sample database schemas. In particular, they are needed by some of the other JDeveloper OBE tutorials. This tutorial describes how to install and populate the schema tables and how to connect to them in JDeveloper 11g.

Time to Complete

Approximately 30 minutes.

Topics

This tutorial covers the following topics:

Overview
Prerequisites
Creating and Populating the Schema Tables

Creating a Database Connection

Creating and Managing Connections with the Resource Palette
Examining the Schemas
Summary
Related information

Viewing Screenshots

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

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

Some JDeveloper OBEs and samples use the HR and OE sample schemas. These schemas are included with the Oracle 10g or Oracle 11g database. Other tutorials use the FOD schema. The scripts to install the FOD schema are included with this OBE.

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 various schemas in JDeveloper.

Back to Topic List

Prerequisites

Before starting this tutorial, you should:

1.

Install Oracle JDeveloper Release 11g.
2.

Have access to an Oracle 10g or Oracle 11g 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 Schema Tables

Warning: For security reasons, it may not be advisable to install the 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 schemas for tutorial and demo purposes, you may drop them by issuing the following SQL*Plus command for each installed 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 Oracle 10g or 11g database. This topic discusses the following subtopic:

Installing the Oracle 10g or Oracle 11g Sample Schemas
Installing the FOD Schema

 

Installing the Oracle10g or Oracle 11g Sample Schemas

1.

If you have dropped the sample schemas from Oracle 10g or Oracle 11g, or if you did not install them in the first place, you should install them following the instructions in Oracle Database Sample Schemas 10g Release 1 (10.1), Oracle Database Sample Schemas 10g Release 2 (10.2) or Oracle Database Sample Schemas 11g Release 1 (11.1). The SQL scripts are on a separate companion CD and are also available for manual installation by right clicking here.

 

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 Oracle 10g or Oracle 11g.

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 Database 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

Back to Topic List

Installing the FOD Schema

Some JDeveloper collaterals require the Fusion Order Demo (FOD) schema to exist in the database. To install the schema, perform the following steps:

1.

Download and extract the FOD_11.zip file to your hard drive.

 

2.

In JDeveloper choose File-->Open, and browse to the location where you extracted the FOD_11.zip file. Select Infrastructure.jws in the Infrastructure directory, and click Open.

 

3.

In the Application Navigator, expand the MasterBuildScript project and the Resources node. Double-click build.properties to open it in the editor.

 

4.

Modify the following properties for your environment.

jdeveloper.home The root directory where you have JDeveloper 11g installed. For example, C:/JDeveloper_11/jdeveloper/
jdbc.urlBase The base jdbc url for your database in the format jdbc:oracle:thin:@<yourhostname>.
For example, jdbc:oracle:thin:@localhost
jdbc.port The port for your database. For example, 1521
jdbc.sid The SID of your database. For example, ORCL
db.adminUser The administrative user for your database. For example, system
db.demoUser.tablespace The tablespace name where the FOD user will be installed. For example, USERS

Leave all other properties at their defaults. The demo user must be named FOD.

 

5.

In the Application Navigator, right-click the build.xml file and choose Run Ant Target-->buildAll.

Type the system password when prompted for it.

Click Continue.

This creates the FOD user and populates the tables in the FOD schema. In the Apache Ant - Log, you will see a series of sql scripts run and finally:

buildAll:
BUILD SUCCESSFUL
Total time: 1 minute 52 seconds

Back to Topic

Back to Topic List

Creating a Database Connection

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

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

 
2.

In the Database Navigator, right-click the Connections node and choose New Connection from the context menu.


3.

In the top half of the Create Database Connection dialog, enter the following connection information:

Connection Name: You can use any name for the connection. This example uses the name FODConn
Connection Type: Oracle (JDBC)
Username:

f you are using the sample schema scripts on an Oracle10g or 11g database, use the HR user for the Human Resources schema or the OE user for the Order Entry schema.

If you are using the FOD schema installed following the instructions in Installing the FOD Schema, use FOD as the Username.
Password:

Use HR as the password for the Human Resources schema or OE for the Order Entry schema.

For the FOD schema use Fusion as the password.

 
4.

In the Oracle (JDBC) Settings box enter the following values:

Driver: thin
Host Name: localhost
JDBC Port: 1521
SID: ORCL

If you do not know these values, check with your database administrator.

 
5.

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.

 
6.

Click OK. The connection now appears below the Connections node in the Database Navigator.

 
7.

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

A connection named FODConn to the FOD schema.

A connection named HRConn to the HR schema

A connection named OEConn to the OE schema

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

Back to Topic List

 

Creating and Managing Connections with the Resource Palette

You can also create database connections using the Resource Palette. You use the Resource Palette to manage your connections and other project resources.

1.

The Resource Palette is used to manage connections and resource catalogs. A resource catalog contains applications components and services such as user interface elements, activity guides, business objects, services, processes, rules and actions. The first time you open JDeveloper the Resource Palette is not visible. When you create your first application, the Resource Palette is automatically displayed in the upper right corner of JDeveloper.

The Resource Palette contains an IDE Connections accordion. Clicking the arrow icon to the left of the name expands the node, and you can see the database connections that you created in the previous topic.

 

2.

You can also create database connections in the Resource Palette.

Right-click the Database node under IDE Connections in the Resource Palette, and choose New Database Connection from the context menu.

This invokes the Create Database Connection dialog that you used in the previous topic.
3.

Once you have created database connections you need to make them available for use by the application you are working with.

The Application Resources panel in the Application Navigator displays a list of the resources available to the currently selected application. Click the arrow icon to the left of the Application Resources panel in the Application Navigator. You see that there is a Connections node, but that it is currently empty for the Infrastructure application. If the Infrastructure application needs to use any of the connections you just created, you have to explicitly add them to its resources.
4.

In the IDE Connections | Database list in the Resource Palette, select the connection that you want to make available to the application, and drag and drop it onto the Connections node Application Resources panel.

The database connection is added to the Connection list in the Application Resources panel, and is now available for use by the application.

 

5.

You can also create database connections in the Application Resources panel. The connections you create here are specific to this application, and will not be generally available in the Resource Palette.

To create a connection here, right-click the Database node and choose New from the context menu.

This invokes the Create Database Connection dialog that you used in an earlier topic.

Back to Topic List

Examining the Schemas

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

1.

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

 

2.

Expand IDE Connections then the node for the connection you have just made, for the HR, OE or FOD schema. You can browse the database elements for this schema and use them in the JDeveloper OBEs. This example shows you the list of tables in the HR schema, and the columns in the EMPLOYEES table.
3.

Clicking the Data tab at the base of the editor window shows you the data in the EMPLOYEES table.

You've now, successfully completed this tutorial.

 

Back to Topic List

Summary

In this lesson, you've learned how to:

Install or gain access to the HR and OE sample schemas in an Oracle10g, or Oracle 11g database.
Install the FOD schema.
Create a connection to a schema in JDeveloper
Use the connection to examine the schema objects in JDeveloper

Back to Topic List

Related Information

Oracle JDeveloper 11g Tutorials

Back to Topic List

Place the cursor over this icon to hide all screenshots.