Installing the Sample Schemas and Establishing a Database Connection
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.
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.
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:
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, 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.
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.
2.
Create a new workspace:
Right-click the Application node in the Application Navigator and
select New Application Workspace from the context menu.
3.
Name the application workspace Samples, then click OK.
4.
With Project selected in the Application Navigator, click Open,
or choose File | Open from the menu.
In the Open dialog, browse to change the Location to the directory
where you extracted the sample schema scripts. Select the mksample8.sql
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.
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.
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.
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.
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.
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.
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.sql
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.
3.
In the Application Navigator, right-click the hr8_drop.sql
file select Run in SQL*Plus, then select the database connection
to use. This opens a SQL*Plus window.
4.
When prompted, enter the password for the hr8
user concatenated with the database name, separated by @;
for example, hr8@mydb.
When the script finishes running, type exit to close the SQL*Plus
window.
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 | 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.
In the Connection Name field type a name for the connection.
The example uses the name
tutorial_jdbc_connection, but you can use any name. Click Next.
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.
6.
On the Connection page:
In the Host 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.
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.
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 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.
Once you have created a connection to one of the sample schemas,
you can examine the schemas from JDeveloper.
1.
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.
Bookmark
