Re: ora-01401 error

~John wrote:

>I must be nuts. I can't find the column thats to long. Anyone see the
>error here? The data column I have added to right of the desc for ease
>of reading...
>The long datatype column is null so it can't be converting any
>characters in a wack fashion. There is a materialized view on this
>table and I get the same error attempting to insert into it...
>no triggers involved...
>db version is oracle 8.1.7
>
>SYSADM/CSAPHA>INSERT INTO ps_audit_employmnt
>(AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,EMPLID,
> 2 EMPL_RCD_NBR,BENEFIT_RCD_NBR,HIRE_DT,REHIRE_DT,CMPNY_SENIORITY_DT,SERVICE_DT,
> 3 EXPECTED_RETURN_DT,TERMINATION_DT,LAST_DATE_WORKED,SUITABLE_FOR_REHIR,
> 4 OWN_5PERCENT_CO,REFERRAL_SOURCE,BUSINESS_TITLE,FT_STUDENT,SUPERVISOR_ID,
> 5 UNITED_WAY_AREACD,UNION_CD,BARG_UNIT,UNION_SENIORITY_DT,PROBATION_DT,
> 6 CREDIT_ASSOC_ID,SECURITY_CLEARANCE,WORK_PHONE,PROFILE_UPDATE_DT,
> 7 LAST_PROFILE_DT,TIME_RPT_LOC,JOB_REPORTING,DED_TAKEN,PAYCHECK_DIST_OPTN,
> 8 PAYCHECK_ADDR_OPTN,PAYCHECK_LOCN_OPTN,LOCATION,MAIL_DROP,PAYCHECK_NAME,
> 9 STREET1,STREET2,STREET3,CITY,COUNTRY,STATE,ZIP,BADGE_ID,CLASS_NAME,
> 10 CLOCK_ID,RESTRICTION_NAME,CUSTOM_AREA1,CUSTOM_AREA2,CUSTOM_AREA3,
> 11 CUSTOM_AREA4,DATE_A,DATE_B,DATE_C,DATE_D,FLAG_1,FLAG_2,FLAG_3,FLAG_4,
> 12 FLAG_5,FLAG_6,FLAG_7,MAPPER_ZEROS,USER_FIELD_1,USER_FIELD_2,LABOR_ACCOUNT,
> 13 ACQUISITION_DT,ELIGIBLE_FOR_SEVER,RESIGN_NOTICE_DT,RETIRE_DT,COMMENTS)
> 14 VALUES ('ADIJON',SYSDATE,'A','3716',0,0,TO_DATE('26-SEP-03','DD-MON-YY'),
> 15 NULL,TO_DATE('26-SEP-03','DD-MON-YY'),TO_DATE('26-SEP-03','DD-MON-YY'),
> 16 NULL,NULL,NULL,'Y','N',' ',' ','N',' ',' ',' ',' ',NULL,NULL,'
>',' ',' ',NULL,
> 17 NULL,' ','N','D','I ','H','D',' ',' ',' ','5sdfa',' ','
>','sadf','USA',
> 18 'CO','23452',' ',' ',' ',' ',' ',' ',' ','
>',NULL,NULL,NULL,NULL,' ',' ',
> 19 ' ',' ','N',' ',' ','0000000000',' ',' ','
>',NULL,'N',NULL,NULL,NULL);
>
>
>INSERT INTO ps_audit_employmnt
>(AUDIT_OPRID,AUDIT_STAMP,AUDIT_ACTN,EMPLID,
> *
>ERROR at line 1:
>ORA-01401: inserted value too large for column
>
>
>SYSADM/CSAPHA>DESC ps_audit_employmnt
> Name Null? Type DATA
> ------------------------------- -------- ----
> AUDIT_OPRID NOT NULL VARCHAR2(8) 'ADIJON'
> AUDIT_STAMP DATE SYSDATE
> AUDIT_ACTN NOT NULL VARCHAR2(1) 'A'
> EMPLID NOT NULL VARCHAR2(11) '3716'
> EMPL_RCD_NBR NOT NULL NUMBER(4) 0
> BENEFIT_RCD_NBR NOT NULL NUMBER(4) 0
> HIRE_DT DATE
>TO_DATE('26-SEP-03','DD-MON-YY')
> REHIRE_DT DATE NULL
> CMPNY_SENIORITY_DT DATE
>TO_DATE('26-SEP-03','DD-MON-YY')
> SERVICE_DT DATE
>TO_DATE('26-SEP-03','DD-MON-YY')
> EXPECTED_RETURN_DT DATE NULL
> TERMINATION_DT DATE NULL
> LAST_DATE_WORKED DATE NULL
> SUITABLE_FOR_REHIR NOT NULL VARCHAR2(1) 'Y'
> OWN_5PERCENT_CO NOT NULL VARCHAR2(1) 'N'
> REFERRAL_SOURCE NOT NULL VARCHAR2(2) ' '
> BUSINESS_TITLE NOT NULL VARCHAR2(30) ' '
> FT_STUDENT NOT NULL VARCHAR2(1) 'N'
> SUPERVISOR_ID NOT NULL VARCHAR2(11) ' '
> UNITED_WAY_AREACD NOT NULL VARCHAR2(2) ' '
> UNION_CD NOT NULL VARCHAR2(4) ' '
> BARG_UNIT NOT NULL VARCHAR2(1) ' '
> UNION_SENIORITY_DT DATE NULL
> PROBATION_DT DATE NULL
> CREDIT_ASSOC_ID NOT NULL VARCHAR2(10) ' '
> SECURITY_CLEARANCE NOT NULL VARCHAR2(1) ' '
> WORK_PHONE NOT NULL VARCHAR2(15) ' '
> PROFILE_UPDATE_DT DATE NULL
> LAST_PROFILE_DT DATE NULL
> TIME_RPT_LOC NOT NULL VARCHAR2(6) ' '
> JOB_REPORTING NOT NULL VARCHAR2(1) 'N'
> DED_TAKEN NOT NULL VARCHAR2(1) 'D'
> PAYCHECK_DIST_OPTN NOT NULL VARCHAR2(1) 'I '
> PAYCHECK_ADDR_OPTN NOT NULL VARCHAR2(1) 'H'
> PAYCHECK_LOCN_OPTN NOT NULL VARCHAR2(1) 'D'
> LOCATION NOT NULL VARCHAR2(5) ' '
> MAIL_DROP NOT NULL VARCHAR2(50) ' '
> PAYCHECK_NAME NOT NULL VARCHAR2(40) ' '
> STREET1 NOT NULL VARCHAR2(40) '5sdfa'
> STREET2 NOT NULL VARCHAR2(40) ' '
> STREET3 NOT NULL VARCHAR2(40) ' '
> CITY NOT NULL VARCHAR2(30) 'sadf'
> COUNTRY NOT NULL VARCHAR2(3) 'USA'
> STATE NOT NULL VARCHAR2(2) 'CO'
> ZIP NOT NULL VARCHAR2(10) '23452'
> BADGE_ID NOT NULL VARCHAR2(10) ' '
> CLASS_NAME NOT NULL VARCHAR2(15) ' '
> CLOCK_ID NOT NULL VARCHAR2(15) ' '
> RESTRICTION_NAME NOT NULL VARCHAR2(15) ' '
> CUSTOM_AREA1 NOT NULL VARCHAR2(10) ' '
> CUSTOM_AREA2 NOT NULL VARCHAR2(10) ' '
> CUSTOM_AREA3 NOT NULL VARCHAR2(10) ' '
> CUSTOM_AREA4 NOT NULL VARCHAR2(10) ' '
> DATE_A DATE NULL
> DATE_B DATE NULL
> DATE_C DATE NULL
> DATE_D DATE NULL
> FLAG_1 NOT NULL VARCHAR2(1) ' '
> FLAG_2 NOT NULL VARCHAR2(1) ' '
> FLAG_3 NOT NULL VARCHAR2(1) ' '
> FLAG_4 NOT NULL VARCHAR2(1) ' '
> FLAG_5 NOT NULL VARCHAR2(1) 'N'
> FLAG_6 NOT NULL VARCHAR2(1) ' '
> FLAG_7 NOT NULL VARCHAR2(1) ' '
> MAPPER_ZEROS NOT NULL VARCHAR2(10) '0000000000'
> USER_FIELD_1 NOT NULL VARCHAR2(15) ' '
> USER_FIELD_2 NOT NULL VARCHAR2(15) ' '
> LABOR_ACCOUNT NOT NULL VARCHAR2(20) ' '
> ACQUISITION_DT DATE NULL
> ELIGIBLE_FOR_SEVER NOT NULL VARCHAR2(1) 'N'
> RESIGN_NOTICE_DT DATE NULL
> RETIRE_DT DATE NULL
> COMMENTS LONG NULL
>
>

Not without spending a lot of time. Remove the NOT NULLs, insert a single character into each VARCHAR2,
a single digit into every NUMBER, and SYSDATE into each date and then slowly add back your parameters
until you break it.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)