LONG Datatype - Hold it!! Don't tell me not to use it :-)

[mike777]

Hello all.
OK, I know I know: DON'T EVER USE THE LONG DATATYPE.
I understand.
But here's my situation:
I'm accessing data through a db link that's from from a MySQL database. Like SQL Server (I guess), they have this char datatype (I don't know the official name of the datatype in MySQL) that is 2G in length I guess.
I did a

select * from all_tab_columns@my_db_link_to_MySQL

and under the data_type column it says "LONG" and under the data_length column it says 2,147,483,647.
I don't want to be a whiner, but in MS Access I just say:

select * from the_mysql_table@my_db_link_to_MySQL

and I get back all the data just fine.
Problem is, I need to use it in Oracle.
Can anyone suggest to me how to access this column via Oracle?
I tried

select substr(really_long_col,1,4000) from the_mysql_table@my_db_link_to_MySQL

but that is probably a bumbling attempt, but it's the best I know. It did not work...lol.
Thank you for the help.
-Mike

 

[Turkbear]

Hi,
explain It did not work
Oracle Syntax error, no data, smoke and flames, ??





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[mike777]

Thanks, Turkbear. Good point.

I didn't pay that much attention to the specific error. I guess I assumed it was just like when you try to say

select some_long_col from a_table

in Oracle. I believe the error (I'm home now so can't retry) was something like...you know I can't remember. Since it's going across a db link, the exact error message I think is not exactly the same as when you try to select a long datatype from a table in Oracle. It was maybe something like "invalid identifier". I think that may have been it. Of course, you get that error when you try to select a column from a table when the column doesn't exist in the table. In this case, I know the column name is good (from select * in MS Access), and when I commented out the column from the select statement in Oracle, the query worked. It was at that point that I rechecked the metadata and saw that the datatype of that problem column was LONG (as far as Oracle was concerned). I then assumed the LONG was the problem and that the "invalid identifier" error message was one of those error messages that while well intended, was nonetheless pointing in the wrong direction (as error messages occasionally do...even in Oracle). OK, I'm rambling I know. Just trying to cover all the bases (even if it is with spaghetti).

When I tried the

select substr(some_long_col,1,4000)

I got an error that said the syntax was not valid on the remote database in that context (again, something to that effect).

Does any of this help at all?

If this is still ambiguous and incomplete, I'll have to wait till Monday to respond when I can run the tests again and copy down the exact error messages.

And no, there was no smoke and flames. In my case, that's progress.

 

[ddiamond]

This link may help.

 

[mike777]

I guess I owe somebody an apology!
I just executed this code (in Oracle):

create table tmp20060508 (f1 long);
insert into tmp20060508 values ('abc123');
commit;

Then I say

select * from tmp20060508

and it works fine. I get results. My mistake was that I always thought you could not do a select on a LONG-datatype column.
The exact error I get when I say:

select "comments" from MySQL_Table@db_link_to_MySQL

is this:

ORA-28500: connection from ORACLE to 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 use near "MySQLTable" WHERE "id"=3' at line 1[SQL State: 37000; SQL Code: 1064)
ORA-02063: preceding 2 lines from DB_LINK_TO_MYSQL ORA-06512 at line 24

I have no problem with any of this syntax when issuing select statements from MS Access/FoxProp.
Can you help? Many thanks.
-Mike