Accessing Oracle® Database XE by using the Easysoft ODBC-Oracle Driver

Contents

Introduction

The Easysoft ODBC-Oracle Driver lets you access Oracle® Database 10g Express Edition (Oracle Database XE) from ODBC-aware applications running on Linux, Unix and Windows. For example, access Oracle® Database XE from a PHP script running on Linux.

As Oracle’s new XE database has now been released, we thought it would be a good idea to publish an article to show how our driver works with it. This article describes, from start to finish, how to install, configure and test the Easysoft ODBC-Oracle Driver with Oracle® Database XE. Currently, we have tested our driver both on Linux and Windows against Oracle’s 10.2 builds.

Pre-requisites

  1. Two networked machines visible to each other. One Microsoft Windows machine and one Linux machine. (Specific Operating System support can be found on Oracle’s web site.)
  2. Oracle® Database XE for Microsoft Windows. This is available for download from Oracle®. The distribution is approximately 150 MB.
  3. Oracle® Client software (10.2). There are 2 different downloads to choose from. Either you can download and install Oracle’s full client libraries (650 MB) or you can use the Instant Client which is much smaller (35 MB).
  4. Easysoft ODBC-Oracle Driver. You can download a free 14 day trial version of the software from Easysoft. Please select the platform for which you have chosen to install the Oracle® client software.

Assumptions

This tutorial was written assuming the Oracle® Database XE install was on Microsoft Windows (NT, 2000, XP) and the client used was the Instant Client (10.2) on Linux. We used the Easysoft ODBC-Oracle Driver build 2.2.5 for Linux-x86. You also need root permissions for the Easysoft install.

Please be aware that the latest build from Oracle® (10.2) needs a minimum of glibc 2.3.3. If you are unsure about this then run the following command. (libc.so.6 may vary in its location depending on the flavour of Linux you are using.)

/usr/lib/libc.so.6

This will show you the version of glibc you are running. If you haven’t got the required version then the best thing for you to do is to download the 10.1 client instead.

GNU C Library stable release version 2.3.3, by Roland McGrath et al.
Copyright (C) 2004 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE.
Compiled by GNU CC version 3.3.3 20040412 (Red Hat Linux 3.3.3-7).
Compiled on a Linux 2.4.20 system on 2004-05-11.
Available extensions:
        GNU libio by Per Bothner
        crypt add-on version 2.1 by Michael Glad and others
        linuxthreads-0.10 by Xavier Leroy
        The C stubs add-on version 2.1.2.
        BIND-8.2.3-T5B
        NIS(YP)/NIS+ NSS modules 0.19 by Thorsten Kukuk
        Glibc-2.0 compatibility add-on by Cristian Gafton
        GNU Libidn by Simon Josefsson
        libthread_db work sponsored by Alpha Processor Inc
