World List Breast Size By Nationality

Using Access to MSAccess DB from Oracle.


You can access oracle databases are not seamlessly with Oracle Transparent Gateways

.

This feature allows you to configure an ODBC DSN entry as you would any Oracle service. Thus, through a dblink, a user could access a table in a database access (or SQLServer or MySQL, for example) from a query or from PL / SQL.

heterogeneous services architecture is based on the following configuration:

You specify a TNS entry in tnsnames.ora file

resolve a host connection, port and service name specified with the parameter
HS = OK
(Oracle Heterogeneous Services).

On the other hand, the target listener is defined for that service call to a program called "hsodbc" which, through a parameter file init

. Ora enter the DNS associated with specific MSAccess database. In this way, and creating a dblink, anyone could make a select data to cross the Oracle schema and an Access DB. STEPS

1 .- Install the dictionary heterogeneous services.

2 .- Configure the tnsnames.ora


3 .- Configure the listener.


4 .- Check that the listener and the tnsnames.ora work properly. 5 .- Configure ODBC for DB access.

6 .- Create dblink to the DB access. 7 .- Create the init file


. Ora

8 .- Run a query using this dblink.


1 .- Install in the dictionary heterogeneous services
@? / rdbms / admin / caths.sql

NOTE: these views may be installed already in the database.
2 .- Configure the tnsnames.ora bbdd_access =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = tcp)
(HOST =
192,168 .2.4

)


(PORT = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME = odbc_access) ) (HS = OK)
)
3 .- Configure the listener. (Entry in bold is to be added) ...
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0))

)

(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.2.4
)(PORT = 1521))
)
)
)
SID_LIST_LISTENER = (SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = orcl)
)
(SID_DESC =
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = odbc_access)
(PROGRAM = hsodbc)
)
)
4 .- Check that the listener and the tnsnames.ora is working properly.
C: \\ Documents and Settings \\ Administrator> tnsping bbdd_access

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 08-SEP-2006 18:19: 42
Copyright (c) 1997 Oracle Corporation. All rights reserved.
parßmetros Files used:

D: \\ oracle \\ ora92 \\ network \\ admin
\\ sqlnet.ora

TNSNAMES Adapter used to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)


(HOST = 192.168. 2.4)
(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = odbc_access)) (HS
= OK)) Properly performed (50 ms)


5 .- Configuring ODBC for the DB access.
access the database used in the example has a single table with four rows of example ... create table example (id counter, text value)



need to create an ODBC to access specific access to the database. Since this option is set to Windows Control Panel, Administrative Tools, ODBC Manager and the System DSN tab.

from there establishing odbc_access driver connected to the database you use: "test_hs.mdb" with this example table.



NOTE: This access is password bbdd "tesths"

6 .- Create dblink to the DB access. SQL> CREATE DATABASE LINK bbdd_access
2 CONNECT TO ADMIN TESTHS
3 IDENTIFIED BY USING 'bbdd_access';
Link to the database created. 7 .- Create the init file

. Ora in the directory ORACLE_HOME / hs / admin /


- FILE initODBC_ACCESS.ora
#




# HS init parameters


# HS_FDS_CONNECT_INFO = odbc_access
HS_FDS_TRACE_LEVEL = 0

HS_FDS_TRACE_FILE_NAME = odbc_access.trc
HS_AUTOREGISTER = TRUE


8 .- Launch the query via the dblink
SQL> column "value" format a40
SQL> set pages 1000
SQL> set Lines 120

SQL > select * from @ bbdd_access example,

id
value
---------- ---------------------- -------- ----------
1 data1
2 data2
3 data3

4 dato4