how do i fix the error ORA-01401: inserted value too large for column in PL/SQL

[Trisha Cardenas]

INSERT INTO sm3pm
SELECT 'MANILA',
group_nm,
upper(theater_desc),
null,
upper(movie_title),
nvl(gross_amt,0),
v_mladt,
sysdate
from mlasm_details@hs_sm3pm_xls
where group_nm is not null
and movie_title is not null
and group_nm <> 'MOVIE_TITLE';

COMMIT;

update sm3pm_gross x
set theater_cd = (select max(theater_cd)
from stg_bds_theater_mstr
where trim(upper(theater_name)) = trim(upper(x.theater)))
where area = 'MANILA'
and theater_cd is null;

[rski]

You need to show us table definitions. It looks like you try to put value that do not match column type.

[amitpatel66]

The problem is with your Insert statement. Just check the length of all the columns of the table sm3pm and make sure the values that you are trying to insert are within the maximum length allowed for the columns.

I suspect columns like theater_Desc and movie_title are causing the problem.

It would be helpful if you can post the table structure and the output of the below query:

 
SQL> SELECT group_nm,LENGTH(group_nm)
upper(theater_desc),LENGTH(theater_desc),
null,
upper(movie_title),LENGTH(movie_title),
nvl(gross_amt,0),
v_mladt,LENGTH(v_mladt),
sysdate
from mlasm_details@hs_sm3pm_xls
where group_nm is not null
and movie_title is not null
and group_nm <> 'MOVIE_TITLE'
/