Thanks for the question, Wang.
Asked: February 07, 2017 - 3:02 am UTC
Last updated: February 07, 2017 - 6:31 pm UTC
Version: 11.2.0.4
Viewed 10K+ times! This question is 
You Asked
Hi Tom,
We have a migration project which from Sybase SQL Anywhere to Oracle. And there is an issue we still have no perfect solution.
In Sybase, When insert/update to a target column, the source string will be auto truncate if the length more than the target column definition. For example. when insert "abcde" to a varchar(2) column, Sybase just truncate it and insert "ab" to the column, in Oracle, it will raise the error:
SQL Error: ORA-12899: value too large for column "XXX" (actual: 5, maximum: 2)
12899. 00000 - "value too large for column %s (actual: %s, maximum: %s)"
Since the orignal project have lot of SP and the business logic is complicated ,We tried to find out these kind of case and use Substr. But we still worried about this somewhere we not covered.
Do you have any suggestion on this? Does Oracle has some settings to auto truncate the string just like the Sybase action? Thanks.
and Connor said...
In Sybase, When insert/update to a target column, the source string will be auto truncate if the length more than the target column definitionWow...thats the most bizarre thing I've heard about Sybase.
Not even a trigger is going to help you out here.
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t ( x varchar2(10));
Table created.
SQL>
SQL> insert into t values ('my very long string');
insert into t values ('my very long string')
*
ERROR at line 1:
ORA-12899: value too large for column "MCDONAC"."T"."X" (actual: 19, maximum: 10)
SQL>
SQL> create or replace
2 trigger t_truncate_string
3 before insert or update
4 on t
5 for each row
6 begin
7 :new.x := substr(:new.x,1,10);
8 end;
9 /
Trigger created.
SQL>
SQL> insert into t values ('my very long string');
insert into t values ('my very long string')
*
ERROR at line 1:
ORA-12899: value too large for column "MCDONAC"."T"."X" (actual: 19, maximum: 10)
SQL>
SQL> select * from t;
no rows selected
SQL>
SQL>
You could add a view layer in there, and use an instead-of trigger
SQL> create or replace
2 view t_alternate as select * from t;
View created.
SQL> create or replace
2 trigger t_truncate_string
3 instead of insert
4 on t_alternate
5 for each row
6 begin
7 insert into t values ( substr(:new.x,1,10));
8 end;
9 /
Trigger created.
SQL>
SQL> insert into t_alternate values ('my very long string');
1 row created.
SQL>
SQL> select * from t;
X
----------
my very lo
but instead-of triggers have their own complexities you'll need to deal with.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment