Registration Forgot password?
Remember me
Login through VKontakte
General > Articles > ORA-01461 one way to fix

ORA-01461 one way to fix

Today I catch ORA-01461. For reproducing, please, use following code

create table table1 (

 field1 varchar2(4000 byte)




  -- max size for 

  -- varchar2 in PL/SQL

  l_txt1 varchar2(32767) :=

      lpad('*', 32767, '*');

  -- max size for

  -- varchar2 in SQL

  l_txt2 varchar2(4000) :=

    substr(l_txt1, 1, 4000);


  -- this line rize ORA-01461

  -- fire because variable length more than

  -- field length field1 (described bottom)

  insert into table1(field1)

  values(substr(l_txt1, 1, 4000));


  -- but when we use local variable with

  -- right length (4000 as length field1) all good

  insert into table1(field1)

  values(substr(l_txt1, 1, 4000));




drop table table1;

When I catch error, first of all I look at table structure, but not found any LONG field. Only few varchar2(4000 byte) fields. Suspecting problem with field size limit excess I try change substr on substrb but withput result, after that I used local variable and problem disappeared.

But what is root of problem? Google few minutes I found description on Oracle Forum. As I understand: when Oracle bind variable, it's not calculate result of function substr before calculating length of variable, as result Oracle "think" that variable length is 32767 and this length more than max size for SQL (4000 characters) so Oracle decide using LONG type, which leads to error . When local variable used bind passes rightly and used variable with right data type varchar2 instead of long.

Hope it's help for somebody
Good luck

You can add comment on current page, or on forum page there.Post count 19.

Last comment


how to make sex romantic for my girlfriend jessica jaymes porn gay
teens porn porn hub sex kim kardashian sex tape full video
darth talon porn gay hairy sex young porn tube latina anal
porn shemale sex videos married sexting meme capricorn sextrology male sloppy seconds christmas album
transexual sex latina booty porn ebony mature porn sex tickling
sex tips after prostate removal no sex for a year with wife vista porn sex tablets for male in dubai sex themed gift baskets thai
anal sex sex in bath 18 sexiest sports broadcasters school sex videos hot
porn videos sex bob omb sex gay iwank porn fair
sex meaning in urdu new sex positions to surprise your girlfriend
with pictures sex vs affection how to have sex free hood porn bbw
anal sex sex muvis sex videos on youtube people sexiest men alive dayna vendetta porn sex sister funny sexual words of wisdom kesha porn porn audition had sex no condom pulled out big fat
black women having sex