This content has been marked as final. Show 10 replies
The best way would be to upgrade to 10G where you get improved error messages like this:
ORA-12899: value too large for column "SCOTT"."EMP"."ENAME" (actual: 12, maximum: 10)
Failing that I think all you can do is test all the data in PL/SQL before trying to insert it.
Thanks a lot for your immediate reply.
we are using Oracle 9i.
I'm able to find which column it is. But some times we dont know data behaviour some time if may get some asci characters or some how if we can get big string its a problem, i want to handle this in exception part. Thats my concern.
Once again thanks a lot.
Create user defined exception using PRAGMA EXCEPTION_INIT
PRAGMA EXCEPTION_INIT (my_exception, -1401);
insert into table () values ..............
WHEN my_exception THEN dbms_output.put_line('Column value too long');
How is that supposed to help?
I am also facing the same problem as you stated earlier.Would you please help me in solving the below problem .What exact solution you used to solve the problem.
Problem as stated:
Is there any way to find out which column is causing inserted value too large for column the problem? I'm trying to insert the data into a table by using cursor into a table in a procedure. My table is having 80 columns.
Why are you reviving an old thread?
Given that his last post in this thread is > 2 years old he could very well be reborn as a turtle since.
Follow the advice given above. Update to 10g. If that's not possible: good luck.
i saw ur reply in this forum.. i am facing a problem in this regard, plz help me
actually i am passing 10 parameters in a procedure. these values are GOING TO insert in one table. when i get any errorr i just want to know in which column error is araise... i know to write exceptions for any error, but i dont know how to find this error in that particular column (reside)..
I guess you are on 9i? - then you are just out of luck.
Oracle has come up with some improvements on this in 10g. (yet another reason to upgrade)
you can print : DBMS_OUPUT.PUT_LINE('MESSAGE'||SQLERRM);
SQLERR : Contains the last error raised.
As someone said, above: What would that help?
Allow me to demonstrate the problem.
So, which column was it?
9i:SQL> create table t (x varchar(10), y varchar2(10)); Tabel er oprettet. SQL> SQL> insert into t select object_name, object_type from all_objects; insert into t select object_name, object_type from all_objects * FEJL i linie 1: ORA-01401: value too large
(*Looks like SQL*Plus knows, Perhaps because I faked it running in 10g, what will it look like in 9i, anyone?*)
Where as in 10g:
10g:SQL> create table t (x varchar(10), y varchar2(10)); Tabel er oprettet. SQL> SQL> insert into t select object_name, object_type from all_objects; insert into t select object_name, object_type from all_objects * FEJL i linie 1: ORA-12899: value too large for column "SCOTT"."T"."X" (actual: 16, maximum: 10)