Oracle Database permite realizar una conexión transparente entre su base de datos y otras no Oracle. Por ejemplo, permite acceso (como si de una tabla Oracle se tratase) a tablas MySQL, Microsoft SQL Server, Informix, Microsoft Access, etc. Para ello incluye un servicio denominado Oracle Transparent Gateway. En este artículo explicamos la conexión de Oracle con Microsoft Access mediante ODBC.

Descripción y requisitos de la conexión de Oracle Database a Microsoft Access

Descripción

En este artículo conectaremos una base de datos Microsoft Access (mdb), ubicada en un PC con sistema operativo Microsoft Windows XP (aunque el ejemplo servirá para cualquier sistema operativo de Microsoft).

Por otro lado la base de datos Oracle Database estará ubicada en un PC con sistema operativo GNU/Linux Ubuntu. Con lo cual el sistema es lo más heterogéneo posible (base de datos principal de Oracle en Linux y base de datos a la que nos conectaremos en Windows XP).

En la siguiente figura se describe el mecanismo utilizado por Oracle para la conexión:

conectar Oracle Database con Microsoft Access - Figura de conexión

Requisitos previos para la conexión

Para la conexión de Oracle Database a tablas Microsoft Access (mdb) necesitaremos lo siguiente (tal y como se muestra en la figura anterior):

  • En el PC que tendrá la base de datos Microsoft Access (con sistema operativo de Microsoft) necesitaremos:
    • Oracle Client (en este artículo explicamos cómo instalarlo): es gratuito, descargable de la web de Oracle y su instalación en modo «mínimo» (Runtime) es sencilla.
    • ODBC de Microsoft Access: ya viene preinstalado con Microsoft Windows XP, también se instala con Microsoft Office.
    • Microsoft Jet: ya viene preinstalado en Microsoft Windows XP, también se instala con Microsoft Access.
    • El fichero de la base de datos Microsoft Access (para este ejemplo lo llamaremos ajpdsoft_access.mdb).
  • En el PC con Oracle Database instalado en GNU/Linux:
    • Necesitaremos, obviamente, Oracle Database correctamente instalado y el puerto 1521 abierto en el cortafuegos (es el puerto por defecto de Oracle Database, el utilizado para la conexión a la base de datos por los clientes, con lo cual debería estar abierto).
    • Oracle Database debe tener instalado el paquete SQL de datos heterogéneos (ubicado por defecto en ORACLE_HOME/rdbms/admin/caths.sql). Este fichero SQL ya viene ejecutando normalmente, por lo que (salvo excepciones) no será necesario ejecutarlo. De ser necesario abriremos una ventana de terminal (en Linux) o una ventana de línea de comandos MS-DOS (en Windows), abriremos SQL*Plus, nos conectaremos con un usuario con suficientes permisos y ejecutaremos:

      Para Windows:

      @C:/Oracle/rdbms/admin/caths.sql

      Para GNU/Linux:

      @/Oracle/rdbms/admin/caths.sql(cambiando las rutas por las elegidas en la instalación de Oracle Database)

En este artículo estamos suponiendo en todo momento que Oracle Database reside en un equipo diferente al equipo con la base de datos Microsoft Access, pero podríamos tener, perfectamente, Oracle Database en el mismo equipo Windows que la base de datos Microsoft Access.

En el siguiente artículo explicamos cómo instalar Oracle Database 11g en Windows XP:

Configuración del equipo de Microsoft Windows con el fichero MDB de Access

En el equipo «cliente» con la base de datos Microsoft Access (ajpdsoft_access.mdb) realizaremos las siguientes tareas. Deberemos tener el fichero mdb de Access con las tablas y datos de la aplicación Access. Para el ejemplo utilizaremos una tabla llamada «facturas» con la siguiente estructura:

conectar Oracle Database con Microsoft Access - Estructura de la tabla de Access

Y los siguientes datos:

conectar Oracle Database con Microsoft Access - Datos de la tabla de Access

Crearemos un nuevo origen de datos (ODBC) de Microsoft Access que apunte a la base de datos Access. Para ello accederemos a «Inicio» – «Configuración» – «Panel de control» – «Herramientas administrativas» y ejecutaremos «Orígenes de datos (ODBC)». Nos abrirá la ventana de «Administrador de orígenes de datos ODBC, pulsaremos en la pestaña «DSN de sistema» (si queremos que el ODBC esté disponible para todos los usuarios del equipo Windows ó «DSN de usuario para que esté disponible sólo para el usuario con el que hemos iniciado sesión). A continuación pulsaremos en el botón «Agregar»:

conectar Oracle Database con Microsoft Access - Crear ODBC

Seleccionaremos el origen de datos «Microsoft Access Driver (*.mdb) y pulsaremos «Finalizar»:

conectar Oracle Database con Microsoft Access - Crear ODBC

