Oracle 10g Thin JDBC Example

Connecting to Oracle using the JDBC driver

Below is a guided example to connecting to an Oracle 10g database using the Oracle Thin JDBC driver.

Scenario

Database version: Oracle 10g version 10.1.0.2.0
JDBC driver: 'Thin' JDBC driver for Java 1.4.
Database server host name: localhost
Database server port: 1521
Database instance SID: “orcl”
Table or View name in database: TEST

Other versions of Oracle

Unless you're using a really old version of Oracle this approach should work. Alternatively, see the Oracle 8.1.7 page.

JDBC driver

You will need the Oracle JDBC driver. This is a file called “ojdbc14.jar” and is normally installed by default alongside the database server/tools. Alternatively it can be found in the download archive or installation CD, or on the Oracle website. This is a pure Java driver for connecting to local or remote Oracle databases.

Steps

Choose File > Connect to database and the Database Connection dialogue will appear:

1. Connection type

• Select JDBC data source
• Click Next

2. JDBC Details

• Driver jar path: enter the path to and including classes12.zip. For example:
  D:\Oracle\product\10.1.0\Db_1\jdbc\lib\ojdbc14.jar
• Driver class:
  oracle.jdbc.OracleDriver
• JDBC URL: of the format “jdbc:oracle:thin:@hostname:port:sid”. For example:
  jdbc:oracle:thin:@localhost:1521:orcl
• Click Next

3. Authentication

• Tick Authenticated, and enter the user name and password to access this database.
• Click Next
• If you can’t get to the next page, there’s a problem with steps 2 or 3. Go back and check your settings.

4. Choose table

• Select the table or view from the list that you want to query. For example:
  NEW_CUSTOMERS
• Click Next

5. Customise SQL

• Unless you want to perform an SQL join (for example) on the fly, you can leave this unmodified and click Next.
  Note: If you enter custom SQL, the Oracle JDBC driver does not support functions; if you need to use functions, create a View using the desired functions in your database, then connect to that View as if it were a Table.

6. Columns

On this page you can change the names of columns as they appear inside the Omniscope. This allows you to change unwieldy database column names. For example, you might want to change “CUST_NAME” to “Customer Name”. But you don’t have to do anything on this page.

• Click Next

7. Specify a title

• If desired, you can name this dataset.
• Click Finish

Exploring your data

Once the Omniscope has finished opening the data, you’ll see a Table, Chart and Pie View, and a set of devices on the right. You can change to different views (visualizations such as Graph and Map) by clicking “Table view”. You can filter data by dragging sliders and clicking checkboxes on the right. The green/red barometer at the top will show you records that have been included or excluded. The Reset button resets all filters to show all records.

Save your settings

It is recommended that you save an IOK file (from the File menu) to save going through the database dialogue again for this particular database table. In future, you can open this IOK file, and optionally refresh data from the database (picking up any updated records).