Creating an Oracle Procedure Using DG4ODBC Against MySQL Loses Information About MySQL Text Columns (Doc ID 1378109.1)

Last updated on JANUARY 18, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 02-Jan-2014***

Symptoms

Creating a procedure that uses DG4ODBC to connect to MySQL using the MySQL ODBC driver 5.1.8 on Linux x86-64 causes column information to be lost for MySQL TEXT columns.

To reproduce -

In MySQL create 2 tables -


create table test_proc (col1 char(1), col2 text) ;
create table test_proc_1 (col1 char(1), col2 text) ;



In Oracle -

- set up DG4ODBC connection to MySQL using MySQL 5.1.8 ODBC driver
- create 2 procedures using the database link to MySQL -

create or replace procedure teste is
begin
insert into "test_proc"@mysql("col2") values
('test proc insert');
end teste ;

create or replace procedure teste_1 is
begin
insert into "test_proc"@mysql("col2") values
('test proc insert');
insert into "test_proc"@mysql("col2") values
('test proc insert');
end teste_1 ;


These can then be successfully executed -

SQL> begin
teste ;
end ;
/

PL/SQL procedure successfully completed.

SQL> begin
teste_1 ;
end ;
/

PL/SQL procedure successfully completed.


However, then trying to create a third procedure that uses 2 different MYSQL tables gives an error -

SQL> create or replace procedure teste_2 is
begin
insert into "test_proc"@mysql("col2") values
('test proc insert');
insert into "test_proc_1"@mysql("col2") values
('test proc insert');
end teste_2 ;
/

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TESTE_2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/6 PL/SQL: SQL Statement ignored
4/62 PL/SQL: ORA-00904: "col2": invalid identifier
SQL>


A describe of the tables in MySQL shows the text columns have been lost -

SQL> desc test_proc@mysql
Name Null? Type
----------------------------------------- --------
----------------------------
col1 NCHAR(1)

SQL> desc test_proc_1@mysql
Name Null? Type
----------------------------------------- --------
----------------------------
col1 NCHAR(1)

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms