Home » SQL & PL/SQL » SQL & PL/SQL » DB-LINK to mysql (Oracle Database 11g Release 11.2.0.1.0)
DB-LINK to mysql [message #658609] Wed, 21 December 2016 12:51 Go to next message
iliyan_84@abv.bg
Messages: 18
Registered: May 2016
Location: Bulgaria
Junior Member
Hi,
I have a oracle 11g Release 11.2.0.1.0 datebase with dblink to mysql 5.5.38 and for programming i use PL/SQL Release 11.2.0.1.0. I can make selects from oracle to mysql but some columns from mysql can`t be select (missing). I mark that the missing columns are with type text.What is wrong in this situation and how to fix it
Re: DB-LINK to mysql [message #658610 is a reply to message #658609] Wed, 21 December 2016 12:59 Go to previous messageGo to next message
BlackSwan
Messages: 26208
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

what datatype are the "missing" MYSQL columns?
Re: DB-LINK to mysql [message #658611 is a reply to message #658610] Wed, 21 December 2016 13:16 Go to previous messageGo to next message
iliyan_84@abv.bg
Messages: 18
Registered: May 2016
Location: Bulgaria
Junior Member
/forum/fa/13368/0/With type text

This is structure of the table in mysql
  • Attachment: did.jpg
    (Size: 121.12KB, Downloaded 381 times)

[Updated on: Wed, 21 December 2016 13:27]

Report message to a moderator

Re: DB-LINK to mysql [message #658612 is a reply to message #658611] Wed, 21 December 2016 13:26 Go to previous messageGo to next message
BlackSwan
Messages: 26208
Registered: January 2009
Location: SoCal
Senior Member
BLOB , TEXT L + 2 bytes, where L < 2^16=65536
but Oracle SQL strings are limited to 4000, so TEXT columns can NOT be correctly handles by Oracle SQL engine.
Re: DB-LINK to mysql [message #658613 is a reply to message #658611] Wed, 21 December 2016 13:29 Go to previous messageGo to next message
iliyan_84@abv.bg
Messages: 18
Registered: May 2016
Location: Bulgaria
Junior Member
/forum/fa/13369/0/


and Name column missing
  • Attachment: gps.jpg
    (Size: 192.11KB, Downloaded 416 times)
Re: DB-LINK to mysql [message #658614 is a reply to message #658613] Wed, 21 December 2016 13:30 Go to previous messageGo to next message
iliyan_84@abv.bg
Messages: 18
Registered: May 2016
Location: Bulgaria
Junior Member
Is there any solution on this problem?
Re: DB-LINK to mysql [message #658615 is a reply to message #658614] Wed, 21 December 2016 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First, try it using SQL*Plus, the problem may come from your client tool.

Re: DB-LINK to mysql [message #658641 is a reply to message #658615] Thu, 22 December 2016 06:40 Go to previous messageGo to next message
iliyan_84@abv.bg
Messages: 18
Registered: May 2016
Location: Bulgaria
Junior Member
No same thing in SQL*Plus the column missing,any ideas
Re: DB-LINK to mysql [message #658645 is a reply to message #658641] Thu, 22 December 2016 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Copy and paste from SQL*Plus:
desc mytable@mylink
Before, Please read How to use [code] tags and make your code easier to read.

Re: DB-LINK to mysql [message #658651 is a reply to message #658645] Thu, 22 December 2016 09:28 Go to previous messageGo to next message
iliyan_84@abv.bg
Messages: 18
Registered: May 2016
Location: Bulgaria
Junior Member
/forum/fa/13371/0/
  • Attachment: sqldit.jpg
    (Size: 123.91KB, Downloaded 357 times)
Re: DB-LINK to mysql [message #658653 is a reply to message #658651] Thu, 22 December 2016 09:45 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is not what I asked.
And you don't need to post an image to post simple text.

[Updated on: Thu, 22 December 2016 10:33]

Report message to a moderator

Re: DB-LINK to mysql [message #658654 is a reply to message #658653] Thu, 22 December 2016 10:30 Go to previous messageGo to next message
iliyan_84@abv.bg
Messages: 18
Registered: May 2016
Location: Bulgaria
Junior Member
I am sorry but i can`t uderstand you
Re: DB-LINK to mysql [message #658655 is a reply to message #658654] Thu, 22 December 2016 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just execute the command I gave.

Re: DB-LINK to mysql [message #658658 is a reply to message #658655] Thu, 22 December 2016 11:09 Go to previous messageGo to next message
iliyan_84@abv.bg
Messages: 18
Registered: May 2016
Location: Bulgaria
Junior Member
SQL> desc "Didtable"@gpsinfo;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DID                                       NOT NULL NUMBER(20)
 GroupID                                            NUMBER(20)
 OwnerID                                            NUMBER(20)
 AutomobileID                                       NUMBER(20)
 OwnNumber                                          NVARCHAR2(100)
 SmsNumber                                          NVARCHAR2(100)
 DatacallNumber                                     NVARCHAR2(100)
 ServiceNumber                                      NVARCHAR2(100)
 ProfileID                                          NUMBER(20)
 version                                            NUMBER(20)
 email                                              NVARCHAR2(255)
 is_fictive                                NOT NULL NUMBER(10)
*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read

[Updated on: Thu, 22 December 2016 14:21] by Moderator

Report message to a moderator

Re: DB-LINK to mysql [message #658661 is a reply to message #658658] Thu, 22 December 2016 13:09 Go to previous messageGo to next message
joy_division
Messages: 4903
Registered: February 2005
Location: East Coast USA
Senior Member
You have two completely different tables.
Re: DB-LINK to mysql [message #658662 is a reply to message #658661] Thu, 22 December 2016 13:22 Go to previous messageGo to next message
BlackSwan
Messages: 26208
Registered: January 2009
Location: SoCal
Senior Member
What is Operating System name & version upon which MYSQL database resides?
Re: DB-LINK to mysql [message #658667 is a reply to message #658661] Thu, 22 December 2016 14:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
joy_division wrote on Thu, 22 December 2016 14:09
You have two completely different tables.
Not really. Some ODBC drivers skip columns they can't handle. Both missing columns (Name & Notes) are text and longtext which apparently ODBS driver can't handle. Some ODBC drivers have parameters for that. E.g. EasySoft SQL Server ODBC driver has VarMaxAsLong = Yes/No. It could be OP needs to set proper parameters.

SY.
Re: DB-LINK to mysql [message #658669 is a reply to message #658667] Thu, 22 December 2016 15:38 Go to previous messageGo to next message
joy_division
Messages: 4903
Registered: February 2005
Location: East Coast USA
Senior Member
Solomon Yakobson wrote on Thu, 22 December 2016 15:17
joy_division wrote on Thu, 22 December 2016 14:09
You have two completely different tables.
Not really. Some ODBC drivers skip columns they can't handle. Both missing columns (Name & Notes) are text and longtext which apparently ODBS driver can't handle. Some ODBC drivers have parameters for that. E.g. EasySoft SQL Server ODBC driver has VarMaxAsLong = Yes/No. It could be OP needs to set proper parameters.
Looks like I completely misread that posted info. I thought there were two completely different tables in the describe in SQL*Plus vs. describe in the other tool. I make many dumb mistakes reading some of these questions.
Re: DB-LINK to mysql [message #658699 is a reply to message #658669] Fri, 23 December 2016 07:01 Go to previous messageGo to next message
iliyan_84@abv.bg
Messages: 18
Registered: May 2016
Location: Bulgaria
Junior Member
innodb_version 5.5.38
protocol_version 10
slave_type_conversions
version 5.5.38-0ubuntu0.12.04.1-log
version_comment (Ubuntu)
version_compile_machine x86_64
version_compile_os debian-linux-gnu

and I 100% sure that then tables are same. Oracle datebase run on server 2003 and for dblink use mysql odbc connector 5.1.1.3
Re: DB-LINK to mysql [message #658700 is a reply to message #658699] Fri, 23 December 2016 07:05 Go to previous messageGo to next message
BlackSwan
Messages: 26208
Registered: January 2009
Location: SoCal
Senior Member
You can be sure & wrong at the same time.
Please NOTE that when MYSQL runs on *NIX operating system, then table_names are CaSeSenSiTive/
Didtable != DidTable
Re: DB-LINK to mysql [message #658701 is a reply to message #658700] Fri, 23 December 2016 07:25 Go to previous message
iliyan_84@abv.bg
Messages: 18
Registered: May 2016
Location: Bulgaria
Junior Member
SQL> desc "DidTable"@gpsinfo
Name Null? Type
----------------------------------------- -------- ----------------------------
DID NOT NULL NUMBER(20)
GroupID NUMBER(20)
OwnerID NUMBER(20)
AutomobileID NUMBER(20)
OwnNumber NVARCHAR2(100)
SmsNumber NVARCHAR2(100)
DatacallNumber NVARCHAR2(100)
ServiceNumber NVARCHAR2(100)
ProfileID NUMBER(20)
version NUMBER(20)
email NVARCHAR2(255)
is_fictive NOT NULL NUMBER(10)

SQL> spool off;
Previous Topic: Quantity allocation to discount slabs
Next Topic: Order By Taking time
Goto Forum:
  


Current Time: Tue Oct 23 14:20:13 CDT 2018