I installed Oracle Server Express 10g on my computer (WinXP). I want to create a database link to Teradata using ODBC. I've created (non-ODBC) database links to other Oracle databases successfully. However, I can't seem to get the Teradata database link to work.
Here's what I did:
1) Created an ODBC Connection in Windows to Teradata using Teradata's ODBC driver version 13. Tested that it works by connecting to the database using Teradata SQL Assistant. Called the connection LPS_PROD_VIEW. I saved my Login details in the ODBC settings.
2) Edited listener.ora In the SID_LIST_LISTENER section:
(SID_DESC =
(SID_NAME = LPS_PROD_VIEW)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = hsodbc)
In the LISTENER section
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1524)
3) In the ...hs\admin\ folder, added initLPS_PROD_VIEW.ora file. Contents:
HS_FDS_CONNECT_INFO = LPS_PROD_VIEW
HS_FDS_TRACE_LEVEL = ON
4) Added an entry in TNSnames.ora (both in the XE server directory and a seperate 10g directory which I had previously before installing Oracle XE).
BMW =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = localhost)(Port = 1524))
)(CONNECT_DATA =
(SID = LPS_PROD_VIEW)(HS=OK)
)
5) Restarted Oracle listener services through services.msc.
6) Connected to local database to create the database link by doing
Create database link TERADATA connect to <username> identified by <password> using 'LPS_PROD_VIEW'
7) Attempt to run queries but get an ORA-12154: TNS: Could not resolve the connect identifier specified.
What am I doing wrong? Does HS support Teradata ODBC ver 13?
Thanks in advance and appreciate your help!