A continuación introduciremos el Nombre del origen de datos (fundamental para luego establecer el enlace con Oracle), en nuestro caso «conexion_oracle_access» y la Descripción del enlace (texto descriptivo), en nuestro caso «Prueba de conexión de Oracle con Access». A continuación pulsaremos en el botón «Seleccionar» para indicar el nombre y ubicación del fichero ajpdsoft_access.mdb (en nuestro caso)

conectar Oracle Database con Microsoft Access - Crear ODBC

Seleccionaremos la unidad y carpeta con el fichero ajpdsoft_access.mdb y pulsaremos «Aceptar»:

conectar Oracle Database con Microsoft Access - Crear ODBC

Aceptaremos el resto de ventanas y ya tendremos añadido correctamente el origen de datos ODBC de Microsoft Access.

A continuación configuraremos el fichero tnsnames.ora (de la instalación de Oracle Client o de la instalación de Oracle Database si está en el mismo equipo que Access). Este fichero suele estar ubicado en la carpeta de instalación de Oracle (ORACLE_HOME) y dentro de ésta, en la subcarpeta «network» y a su vez en la carpeta «admin». Por ejemplo en:

C:/ora92/network/admin/tnsnames.ora

Lo abriremos con cualquier editor de texto plano, como por ejemplo el Bloc de notas (notepad) de Microsoft:

conectar Oracle Database con Microsoft Access - Editar tnsnames.ora

Añadiremos al final de este fichero el texto seleccionado (en azul y en negrita debajo de la imagen):

conectar Oracle Database con Microsoft Access - Editar tnsnames.ora

Con el texto:

En el texto anterior, explicamos lo que es cada cosa para el fichero tnsnames.ora:

  • «conexionoracle»: lo que no está en negrita es la conexión normal con Oracle Database, no tiene nada que ver con nuestra conexión de Oracle a Access. Lo hemos puesto a modo de ejemplo, donde «192.168.1.100» es la IP del equipo con Oracle Database y GNU/Linux y «AJPDSOFT» es el nombre del SID de la base de datos Oracle Database.
  • «conexion_oracle_access»: nombre y cadena de conexión que se utilizará para el enlace de Oracle con Access. Podemos utilizar el nombre que queramos.
  • «SERVICE_NAME=conexion_oracle_access«: importante en este parámetro indicar el nombre que le hemos dado al origen de datos (ODBC) en el paso anterior.
  • «HS = OK«: con esto le estamos indicando a Oracle que para esta conexión utilice su servicio Heterogeneous Services o Oracle Transparent Gateway. De esta forma, Oracle tratará esta conexión mediante el origen de datos ODBC indicado.
  • «HOST=192.168.1.100«: en el parámetro «HOST» indicaremos el nombre (hostname) o la IP del equipo con la base de datos Oracle Database, en nuestro caso el equipo GNU/Linux Ubuntu con la IP 192.168.1.100.
  • Configuraremos también el fichero listener.ora, ubicado en la misma carpeta que el fichero tnsnames.ora. Lo editaremos, igual que el fichero tnsnames.ora, con el Bloc de notas (notepad). Hemos seleccionado el texto añadido para la conexión de Oracle con Access:
conectar Oracle Database con Microsoft Access - Editar listener.ora

