Find column for ORA-01401: inserted value too large for column

[JtheRipper]

Hi there,

Is there an easy way to find the problem column/error record of the above mentioned error? I just do not want to go through all 30 columns and it is especially a problem if you need to insert millions of records.

Thanks,
J.

 

[lewisp]

How are you doing the insert? Through a PL/SQL script, import or an INSERT/SELECT for example?

 

[JtheRipper]

Hi,

Insert/Select command. But I am sure it can be changed if needed to run in PL/SQL.

Thanks,
J.

 

[Turkbear]

Hi,
Can you compare the metadata ( specifically field size) of the inserted columns and compare them to the ones you are trying to fit them into:
( DESC Source_table, then DESC target_table)

Or change your Insert into <table> to use
select substr(source_field,1,size_of_target_field), etc..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

...or

SELECT rowid from <source_table>
WHERE length(longer-defined col-x in <source_table>) >
      <DESCRIBEd length of col-x in <target_table>)
   OR length(longer-defined col-x in <source_table>) >
      <DESCRIBEd length of col-x in <target_table>)
et cetera

That will give you a specific list of rows to display that you know are offending on INSERT.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[JtheRipper]

Thanks for all the feedback guys. I will try SantaMufasa's solution as well.

I also got the following ida from metalink for those interested:

create procedure (or a write trigger) 
create or replace procedure ep(n1 number,v1 varchar) as 
error_variable varchar2(30); 
error_value varchar2(60); 
num1 tab_test.num%type; 
varc1 tab_test.varc%type; 
begin 
error_variable :='num'; 
error_value := n1; 
num1 := n1; 
error_variable :='varc'; 
error_value := v1; 
varc1 := v1; 
insert into tab_test(num,varc) values(n1,v1); 
commit; 
exception when others then 
insert into error_table(error_variable,error_values) values (error_variable,error_value); 
end;

Thanks,
J.