Oracle Connection Information

We are now running Oracle 9.2.0.1, for Windows 2000 in the Transaction Lab. It is installed on RDB. The client tool is installed on every machine. The path to the executable is C:\OracleInstantClient. The JDBC drivers to connect to the Oracle Server are also installed in the corresponding client directories. There are namely 2 drivers classes12.jar and ojdbc14.jar available for connection to the database. If you 're using a version of jdk1.4.2 or later the driver ojdbc14.jar should be specified in the JDBC connect string.

• Connecting to the Oracle Server from Outside the Department
• Instructions for Using JDBC

Instructions for connecting to Oracle in from inside the Department
You will be given an Oracle Username/Password to connect to the database. Every account which is handed out is typically an empty schema. You will need to create database objects under your schema to be able to see something.

You will be able to access the database in two ways:

1. Oracle Instant Client 10g – Command Line Interface
Instant Client 10g is a command line interface to connect to the Oracle Server.
From Windows Start Menu -->Run---> cmd
This opens up the command window. At the prompt type:

1. sqlplus username@oraserv. (Please enter your assigned username and hit enter.)
2. This throws up the Password prompt. At the prompt type in your respective passwords and you are set to start working on your database schema.

How to change your default password from the command Prompt.
At SQL> Prompt type the following command once you are connected.
SQL>alter user <username> identified by <password>;

In the <username> placeholder type in your login name which you used to connect to the database and in the <password> placeholder type in the new password that you would like to set for your account. Terminate every command from the SQL> prompt with a semi-colon. Then hit the ENTER key and your new password is set.
A sample schema with its Entity-Relationship diagram has been provided for your convenience.

To access the HR schema from Instant Client at the command prompt follow the instructions above but simply replace the username and password with hr/hr.

Then issue the command at the SQL> prompt
select * from tab;
All the tables of the HR schema will be visible. This also ensures that the database is up and running.

The Human Resources (HR) Schema

In the company’s human resource records, each employee has a unique identification number, e-mail address, and job identification number, as well as a salary and a manager. Some employees earn commissions in addition to their salaries, and these commissions are also tracked. When an employee switches jobs, the company records the start date and end date of the former job, the job identification number, and department.

The company also tracks information about jobs within the organization. Each job has an identification number, job title, and a minimum and maximum salary. The sample company is regionally diverse, so it tracks the locations of not only its warehouses but also of its departments. Each of the company’s employees is assigned to a department. Each department is identified by a unique department code and a short name, and is associated with one location. Each location has a full address that includes the street address; postal code; city, state, or province; and country code.

For each country where it has facilities, the company records the country name, currency symbol, currency name, and geographical region.

2. iSQL*Plus User Interface (Client Tier) – Web Interface

The iSQL*Plus user interface runs in a Web browser.
Logging in to iSQL*Plus
To log into iSQL*Plus, perform the following:
1. Open your web browser and type in the following in your Web browser's Address or Location field.
http://rdb.translab.cs.sunysb.edu:7778/isqlplus

The browser opens a login screen as shown below:

2. Type in your Username and Password.
3. Enter ORASERV in the Connection Identifier text box.
4. Then hit the Login button.

5. The iSQL*Plus Work Screen is displayed.

To begin with since none of you will have any objects (as in tables) in your database schema use the following instructions to access a dummy schema in the database.
Repeat steps 1 to 4 as mentioned above:
Only in step 2 enter hr/hr for the Username and Password fields instead of your own. The iSQL*Plus Work Screen is displayed.

Loading Scripts

To load a SQL script file to run in iSQL*Plus, you find the URL and load the script or run the script from the Input area. To practice this task, perform the following steps:
Enter (or copy) the following commands into the Input area.
Click the Execute button. The results are displayed under the Input area.

set pagesize 200
describe employees
select first_name from employees where salary>1200;
select * from employees;

You can also set the output to appear in a separate window, or be saved to a file by configuring the Set Interface Options in the Preferences icon at the top of the window.
Click the Clear Screen button to clear the input and output areas.
To load your own custom scripts, click Browse and select your own scripts for example script1.sql from your working directory and click Open. Then click Load Script.

There are some demo scripts in HR’s schema available for your convenience and to test database connectivity. Since the scripts reside on the Web Server the other way to load and execute a script is to enter the URL in the Input area, and then click the Execute button:

Type the following URL in the Enter statements area:
@http://rdb.translab.cs.sunysb.edu:7778/iscript/script1.sql

Repeat the above URL just changing script1.sql to script2.sql, script3.sql and script4.sql to get an idea on how iSQL*Plus works.
Click the Clear Screen button to clear the Input and Output areas.

Setting your iSQL*Plus Preferences
There are a number of preferences that can be set for iSQL*Plus. To practice this task, perform the following steps:

• Click the Preferences icon at the top right of the window.
• Select the Change your password link.

This will change your own default user's password in the database. You will not make any changes at this time. Click OK.

Running Dynamic Reports

You can use iSQL*Plus to create dynamic reports. iSQL*Plus returns the results in HTML and terminates the iSQL*Plus session. In this module, you will run the following dynamic reports

