Rooms Information - Java Stored Procedure Sample

Calling Program

1.Oracle9i JDeveloper (Note: JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here )
or
JDK 1.2 or higher (can be downloaded from here)

2. Oracle9i Client complete installation (can be downloaded from here)

Host

1. Oracle9i Database or higher running SQL*Net TCP/IP listener (can be downloaded from here )

2. Travel Schema needs to be loaded into the database. (can be downloaded from here)

1.

Ensure that loadjava.exe available at <oracle_home>\bin is available in the system path.<oracle_home> refers to the directory where Oracle9i client is installed.
On Command Prompt, type the following command

% loadjava -user travel/travel@HostName:Port:SID -verbose -force -resolve -thin <your_directory_structure>\RoomsInformationSample\src\oracle\otnsamples\jsp\
roomsinformationsample\RoomsInformationProcedure.java
Where

2.

Connect to your database using the command
% sqlplus travel/travel@<DatabaseConnectString>
where <DatabaseConnectString> is your TNSName

3.

Run the script RoomsInformationSample\config\DatabaseSetup.sql at the  SQL prompt
@<your_directory_structure>\RoomsInformationSample\config\DatabaseSetup.sql
This creates a database stored procedure GET_ROOM_DETAILS.

4.

Edit Connection.properites file. Change the hostname, SID, and portnumber to that of the database instance you want to connect to.

For eg.

5.

Install Oracle JDBC Driver and include the classes12.zip file in the CLASSPATH. Also ensure <your_directory_structure>\RoomsInformationSample\src and <your_directory_structure>\RoomsInformationSample\config are set in the CLASSPATH.

6.

On command prompt, navigate to <your_directory_structure>\RoomsInformationSample.Compile all the source files used in this sample application using:
%javac src\oracle\otnsamples\jsp\roomsinformationsample\*.java

7.

Run the 'RoomsInformationSample' class file using java:
%java oracle.otnsamples.jsp.roomsinformationsample.RoomsInformationSample

1.

Using Oracle9i JDeveloper, File/Open option select the RoomsInformationSample.jws from the RoomsInformationSample directory.

2.

Edit Connection.properties file. Change the hostname, SID, and portnumber to those of the database instance you want
to connect to.

For eg.

3.

To create the deployment profile, click File-> New-> Deployment Profiles -> LoadJava and Java Stored Procedure.
Save the Deployment Profile file with the default name given for the deployment file.

4.

In the 'LoadJava and Java Stored Procedure Deployment Profile Settings', deselect all the files except 'RoomsInformationProcedure.java' as shown in the figure. 1.1. On clicking 'OK' the deployment profile would appear in the Oracle9i JDeveloper Navigator window.

Figure 1.1 ScreenShot Displaying the LoadJava and JavaStored Procedure Deployment Screen

5.

Right click on the Deployment Profile created, select 'Add Stored Procedure' for publishing the Stored Procedures. Choose 'getRoomDetails' method from the displayed list.

6.

Click 'Settings' button. Give 'GET_ROOM_DETAILS' as the name by which it will be published. Also in the PL/SQL parameter settings change the mode as 'OUT' for 'P3', 'P4' parameters which are of type 'Array' as shown in figure 1.2. Click OK till all windows are closed.

Figure 1.2 Screenshot for method setting while publishing

7.

To deploy the 'Get_Room_Details' stored procedure to the database,
i) Right click on the Deployment Profile,
ii) Select the 'Deploy To' option choose the database connection to which you want to deploy the Stored Procedures if database connection already exists
iii) Else create a database connection by right clicking on the Deployment Profile->Deploy to ->New Connection.
iv) Enter connection name, Connection type as Oracle JDBC, click 'Next' enter Username and Password of the user in which Travel schema is installed.click 'Next', Select 'Driver as 'Thin',enter Hostname, JDBC Port and SID .Click 'Finish'. This creates a database connection and also completes the deployment.

8.

To run the application, select Run/Run RoomsInformationSample.jpr from Oracle9i JDeveloper main menu.

Step 1.

The user invokes the application, and the application displays the connection status or any errors in the status bar.

Step 2.

If the connection was successful, the application will  display all hotels as shown in figure 1.3

Figure 1.3 ScreenShot Displaying List of Hotels

Step 3.

Then the  user has to select a hotel and  click  'Room Enquiry' button to get the room details. The room details are displayed as shown in figure 1.4

Figure 1.4 Screenshot Displaying Room Information

Step 4.

 When the user click the "EXIT" button, the application exits.