Graduate Windows Lab

Oracle Connection Information

We are now running Oracle 11g (version 11.1.0.7) server on Windows 2008, 64 bit virtual machine. The Oracle Client 11g is installed on every Translab and Gradlab machine. 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.

Basic connection information to connect to an Oracle database is as follows:

• Database username (schema): <your NetID>
• Database password: <Your 9-digit Stony Brook ID prefixed with keyword 'orcl' e.g orcl324096789 >
• Database service name (SID): STU
• Hostname: vmoracle.cs.stonybrook.edu
• Listener Port: 1522

1. Sample code to test the connection

Following are the steps.

Download the Oracle JDBC driver in your computer from here .File Name: ojdbc14.jar

Create a new project, say testOracle, in Eclipse.

Right click Project -> Properties -> Java Build Path -> Libraries -> Add External Jar

Select the downloaded Oracle JDBC Driver.

Create a new java file, say testOracle and copy paste the code below.

import java.sql.*;
public class testOracle{

public static void main(String [] args) {
Connection con = null;
try {
// Load the Oracle JDBC driver

Class.forName("oracle.jdbc.OracleDriver") ;
System.out.println("Oracle JDBC driver loaded ok.");

// Establish the connection

con = DriverManager.getConnection("jdbc:oracle:thin:username/password@hostname:port:SID");
System.out.println("Connected");
con.close();
}catch(Exception e){
System.out.print(e);
}
}

}



2. How to connect using SQL*Plus Command Line Interface?

In Windows select Start → Programs → Oracle (it will be some extended folder name) → Application Development → SQL Plus
This opens up the command window. At the SQL> prompt type:

SQL>connect <your user name>@stu [Hit Enter]
Enter password: <Enter your 9-digit SBID> [Hit Enter]


3. How to connect using SQL*Developer GUI Interface?

In Windows select Start → Programs → Oracle (it will be some extended folder name) → Application Development → SQL Developer
This opens up the GUI Interface. Right Click on 'Connections' to create a new connection:

Give any name for the connection that you can remember.
Username: <You net ID>
password: <Enter your 9-digit SBID>
Hostname: vmoracle.cs.stonybrook.edu
SID: stu
Test the crredentials and then connect.

Visit Online Demonstrations and tutorials for using using SQL Developer.

4. More information on using JDBC

Connecting to the Oracle Database Server using JDBC from Home

Since a Firewall sits between the Oracle Database Server and the Oracle Client the JDBC connect string has to account for that. The steps below walk a user on using Oracle jdbc drivers to connect to the database.

1. Whether you use TYPE-II JDBC-OCI driver (requires the Oracle-9i Rel2 thick client) or TYPE-IV JDBC-THIN driver, a copy of the oracle client is necessary when connecting from home.
   
2. A copy of the Oracle client (Thick client) can be downloaded from the oracle website.
3. If JDBC thin client is used to connect to the database then OS specific Oracle InstantClient can be downloaded from the following location:
   http://www.oracle.com/technology/tech/oci/instantclient/instantclient.html
   
4. To download the client software from the above links may require users to do a free registration on Oracle technet.
   
5. Copies of sample programs which have connection information to the database using both JDBC-THIN and JDBC-OCI drivers can be obtained by downloading the file JDBC.zip. A checklist of common CLASSPATH issues that need to be addressed is also available in the zip.
   JDBC.zip

NOTE: When connecting to the database from inside the department Translab or Gradlab, only Oracle Instant Client is available on the machines which is JDBC-THIN compliant. Running programs which use JDBC-OCI to establish a connection to the oracle server will thus fail to make a connection from the GRADLABS because of incompatibilty.


5. How to connect to the Oracle database from Outside the Department?

• Use SSH (Secure Shell Client) to connect to one of the Unix machines with your network login names and passwords
• If you do not have a copy of SSH, download SSH Client 3.x from the downloads area of our Services via Secure Sign On and read the instructions at https://www.cs.sunysb.edu/faq.php to connect using SSH.
• Successful login will take you to your home directory.
• At the Unix Prompt type the following: source /usr/local/pkg/instantclient/setup.csh; The script sets up the Oracle client environment and you are ready to connect to the database server.
• At the prompt now type 'sqlplus username@stu'
• Enter the password when prompted, you will be connected to the SQL*Plus command line interface.

 

For any concerns, questions or issues please contact us.