En el texto anterior, explicamos lo que es cada cosa para el fichero listener.ora:

  • Todo lo que no está en negrita es un ejemplo del contenido «estándar» de este fichero (variarán los nombres de los SID de la base de datos, nombres o IPs de los host, etc.).
  • Lo que está en negrita, a partir de «(SID_DESC =»…:
  • «ORACLE_HOME«: carpeta de instalación de Oracle Database (si está en el mismo equipo que Access) o Oracle Client.
  • «SID_NAME = conexion_oracle_access«: importante en este parámetro indicar el nombre que le hemos dado SERVICE_NAME en el fichero tnsnames.ora, en nuestro caso el mismo que el ODBC «conexion_oracle_access» (de esta forma no hay confusión.
  • «PROGRAM = hsodbc«: con esto le estamos indicando a Oracle que para esta conexión utilice su servicio Heterogeneous Services o Oracle Transparent Gateway. De esta forma, Oracle tratará esta conexión mediante el origen de datos ODBC indicado. 

A continuación modificaremos (o crearemos si no existe que es lo lógico) el fichero propio del servicio HS (Heterogeneous Service ó Oracle Transparent Gateway). Para ello crearemos un fichero con el nombre compuesto de: «init» + «nombre del SID» + «.ora». En nuestro caso, puesto que el nombre que le hemos dado al SID es conexion_oracle_access, el nombre del fichero a crear será:

initconexion_oracle_access.ora

y estará ubicado en C:/ora92/hs/admin:

conectar Oracle Database con Microsoft Access - Editar fichero servicio HS

Tendrá el siguiente contenido (seleccionado en azul la parte que nos interesa, el resto son comentarios):

conectar Oracle Database con Microsoft Access - Editar fichero servicio HS

Con el texto:

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
# HS init parameters
HS_FDS_CONNECT_INFO = conexion_oracle_access
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = C:\ora92\hs\admin\odbc_access.trc
HS_AUTOREGISTER = TRUE
# Environment variables required for the non-Oracle system
#set <envvar>=<value>

Donde:

  • HS_FDS_CONNECT_INFO: será el parámetro más importante, en él indicaremos el nombre del SID que hemos creado en el fichero tnsnames.ora que a su vez apunta al ODBC creado en el primer paso. Como siempre en este artículo, para evitar confusiones, lo hemos nombrado siempre como «conexion_oracle_access».
  • HS_FDS_TRACE_LEVEL: indicaremos el nivel de traza, con «0» (OFF) lo desactivamos. Esto sólo es conveniente activarlo en caso de depuración de errores, para activarlo «1» o también «ON».
  • HS_FDS_TRACE_FILE_NAME: con este parámetro indicaremos la ruta del fichero de traza (si está activado).
  • HS_AUTOREGISTER: habilita o deshabilita el registro automático de servicios heterogéneos. Si la activamos (TRUE) la información se carga de forma automática en el diccionario del servidor. Activando este parámetro Oracle funcionará con los servicios heterogéneos de forma más rápida y eficiente.
  • Para acabar con la configuración necesaria en el equipo que contendrá la base de datos Microsoft Access, detendremos e iniciaremos el Listener con el comando:

    lsnrctl stop

Que devolverá algo así:

C:\>lsnrctl stop
LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 – Production on 13-FEB-2009 21:43:25
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
The command completed successfully
C:\>

Y lo volveremos a iniciar con el comando:

lsnrctl start

Que devolverá algo así:

C:\>lsnrctl start
LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 – Production on 13-FEB-2009 21:45:03
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Starting tnslsnr: please wait…
TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 – Production
System parameter file is C:\ora92\network\admin\listener.ora
Log messages written to C:\ora92\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc
)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PCWXP)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
STATUS of the LISTENER
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 – Produc
tion
Start Date 13-FEB-2009 21:45:05
Uptime 0 days 0 hr. 0 min. 2 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File C:\ora92\network\admin\listener.ora
Listener Log File C:\ora92\network\log\listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PCWXP)(PORT=1521)))
Services Summary…
Service «conexion_oracle_access» has 1 instance(s).
Instance «conexion_oracle_access», status UNKNOWN, has 1 handler(s) for this service…
Service «orcl» has 1 instance(s).
Instance «orcl», status UNKNOWN, has 1 handler(s) for this service…
Service «AJPDSOFT» has 1 instance(s).
Instance «AJPDSOFT», status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

(obviamente el resultado variará en función de la configuración del fichero listener.ora).

Tras reiniciar el Listener, realizaremos una prueba con el comando Oracle tnsping:

tnsping conexion_oracle_access

Con el resultado (si todo es correcto):

C:\>tnsping conexion_oracle_access
TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 – Production on 13-FEB-2009 21:56:33
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
C:\ora92\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=192.168.1.100)
(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME=conexion_oracle_access)) (HS = OK))
OK (0 msec)
C:\>

Configuración del equipo con Oracle Database

Crearemos el Database Link (enlace a base de datos): para esto accederemos a Oracle Database con cualquier software que permita ejecutar sentencias SQL de creación de objetos en Oracle. En nuestro caso utilizaremos una utilidad desarrollada por nuestro técnicos llamada AjpdSoft Administración Bases de Datos (gratuita y con código fuente incluido), aunque podremos utilizar por ejemplo la utilidad que incluye Oracle llamada SQL*Plus. En cualquiera de ellas ejecutaremos la siguiente sentencia SQL para crear el DBLINK:

CREATE DATABASE LINK conexion_oracle_access
USING ‘conexion_oracle_access’

Donde:

«conexion_oracle_access» (el de la primera línea): será el nombre que le daremos al enlace, que luego hemos de utilizar para acceder a las tablas Access desde Oracle, con la estructura: nombre_tabla_access@nombre_dblink, en el ejemplo sería:

select * from facturas@conexion_oracle_access

«conexion_oracle_access» (el de la segunda línea): será el nombre que le hayamos dado en el fichero tnsnames.ora al enlace con Access.

La consulta SQL en ejecución:

conectar Oracle Database con Microsoft Access - Consulta SQL para crear enlace a base de datos

Probaremos el enlace de Oracle con nuestra base de datos Microsoft Access (ajpdsoft_access.mdb). En primer lugar ejecutaremos la consulta SQL genérica de prueba de DBLINK:

select * from dual@conexion_oracle_access

Y por último realizaremos una prueba real. Para ello, desde la aplicación que utilicemos para conectarnos a Oracle (por ejemplo AjpdSoft Administración Bases de Datos), ejecutaremos una consulta SQL que nos devuelva el contenido de la tabla creada en Access (en nuestro caso «facturas»):

select * from facturas@conexion_oracle_access