Abhijeet Maharana

I needed a basic database on my Ubuntu Feisty system and decided to try out Oracle 10g Express Edition. The installation is pretty straightforward. Below is a log of what I did to get it running along with Java code to access the database.

My Setup:

• AMD 3600+ 64 bit running Ubuntu 7.04 32 bit
  (2.6.20-15-generic kernel)
• 2GB RAM

Download the .deb file for Oracle Database 10g Express Edition for Linux x86. I have oracle-xe_10.2.0.1_i386.deb which is a bit old now. The official installation guide is located here.

The installation steps are as follows:

• Step1: Requirements and installation
  In short: 1.5 GB of disk space, 512 MB of RAM with 1GB swap. The Express Edition can use a maximum of 1GB of RAM even if more is available. The minimum and recommended requirements for installation can be found here . The installation went fine on my machine with 2GB of RAM and no swap space.

  Run the .deb. You may need to be connected to the Internet for the dependencies. The installation creates shortcuts under the Applications menu. However, we don’t need to use any at the moment.

  screenshot

• Step2: Configuring the database
  Run

  sudo /etc/init.d/oracle-xe configure

  Use the default settings for Oracle Application Express (8080) and the database listener (1521) or change the ports if you have other servers using these ports.

  Enter the SYS/SYSTEM password. I used ‘manager’.

  Select ‘n’ for the option for starting the database at boot time since we can conveniently do the same using the created menu items. [See the troubleshooting section below]

  screenshot

  The user starting or stopping the database must be a member of the ‘dba’ group. This can be done from [Gnome Menu] >> System >> Administration >> Users and groups >> Manage groups. Select ‘dba’ and click on properties. Select user(s) to add them to this group.

• Step3: Administration using the web interface
  Log into Application Express by hitting the URL http://127.0.0.1:8080/apex
  Enter SYSTEM / [password you had entered earlier]. There is a built-in user account HR which needs to be unlocked before it can be used. But, we will create a new account for our use.
  Goto Administration >> Database users >> Create user
  Enter ’scott’ / ‘tiger’ for the username / password and click Create.

  screenshot 1 | screenshot 2

  Log out and log in again as scott.

• Step4: Create test tables
  Create a table
  • using the object browser
    Object browser >> create >> table

    screenshot 1 | screenshot 2

  • using a SQL Query
    SQl >> SQL Commands >> Enter Command
    You can enter multiple statements in the editor and execute one statement at a time by selecting a statement and hitting CTRL + ENTER.
    CTRL + ENTER works out of the box on Firefox 2.0.0.3. On Opera 9.24, you may need to change some settings. At this point I can’t say what.

    screenshot 1 | screenshot 2 | screenshot 3

• Step5: Access database from Java

  Save the following code as OracleJdbcDemo.java. It prints values from the first column.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

  import java.sql.*;   public class OracleJdbcDemo { public OracleJdbcDemo() throws Exception { Class.forName("oracle.jdbc.OracleDriver"); String connString = "jdbc:oracle:thin:@localhost:1521:XE"; Connection conn = DriverManager.getConnection(connString, "scott", "tiger"); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from student"); while(rs.next()) { System.out.println(rs.getString(1)); } }   public static void main(String args[]) throws Exception { new OracleJdbcDemo(); } }

  Compile and run the code by supplying the ojdbc14.jar file in the classpath:

  $ javac OracleJdbcDemo.java
  $ java -cp "/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/jdbc/lib/ojdbc14.jar":. OracleJdbcDemo

  If you have downloaded and installed a JDK yourself, make sure the compiler and interpreter are both from the same installation.
  I have JDK1.6.0 installed on my system and this is the entry in my /home/abhijeet/.bashrc

  export JAVA_HOME="/apps/java/jdk1.6.0"
  export PATH=${JAVA_HOME}|>/bin:${PATH}|>

Troubleshooting

I came across a situation where just after the installation everything worked fine but after a reboot, neither http://localhost:8080/apex nor the Java program worked. However, the database was starting and stopping from the menu options as I was able to connect using the “Run SQL Command Line” utility found in the menu. First few lines of the exception thrown by the program are shown below:

Exception in thread "main" java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
...

I had answered “no” to start the database at boot time since I didn’t want it running when I am not using it. This may or may not be related to the problem above and I did not reinstall selecting “yes” to check.

After searching a little, this is what worked.
Register environment variables and start TNSLISTENER:

$ . /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
$ sudo /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/tnslsnr

If you get the following error after the first command

/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh: 114: [[: not found
/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh: 114: [[: not found

then edit /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/nls_lang.sh and change the first line from

#!/bin/sh

to

#!/bin/bash

Wait for sometime after starting the listener and both the web interface and Java program should start working.

[Reference: Oracle forums]