1. A dynamic report with a simple query
2. A dynamic report with a simple query and passed parameters
3. A dynamic report with a query, passed parameters, and a login.

Running a Dynamic Report with a Simple Query
You will run a dynamic report that displays the results of a simple query. You must log in to iSQL*Plus to execute the query. This report is created with an HTML page that contains a link to iSQL*Plus and the name of the script to run.
The dynrep1.html HTML file contains the following:

<HTML>
<HEAD>
<TITLE>iSQL*Plus Dynamic Report</TITLE>
</HEAD>
<BODY>
<H1><i>i</i>SQL*Plus Report</H1>
<p><A HREF="http://rdb.translab.cs.sunysb.edu:7778>/isqlplus?script=
http://rdb.translab.cs.sunysb.edu:7778/iscript/script3.sql">
Run Employee Report</A>
</p>
</BODY>
</HTML>

The script3.sql SQL file contains the following:
SET PAGESIZE 200
SELECT *
FROM EMP_DETAILS_VIEW
ORDER BY LAST_NAME, EMPLOYEE_ID
/
To execute the dynamic report, perform the following steps:
Open your browser and specify:
1. http://rdb.translab.cs.sunysb.edu:7778/iscript/dynrep1.html as the URL.
Click the Run Employee Report link.

2. Log in to iSQL*Plus as hr/hr and oraserv as the connection identifier.

3. The results of the query are displayed, and the iSQL*Plus session is terminated (you cannot make any other queries).

Running a Dynamic Report with a Simple Query and Passed Parameters

You will run a dynamic report which displays the results of a simple query and passes variables to the script. This dynamic report is created with an HTML page that contains a window in which you enter the parameters that you want passed to iSQL*Plus and the name of the script to run.
The dynrep2.html HTML file contains the following:

<HTML>
<HEAD>
<TITLE>iSQL*Plus Dynamic Report</TITLE>
</HEAD>
<BODY>
<H1><i>i</i>SQL*Plus Report</H1>
<H2>Search for an Employee</H2>
<FORM METHOD=get ACTION="http://rdb.translab.cs.sunysb.edu:7778/isqlplus">
<p>
<INPUT TYPE="hidden" NAME="script" VALUE="http://rdb.translab.cs.sunysb.edu:7778/iscript/script4.sql">
Enter the employee's last name (or part of the last name):
<INPUT TYPE="text" NAME="last_name" SIZE="20" value="F">
<INPUT TYPE="submit" VALUE="Search">
</p>
</FORM>
</BODY>
</HTML>

The script4.sql SQL file contains the following:
SET VERIFY OFF
SET PAGESIZE 200
SET FEEDBACK OFF
SET MARKUP HTML ENTMAP OFF
PROMPT <H1>Employee Details for Employee(s) with Last Name like &last_name</H1>
SET MARKUP HTML ENTMAP ON
SELECT *
FROM EMPLOYEES
WHERE last_name LIKE ('&last_name%')
/
To execute the dynamic report, perform the following steps:
In your browser, specify:
1. http:// rdb.translab.cs.sunysb.edu:7778/iscript/dynrep2.html as the URL.
You can change the default value of ‘F’or just click the Search button.

2. Repeat step2 when you ran the first report.

3. The results of the query are displayed, and the iSQL*Plus session is terminated.

Running a Dynamic Report with a Simple Query, Passed Parameters, and a Login

You will run a dynamic report that displays the results of a simple query, passes variables to the script, and logs in automatically. This dynamic report is created with an HTML page that contains a window in which you enter the parameters that you want passed to iSQL*Plus, the name of the script to run, and login information.
The dynrep3.html HTML file contains the following:

<HTML>
<HEAD>
<TITLE>iSQL*Plus Dynamic Report</TITLE>
</HEAD>
<BODY bgcolor="#FFFFFF">
<H1><i>i</i>SQL*Plus Report</H1>
<H2>Search for an Employee</H2>
<FORM METHOD=get ACTION="http://rdb.translab.cs.sunysb.edu:7778/isqlplus">
<p>
<INPUT TYPE="hidden" NAME="userid" VALUE="hr/hr">
<INPUT TYPE="hidden" NAME="script"
VALUE="http:// rdb.translab.cs.sunysb.edu:7778/iscript/script5.sql">
Enter the employee's identification number:
<INPUT TYPE="text" NAME="eid" SIZE="10" value="105">
<INPUT TYPE="submit" VALUE="Search">
</p>
</FORM>
</BODY>
</HTML>

The script5.sql SQL file contains the following:
SET VERIFY OFF
SET PAGESIZE 200
SET MARKUP HTML ENTMAP OFF
PROMPT <H1>Employee Details for Employee Number &eid</H1>
SET MARKUP HTML ENTMAP ON
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID = &eid
/
To execute the dynamic report, perform the following steps:
In your browser, specify:
1. http:// rdb.translab.cs.sunysb.edu:7778/iscript/dynrep3.html as the URL.
You can change the default value of 105 or just click the Search button.


2. Repeat Step2 of the first script and see the report displayed.