Tuesday, January 16, 2007

XE: ODBC connection to a remote XE server

How do you set up an ODBC connection to a XE instance on a remote server?

The basic steps are:
* Download and install the XE client on your Windows machine
* Create an ODBC DSN entry
* Create a new MS Access MDB and link two tables from XE (using the sample user HR)

First, you download the XE Client software from the Oracle Technet: http://www.oracle.com/technology/software/products/database/xe/index.html

Double-click on the downloaded file to install it:


During the installation choose to install the client software into a directory of your choice and finish the installation.



After that you can configure the ODBC entry. Goto Start > Control Panel > Administration > ODBC DatasourcesOn the tab System-DSN add another data source:


Choose Oracle in XEClient:


Enter the details for the connection to the remote machine, in my case the remote machine is daust3.opal-consulting.de

Data Source Name: choose an arbitrary name
Description: choose an arbitrary description
TNS Service Name: host:port/SID
User ID: Oracle user to connect to

The service name is critical here, the hostname is the name of the remote machine but it can also be an ip-address. The port is the port on which the Oracle listener is operating, usually 1521. The SID is fixed, it is called XE.

Then test the connection by entering the password for the user HR. The result has to be "Connection successful".


After that start MS Access (in my case I used MS Access 2000) and create a link to the tables stored in XE. You can do this either by clicking on New > Link table


or by right clicking in the table pane and selecting Link Tables ...


then choose ODBC Databases from the bottom of the list:


Choose the computer data source XE, which we just configured:


then enter the password for the user HR. You can also just overwrite the User Name with a different user you want to connect to:


then select the relevant tables by holding down the control-key and click on the table names. Then hit OK.


Voila! The tables are linked and you can start using them:


Just be aware of firewalls!!! If you cannot connect to the remote machine the reason can be that port 1521 is blocked by your firewall!

Update: 18.01.2007

Storing the password in the DSN entry

You can also store the password together with the username in the DSN entry. This way the login prompt won't show when you link tables in MS Access. This might be useful in certain situations.
Just use the username/password in the User ID field when configuring the DSN entry.
For example you want to connect as the oracle user HR with password hrpwd. Then enter HR/hrpwd as the User ID instead of HR.

Thanks to Jer for this tip ( post in the XE forum ) .


Update: 18.01.2007

Using tnsnames.ora with XE client

In the DSN entry you can specify the connection information to the XE server instance using the easy connect syntax, just as shown above.

But you can also use the traditional resolution via the tnsnames.ora file. This adds another layer of abstraction so that you can access the tnsnames entry in you VBA code and not hardcode the server, port and SID information into your code.

To accomplish this, the following steps are necessary:

First, you have to add the variable TNS_ADMIN to your Oracle configuration in the registry.
Start regedit and go to HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XEClient.



Then add a new string called TNS_ADMIN. Set the value to the directory where you want to store the tnsnames.ora file (you can also reference a directory of another Oracle install where a valid file is located).




Then create (or copy) the file tnsnames.ora and enter the following content:

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DAUST3.opal-consulting.de)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

Then you can reconfigure your DSN entry and replace daust3.opal-consulting.de:1521/XE with XE.


Regards,
~Dietmar.

12 comments:

Anonymous said...

use plsql developer instead

Unknown said...

Unfortunately, not all of us live in a *pure* Oracle world.

Many of us have to be able to interface with other technologies. This is one possible way to do it. But there are others, too. It always depends on your use case.

Regards,
~Dietmar.

Anonymous said...

Even though we not all live in a "pure" world, I would personally prefer the use of Heterogeneous services (which is a part of XE).

Simplified, you can create a database link to an odbc connection, and thus

select * from my_table@my_odbc_source;

Just my 25 cents :)

Unknown said...

Hi Kim,

yes. I am absolute fan of the heterogenous services. You can even create a database link to an MS Excel Sheet. Very cool.

But nevertheless, sometimes you need both directions. For example, right I am working on migrating an MS Access application to Apex. First we fixed the table names with a macro in Access (table and column names max. 30 characters, with no special symbols and spaces).

Then we used the database link to Access to migrate the stuff to Oracle. Then we need the link from Access to Oracle in order to keep the MS access application running. Now we can slowly move parts of the application to Apex.

Regards,
~Dietmar.

Anonymous said...

Good design!

Anonymous said...

Hi,
I also have to migrate my access application to working with Oracle XE (instead of .mdb)

I migrate all the tables to XE and
linked them within the .mdb "program" file

(for "program" I mean where the forms, reports and queries are saved).

Then if I try the program (with the linked oracle tables) I have a lot of problems......

If I check the DB I can see a lot of SQL executions that "shouldn't" not exists (in my opinion).

For example if I only want all the records of a linked table, so using this sql:

"select field1,field2 from xxx"

I the oracle db I see:

1 (correct) execution of SQL ("select fiel1,field2,.. from xxx")

5 executions of:
select field1, field2 .. from xxx
where PK_xxx = :v001
OR PK_xxx = :v002
OR PK_xxx = :v003
OR PK_xxx = :v004
OR PK_xxx = :v005

where for "PK_xxx" I mean the primary key of the table named xxx


Why? Is that correct?

Hope you understand what I explain

thanx
bye bye
MArco

Unknown said...

Hi Marco,

I am not an MS Access expert. But I am seeing the same phenomenon.

First I see a statement:
select department_id from hr.departments;

Then I see multiple statements:

SELECT "DEPARTMENT_ID", "DEPARTMENT_NAME", "MANAGER_ID", "LOCATION_ID"
FROM "HR"."DEPARTMENTS"
WHERE "DEPARTMENT_ID" = :1
OR "DEPARTMENT_ID" = :2
OR "DEPARTMENT_ID" = :3
OR "DEPARTMENT_ID" = :4
OR "DEPARTMENT_ID" = :5
OR "DEPARTMENT_ID" = :6
OR "DEPARTMENT_ID" = :7
OR "DEPARTMENT_ID" = :8
OR "DEPARTMENT_ID" = :9
OR "DEPARTMENT_ID" = :10

It looks like MS Access first gets all the primary key ids for the table. Then it queries the table by the primary key to display the required details (in chunks of 10 to display the visible data).

Once I scroll down in the dataset the statement is executed again.

Looks reasonable to me.

Hope that helps,
~Dietmar.

Anonymous said...

Hi,
I found the post indeed very useful. However, I my table did not appear among the tables that appeared upon connection to my oracleXE database. What do you think could be responsible for this?

Unknown said...

Hi emiola,

most probably you connected to XE with a different user than the user who owned your table.

What schema (user) is the table in XE, is it the default schema HR?

What is the name of the table and the name of the user you used to create the table?

What tools did you use to create the table? Was it via the web interface (http://localhost:8080/apex) or via the command line?
Which user did you connect to?

Regards,
~Dietmar.

Anonymous said...

Many thanx, this worked out of the box.
This excellent guide should be part of the Oracle XE ODBC client help!

Ton

Anonymous said...

Hi Dietmar

Can you guide me, how can i setting the tnsname.ora file in XEclient.?

thank you for your help

Unknown said...

Hi anonymous,

I am not sure what the question is. In this post I already show to include the traditional tnsnames.ora file and reference it in an XE Client installation.

Search for "Update: 18.01.2007
Using tnsnames.ora with XE client" in this post.

Or please give more details what you are trying to accomplish.

Regards,
~Dietmar.