Thread-local storage support included.
Report bugs using the `glibcbug' script to 'bugs@gnu.org'.

Note that the line you need to look at is at the top of the output which reads:

GNU C Library stable release version 2.3.3

We have also assumed that you will use the unixODBC driver manager included with the Easysoft ODBC-Oracle Driver (build 2.2.7).

Installing Oracle® Database XE on Windows

After you have downloaded the Windows distribution from Oracle®, you will have an executable named OracleXE.exe.

Run the executable, create a password for the user ‘system’ and accept the defaults through the install. Your database is now installed.

You now have to make sure your database has started correctly.

To do this, choose Start> Programs> Oracle® Database 10g Express Edition> Start Database.

You should now see the following:

Output showing that Oracle XE has already been started successfully

From this you can see that the database was already running, so you are now ready to connect to your database by using the SQL tool.

To run the SQL command line tool, choose Start> Programs> Oracle® Database 10g Express Edition>Run SQL Command Line.

You should now be presented with an SQL prompt, from here, type connect. It will then ask you for a username and password. The username is system and the password is the one you created during the install.

Oracle XE SQL tool showing the results of a query following a successful connection

You can now try a simple query:

select * from dual;

We now know that the database is fully operational and we can retrieve data.

Because you weren’t given the option of setting up a SID (Service ID), Oracle® have created ‘XE’ as an example name. This can be seen more clearly in your newly created tnsnames.ora file which can be found in C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN. It should look something like this:

XE =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XE)
  )
)

The values of SERVICE_NAME, HOST and PORT are used later in configuring a DSN on your client (Linux) machine, so it may be worth making a note of these values now.

Installing Oracle® Instant Client on Linux

You can also install Oracle's SQL*Plus utility which will enable you to connect to your database. This is very useful to prove your database is correctly configured before installing the Easysoft ODBC-Oracle Driver.

After you have downloaded the Linux distribution from Oracle®, you will have a zip file named instantclient-basic-linux32-10.2.0.1-20050713.zip

You need to move the zip file into a suitable directory. From here you can unzip the file:

cd /usr/lib      
unzip instantclient-basic-linux32-10.2.0.1-20050713.zip

(You may need to log in as root to do this in /usr/lib. Exit the root user account after you have unzipped the file.)

This will create a directory in /usr/lib called instantclient_10_2. This is all you need to do for the client install!

If you have chosen not to install SQL*Plus then you can skip this section.

Installing SQL*Plus on Linux

Once you have downloaded instantclient-sqlplus-linux32-10.2.0.2-20060331.zip from Oracle® you can unzip the file into /usr/lib.

In order to use SQL*Plus you will first have to set your LD_LIBRARY_PATH to point to /usr/lib/instantclient_10_2:

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/instantclient_10_2
export LD_LIBRARY_PATH

In /usr/lib/instantclient_10_2 type the following command:

./sqlplus username/password@//server_name:1521/database_name

You should be now presented with an SQL prompt where you can execute sql commands to your database:

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Jul 18 15:21:53 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>select * from dual;

At this point you now know that your database is set up correctly and you can retrieve data from it.

Installing the Easysoft ODBC-Oracle Driver

Download the Easysoft ODBC-Oracle Driver

After you have downloaded the ODBC driver from Easysoft, place it somewhere on your Linux machine to be untarred e.g. /tmp.

If this is not the first time you have installed the Easysoft ODBC-Oracle Driver on this machine, you will need to rename your existing easysoft tree in /usr/local.

# mv /usr/local/easysoft /usr/local/easysoft_backup

Now untar the distribution.

tar -xvf odbc-oracle-2.2.5-linux-x86-glibc.tar

This will create a directory named odbc-oracle-2.2.5-linux-x86-glibc in your current working directory.

Change into the directory and as the root user, run the install.

# cd odbc-oracle-2.2.5-linux-x86-glibc
# ./install

After you have accepted the License Agreement, accept the defaults throughout the install.

You will then be asked if you would like to license the ODBC driver. Answer ‘y’. You will now be taken into the License Manager. Fill in the requested details. You will now be asked how you would like to request your license.

How would you like to obtain the license?
[1] Automatically by contacting the Easysoft License Daemon
[2] Write information to file so you can fax, telephone it
[3] Cancel this operation 
  1. To request the license automatically, you will need access to the Easysoft License Daemon which runs on port 8884. Alternatively you can visit the licensing page where you can generate your trial license.
  2. If you don’t have access to port 8884, you can send a text file to request the license key. Selecting this option will create a file /usr/local/easysoft/license/license_request.txt.

After you have been through the licensing procedure, the next step is to set up a data source (DSN) to connect to your newly created Oracle® database.

The following section will create an Oracle data source based
on the information entered at the following prompt.
      
The created data source will be called 'ORACLE';.
      
Enter the Oracle Database Name (i.e. test.server): XE
Enter an Oracle user name (i.e. system): system
Enter the Oracle password for system: your_password

Here you can see what you need to set the attributes to in order to configure your DSN correctly.

After you have followed these steps, you can now accept the default answers through the rest of the install. After the install completes, exit out of the root user account.

Now that the installation is finished, you can now have a look at your DSN setup just to make sure everything is correct. This can be found in /etc/odbc.ini and it should look like this.

[ORACLE]
Driver          = ORACLE
Database                = //your_server:1521/XE
User            = system
Password                = your_password
METADATA_ID             = 0
ENABLE_USER_CATALOG             = 1
ENABLE_SYNONYMS         = 1

You now need to set your LD_LIBRARY_PATH to point to the Oracle® client:

# LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/instantclient_10_2
# export LD_LIBRARY_PATH

Now we are ready to make a connection to the database. Run the following command.

# /usr/local/easysoft/unixODBC/bin/isql -v ORACLE

This should now take you to an SQL prompt where you can execute a simple query:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>select * from dual;
+------+
| DUMMY|
+------+
| X    |
+------+
1 rows returned
SQL>

Article Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.