Registration Forgot password?
Login
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)

);

 

declare

  -- 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);

begin

  -- 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));

end;

/

 

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
Sergii

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

Last comment

17.01.2020
porno real chica busca chico en zaragoza pagina para mujeres solteras contactos mujeres peludas actrices porno rumanas sexo
gratis con viejos sexo gratiz porno russia chico busca chico en cantabria porno gratis hd video porno incesto porno
six descarga gratis videos porno videos de
dibujos porno sexo anal consejos sexo depilado pelicula porno en espanol contacto con mujeres no profesionales porno aleman porno lebico busco sexo en valencia mejores pajinas porno chicas contacto
malaga porno chilenas codigo lyoko porno porno gay
trio conocer mujeres para sexo mujeres sexo murcia mujeres millonarias solteras porno xxxx gratis video
porno gratis videos porno japoneses encuentros sexuales madrid porno mayores sexo en grupo amateur maduras busca chico video
de sexo revistas pornograficas video porno transexual sexo anal con mi novia intercambio
de pareja porno chico busca chico en almeria los simpsons porno sexo sin compromiso valencia erotismo sexo his
09.07.2016
If your artelcis are always this helpful, "I'll be back."
Игорь
31.03.2016
Спасибо, помогло разобраться.

Name*E-mail
Code*