RE: getting data from an access MDB file into Oracle

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Mon, 29 Jan 2001 22:10:10 -0000
Message-Id: <10756.127757@fatcity.com>

Adam,

I have got this working in a test environment but not used it in production and have a few suggestions that might help.

(I also found note 114820.1 helpful - "QUICK START GUIDE: WIN NT - Generic Connectivity using ODBC")

In your tnsnames.ora:

For hsodbc, put (HS=OK)
eg:
ACCESSLP.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =

        (ADDRESS =
          (PROTOCOL = TCP)
          (Host = nameofmypc)
          (Port = 1521)
        )

    )
(CONNECT_DATA = (SID = ACCESSLP)

    )
(HS=OK)

  )

In my listener.ora, I had:

(SID_DESC =

      (GLOBAL_DBNAME = ACCESSLP.WORLD)
      (SID_NAME = ACCESSLP)
      (PROGRAM = HSODBC)

)

In my initaccesslp.ora file (note the domain setting to avoid ora-02085.

# initaccesslp.ora
#
# Created 01-Dec-2000 , Bruce Reardon.
#
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent. 
#
# HS init parameters
#
#   |<- ODBC Data Source Name configured in step 4

HS_FDS_CONNECT_INFO = ACCESSLP
# |<- trace levels are from 0 to 4 (4 is maximum) HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = ACCESSLP.trc
# seet this so don't get ora-02085 when connecting from a database with global_names=true
HS_DB_DOMAIN=WORLD
HS_DB_NAME=ACCESSLP Hope this helps,

Regards,
Bruce

-----Original Message-----
From: Adam Turner [mailto:ATurner_at_concreteinc.com] Sent: Friday, 26 January 2001 2:02

Thanks guys.

I was hoping someone out there was using the Generic Hetergeneous Data access functionality in the Oracle 8i kernel. I found it and almost have it configured, but keep getting errors.

The access ideas are OK, but I don't want to install that on my production server - I just want some ODBC level connectivity to the .MDB file.

Here's the problem I am facing now. From metalink article 109730.1 I have configured my files like this:

tnsnames.ora:

<snip>
ADAMTEST_172.20.23.63 =
  (DESCRIPTION =
(ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.23.63)(PORT = 1521))     )
(CONNECT_DATA = (SID = adamtest)(SERVER = DEDICATED))
  )

hsodbc = (DESCRIPTION=

            (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
            (CONNECT_DATA=(SID=hsodbc))
            (HS=)
          )

ADAMTEST_MONKEY2 =
  (DESCRIPTION =
(ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = monkey2)(PORT = 1521))     )
(CONNECT_DATA = (SID = adamtest)(SERVER = DEDICATED))
  )
<snip>

listener.ora:

LISTENER.ORA Network Configuration File: D:\Oracle\Ora81\network\admin\listener.ora # Generated by Oracle configuration tools.

SAVE_CONFIG_ON_STOP_LISTENER = TRUE LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =

      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = monkey2)(PORT = 1521))
      )

)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =

      (GLOBAL_DBNAME = monkey2.concreteinc)
      (ORACLE_HOME = d:\oracle\ora81)
      (SID_NAME = adamtest)

)
(SID_DESC=

          (SID_NAME=hsodbc)
          (ORACLE_HOME=d:\oracle\ora81)
          (PROGRAM=hsodbc)
     )

)

inithsodbc.ora:

# 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 = accesstest

HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = hsodbc.log

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

then I add a db link to my "db"

create database link ksodbc using 'hsodbc';

I now have to ODBC entries: hsodbc, and accesstest ....

I issue a statement :

select * from table1_at_hsodbc
OR
select * from table1_at_hsodbc.concretetemedia OR
select * from table1_at_accesstest

and I get the same error:

ORA-12154 TNS:could not reslove service name.

If anyone has some time to take a look and give me another set of ideas for Received on Mon Jan 29 2001 - 16:10:10 CST

RE: getting data from an access MDB file into Oracle - Using Hete