[MDEV-9071] Getting error ORA-12899: value too large for column inserting from MariaDB to Oracle Created: 2015-11-02  Updated: 2023-12-05

Status: Stalled
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.0
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Minor
Reporter: Andy Ferretti Assignee: Olivier Bertrand
Resolution: Unresolved Votes: 2
Labels: None
Environment:

10.0.20-MariaDB-log
RedHat Linux
Oracle Enterprise Edition 11.2.0.4


 Description   

From MariaDB inserting into Oracle table using linked table.
No matter what, Oracle thinks source table in MariaDB has one more character than target in Oracle. Details below:
Oracle:

drop table simple;
 
create table simple (col1 varchar2(5), col2 number);
 
insert into simple values ('A', 199);
 
select * from simple;
 
commit;

MariaDB:

describe simple_link
 
--------------
 
 
 
Field   Type    Null    Key     Default Extra
 
COL1    varchar(5)      YES             NULL
 
COL2    double(40,0)    YES             NULL
 
 
 
ERROR 1296 (HY000) at line 5 in file: 'simple.sql': Got error 122 'SQLExecute: [unixODBC][Oracle][ODBC][Ora]ORA-12899: value too large for column "SOE"."SIMPLE"."COL1" (actual: 6, maximum: 5)
 
' from CONNECT

Fixed by redefining table simple in Oracle to length of 6:
create table simple (col1 varchar2(6), col2 number);

Able to insert from MariaDB to Oracle.

--------------
 
select * from simple_link
 
--------------
 
 
 
COL1    COL2
 
A       199
 
--------------
 
insert into simple_link values ("B", 299)
 
--------------
 
 
 
--------------
 
select * from simple_link
 
--------------
 
 
 
COL1    COL2
 
B       299
 
A       199



 Comments   
Comment by Olivier Bertrand [ 2015-11-05 ]

I don't understand how a one letter value ("B") can be regarded by unixODBC as having a length of 6.

On Windows 7 I cannot reproduce this error.

create table dorx (
 
command varchar(128) not null,
 
number int(5) not null flag=1,
 
message varchar(255) flag=2)
 
engine=connect table_type=odbc connection='DSN=ORACLE_TEST;UID=system;PWD=manager' option_list='Execsrc=1';
 
select * from dorx where command='create table simple (col1 varchar2(5), col2 number)';
 
select * from dorx where command="insert into simple values ('A', 199)";

The above creates and inserts one line into the table simple in Oracle.

create table simple_link (
 
col1 varchar(5),
 
col2 double(40,0))
 
engine=CONNECT CONNECTION='DSN=ORACLE_TEST;UID=system;PWD=manager' table_type=odbc tabname='simple';

The CONNECT simple_table is created with varchar(5):

describe simple_link;

Field Type Null Key Default Extra
col1 varchar(5) YES <null> <null> <null>
col2 double(40,0) YES <null> <null> <null> 

insert into simple_link values ("B", 299);

This command is executed successfully.

select * from simple_link;

This last one replies:

col1 col2
A 199
B 299
Comment by Andy Ferretti [ 2015-11-05 ]

When I first had this problem, COL1 was char(1). I tried a few solutions, first using varchar2(1), and then increasing it to 5. Each time, it lists the actual as one greater than the the value that I use when I create the table. However, if I redefine the Oracle table to have one extra character, it works. OK.
Here is some more interesting data, note that when I try to insert 6 characters, it just truncates one. My hunch is that it has something to do with the locale. I'll have to work on it some more.

--------------
select * from simple_link
--------------

COL1 COL2
B 299
B 299
ABCDE 399
12345 599
A 199
--------------
delete from simple_link
--------------

--------------
commit
--------------

--------------
select * from simple_link
--------------

--------------
insert into simple_link values ("B", 299)
--------------

--------------
select * from simple_link
--------------

COL1 COL2
B 299
--------------
insert into simple_link values ("ABCDE", 399)
--------------

--------------
insert into simple_link values ("123456", 599)
--------------

--------------
select * from simple_link
--------------

COL1 COL2
12345 599
B 299
ABCDE 399
--------------
commit
--------------

Comment by Andy Ferretti [ 2015-11-05 ]

Would you mind running this command against your Oracle database?
select * from nls_database_parameters where parameter like 'NLS%CHARACTERSET';

Comment by Olivier Bertrand [ 2015-11-05 ] 

select * from ndp where parameter like 'NLS%CHARACTERSET';

PARAMETER VALUE
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
Comment by Andy Ferretti [ 2015-11-05 ]

Thanks, this matches the values that I have. I'll have to keep digging.

Comment by Alexander Barkov [ 2015-11-06 ]

I could not reproduce the problem.

Oracle:

drop table simple;
 
create table simple (col1 varchar2(5), col2 number);
 
insert into simple values ('A', 199);
 
commit;

MariaDB:

DROP TABLE IF EXISTS simple_link;
 
CREATE TABLE simple_link ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' TABNAME='MTR.SIMPLE';
 
INSERT INTO simple_link VALUES ('B',300);
 
INSERT INTO simple_link VALUES ('BBBBB',400);
 
SELECT * FROM simple_link;

returns

+-------+------+
 
| COL1  | COL2 |
 
+-------+------+
 
| A     |  199 |
 
| B     |  300 |
 
| BBBBB |  400 |
 
+-------+------+

MariaDB:

SHOW CREATE TABLE simple_link;

returns

+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table       | Create Table                                                                                                                                                                                                                              |
 
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| simple_link | CREATE TABLE `simple_link` (
 
  `COL1` varchar(5) DEFAULT NULL,
 
  `COL2` double(40,0) DEFAULT NULL
 
) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineOracle;UID=mtr;PWD=mtr' `TABLE_TYPE`='ODBC' `TABNAME`='MTR.SIMPLE' |
 
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Comment by Alexander Barkov [ 2015-11-06 ]

Andy, can you please post the entire simple.sql ?

Thanks.

Comment by Andy Ferretti [ 2015-11-06 ]

Here is the script with the create. I added the " DEFAULT CHARSET=latin1 " from your example and tried again (same results).

simple_link_create.sql

 
use dblink;
 
drop  table simple_link;
 
create table simple_link  ENGINE=CONNECT DEFAULT CHARSET=latin1  TABLE_TYPE=ODBC tabname='SIMPLE' CONNECTION='DSN=t1c3d683;UID=SOE;PWD=Swingbench20151015' ;
 
describe simple_link;
 
select * from simple_link;
 
insert into simple_link values ("B", 299);
 
select * from simple_link;

And here is an additional script:

use dblink;
 
describe simple_link;
 
select * from simple_link;
 
delete from simple_link;
 
commit;
 
select * from simple_link;
 
insert into simple_link values ("B", 299);
 
select * from simple_link;
 
insert into simple_link values ("ABCDE", 399);
 
insert into simple_link values ("123456", 599);
 
select * from simple_link;
 
commit

Here is my script on Oracle:

drop table simple;
 
create table simple (col1 varchar2(6), col2 number);
 
insert into simple values ('A', 199);
 
select * from simple;
 
commit;

Comment by Julien Fritsch [ 2018-03-22 ]

bertrandop may I ask if this is really in Progress still? I guess, no. And if not, is that planned or should we close it, maybe?

Comment by Olivier Bertrand [ 2020-07-28 ]

By the way, as there are many problems with ODBC, especially on Linux, could you try the same using JDBC instead? So we can be sure it is not an ODBC issue.

Generated at Thu Feb 08 07:31:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.