Database link, heterogeneous services

[mike777]

Hello all.

I keep going back and forth on this as to whether it's an Oracle issue or a MySQL. So, honestly, I'll let you decide and will take your advice.

Not sure if this is allowed, but I'm going to cross-post this question in the MySQL forum, with a similar message there that it is cross-posted here.

Scenario: I am successfully accessing MySQL data via ODBC through Oracle's Heterogeneous Services. There is a problem, however. Hence, this post. Suppose my query looks something like this:

select "f1","f2","f3" from MySQLTable@MySQLDB

where MySQLDB is an Oracle database link that connects, via ODBC, to a MySQL database.

When I run the query in MS Access or MS FoxPro, it works fine. So we know the table name and column names are good (the only difference being that you have to take out the double-quotes, otherwise these programs think the values within the quotes are constants and not column names).

When I run the query in Oracle (where the double-quotes are required, I have learned, when accessing MySQL data via ODBC), exactly as typed above, I get this error (total, verbatim):

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-debug]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to user near "MySQLTable" WHERE "id"=3' at line 1(SQL State: 37000; SQL Code: 1064)
ORA-02063: preceding 2 lines from MySQLDB

Now, when I start to do research, I run this query:

select table_name,column_name,data_type,data_length
from   all_tab_columns@MySQLDB
where  table_name='MySQLTable'

Upon doing so, I get the following results:
===========================================================
TABLE_NAME........COLUMN_NAME........DATA_TYPE........DATA_LENGTH
MySQLTable...........f1..........................VARCHAR2.........25
MySQLTable...........f2..........................NUMBER.............4
MySQLTable...........f3..........................LONG.................2147483647
===========================================================

Seeing this, I am immediately suspicious of column "f3". So I rerun the query that points to the MySQL database with that column excluded as in:

select "f1","f2" from MySQLTable@MySQLDB

and it works just fine.

Can you suggest what might be the problem? I need the data in column "f3".

Many thanks.

-Mike