ORA-12663 and dblinks to non-oracle table

From: Glen A Stromquist <glen_stromquist_at_no.spam.yahoo.com>
Date: Tue, 02 Sep 2003 21:35:25 GMT
Message-ID: <x285b.64105$ho5.1152634@news2.telusplanet.net>

One of our apps uses a dblink to an MSaccess db on a different machine, to set this up I created an odbc connection on the main database server to the access db and made the neccesary changes to tnsnames, listener.ora and init.ora file in the ora81\hs\admin directory. I also turned global_names to false in the main databases init.ora file. (this was all per vendors instructions)

Files changed are below:

---

tnsnames.ora:
ACCESSDBNAME =
    (DESCRIPTION=

     (ADDRESS_LIST =


	(ADDRESS= (PROTOCOL=tcp)(HOST=localhost)(PORT=1521))

     )
     (CONNECT_DATA=


	(SID=ACCESS_DB_NAME)

     )


	(HS=OK)

     )

listener.ora:
SID_LIST_LISTENER =

       SID LIST=(


	(SID_DESC =

         (SID_NAME = ACCESS_DB_NAME)
         (ORACLE_HOME = C:\oracle\ora81)
         (PROGRAM = hsodbc)
     )

    )
init<access_db>.ora in c:\oracle\ora81\hs\admin\:

HS_FDS_CONNECT_INFO=ACCESS_DB_NAME
HS_FDS_TRACE_LEVEL=ON
HS_FDS_TRACE_FILE_NAME=c:\orahs.log
===================================================

All was working good, I could so a select * from <access_table>@<dblink> as well as a desc and a select <col> from <access_table@<dblink> where <col> = 'stuff' on the MSaccess db.

While I was away last week someone commented out the entry for the heterogenus service in the tnsnames file, there were no other changes in it so I fixed it and could tnsping the service and do a select count(*) from the access tables ok, but when trying to do a desc or select a certain column in either the select or where clause I get "ORA-12663 services required by client not available on the server" and "ORA-00904 invalid column name" respectively.

Sqlnet.ora and listener.ora appear unchanged and ok. Database is 8.1.7.4 on W2000 advanced server.

One of the views in the main db that gets its info from a table via this dblink is ok (has all the data), so I'm not sure whats going on here, and searches and documentation so far have been fruitless.

Anywhere/anything else I should be looking at?

TIA ps. one other thing, when setting up the HS service as per the vendors instructions, they used a sid name longer than oracle accepts, so the changes to the tnsnames and listener files had to be made manually. I've been meaning to change this to proper length sid name so I can add the entry to my names server, but up till now it has been working fine so I've left it alone until I can schedule some down time. Received on Tue Sep 02 2003 - 16:35:25 CDT