Error Message when i try to excute the procedure by passing some input parameter

[praveena]

Hi,

I got the Following Error Message when i try to excute the procedure by passing some input parameter,This procedure will insert the input parameter to a table.
ERROR at line 1:
ORA-01401: inserted value too large for column
ORA-06512: at "TP2.T1_INS", line 44
ORA-06512: at line 73

By seeing the error message i thought the values which iam passing to the procedure is large for that column in the table but to my wonder all the column size in the table are large.

Please help me in resolving the issue.

Step 1:

I created one table with following columns:
CREATE TABLE T1(
CI_LOC_NAME VARCHAR2 (1020) NOT NULL,
CREATED_BY VARCHAR2 (1020) NOT NULL,
CREATED_ID CHAR (20) NOT NULL,
CREATED_ON DATE NOT NULL,
FLAGS CHAR (10) NOT NULL,
ID CHAR (20) NOT NULL,
LOC_NAME VARCHAR2 (1020) NOT NULL,
LOC_NO VARCHAR2 (100) NOT NULL,
TIME_STAMP VARCHAR2 (100) NOT NULL,
UPDATED_BY VARCHAR2 (1020) NOT NULL,
UPDATED_ON DATE NOT NULL,
TIMEZONE_ID CHAR (20) NOT NULL,
CONTACT_ID CHAR (20),
CUSTOM0 VARCHAR2 (1020),
CUSTOM1 VARCHAR2 (1020),
CUSTOM2 VARCHAR2 (1020),
CUSTOM3 VARCHAR2 (1020),
CUSTOM4 VARCHAR2 (1020),
DEPT_ID CHAR (20),
EMAIL VARCHAR2 (1020),
FAX VARCHAR2 (100),
PHONE1 VARCHAR2 (100),
PHONE2 VARCHAR2 (100),
CUSTOM5 VARCHAR2 (1020),
CUSTOM6 VARCHAR2 (1020),
CUSTOM7 VARCHAR2 (1020),
CUSTOM8 VARCHAR2 (1020),
CUSTOM9 VARCHAR2 (1020),
SPLIT VARCHAR2 (100),
CONSTRAINT TPPK_LOCATIONS_ID
PRIMARY KEY ( ID ) ) ;


Step 2 :
and try to insert into this table using procedure :

CREATE OR REPLACE procedure t1_ins
( xid IN CHAR,
xtime_stamp IN VARCHAR2,
xloc_no IN VARCHAR2,
xloc_name IN VARCHAR2,
xenabled IN CHAR,
xcreated_id IN CHAR,
xcreated_by IN VARCHAR2,
xcreated_on IN DATE,
xupdated_by IN VARCHAR2,
xupdated_on IN DATE,
xsplit IN VARCHAR2,
xaddr1 IN VARCHAR2,
xaddr2 IN VARCHAR2,
xcity IN VARCHAR2,
xState IN VARCHAR2,
xzip IN VARCHAR2,
xcountry IN VARCHAR2,
xcustom0 IN VARCHAR2,
xcustom1 IN VARCHAR2,
xcustom2 IN VARCHAR2,
xcustom3 IN VARCHAR2,
xcustom4 IN VARCHAR2,
xcontact_id IN CHAR,
xphone1 IN VARCHAR2,
xphone2 IN VARCHAR2,
xemail IN VARCHAR2,
xfax IN VARCHAR2,
xdept_id IN CHAR,
xtimezone_id IN CHAR,
xcustom5 IN VARCHAR2,
xcustom6 IN VARCHAR2,
xcustom7 IN VARCHAR2,
xcustom8 IN VARCHAR2,
xcustom9 IN VARCHAR2,
xnewts IN VARCHAR2 ) as
xxaddr_id CHAR(20);
xxgen_loc_no varchar2(100);
xxflags CHAR(10);
begin
xxgen_loc_no := xloc_no;
xxflags :='1';
fgp_generate_get ('cpatr000000000001682', 0);
insert into t1
(id,
time_stamp,
loc_no,
loc_name,
flags,
created_id,
created_by,
created_on,
updated_by,
updated_on,
contact_id,
phone1,
phone2,
email,
fax,
custom0,
custom1,
custom2,
custom3,
custom4,
split,
dept_id,
ci_loc_name,
timezone_id,
custom5,
custom6,
custom7,
custom8,
custom9)
values
(
xid,
xnewts,
upper(xxgen_loc_no),
xloc_name, xxflags,
xcreated_id,
xcreated_by,
sysdate,
xupdated_by,
sysdate,
xcontact_id,
xphone1,
xphone2,
xemail,
xfax,
xcustom0,
xcustom1,
xcustom2,
xcustom3, xcustom4, xsplit, xdept_id, lower(xloc_name), xtimezone_id, xcustom5, xcustom6, xcustom7,xcustom8, xcustom9); end;
/

Step 3:
Executed the procedure by passing some values:
DECLARE
XID CHAR(200);
XTIME_STAMP VARCHAR2(200);
XLOC_NO VARCHAR2(200);
XLOC_NAME VARCHAR2(200);
XENABLED CHAR(200);
XCREATED_ID CHAR(200);
XCREATED_BY VARCHAR2(200);
XCREATED_ON DATE;
XUPDATED_BY VARCHAR2(200);
XUPDATED_ON DATE;
XSPLIT CHAR(200);
XADDR1 VARCHAR2(200);
XADDR2 VARCHAR2(200);
XCITY VARCHAR2(200);
XSTATE VARCHAR2(200);
XZIP VARCHAR2(200);
XCOUNTRY VARCHAR2(200);
XCUSTOM0 VARCHAR2(200);
XCUSTOM1 VARCHAR2(200);
XCUSTOM2 VARCHAR2(200);
XCUSTOM3 VARCHAR2(200);
XCUSTOM4 VARCHAR2(200);
XCONTACT_ID CHAR(200);
XPHONE1 VARCHAR2(200);
XPHONE2 VARCHAR2(200);
XEMAIL VARCHAR2(200);
XFAX VARCHAR2(200);
XDEPT_ID CHAR(200);
XTIMEZONE_ID CHAR(200);
XCUSTOM5 VARCHAR2(200);
XCUSTOM6 VARCHAR2(200);
XCUSTOM7 VARCHAR2(200);
XCUSTOM8 VARCHAR2(200);
XCUSTOM9 VARCHAR2(200);
XNEWTS VARCHAR2(200);
BEGIN
XID := 'l';
XTIME_STAMP := '2';
XLOC_NO :='17';
XLOC_NAME := 'test1';
XENABLED := '1';
XCREATED_ID := 'e';
XCREATED_BY := 'a';
XCREATED_ON := sysdate;
XUPDATED_BY := 'a';
XUPDATED_ON := sysdate;
XSPLIT := 'd';
XADDR1 := NULL;
XADDR2 := NULL;
XCITY := NULL;
XSTATE := NULL;
XZIP := NULL;
XCOUNTRY := NULL;
XCUSTOM0 := NULL;
XCUSTOM1 := NULL;
XCUSTOM2 := NULL;
XCUSTOM3 := NULL;
XCUSTOM4 := NULL;
XCONTACT_ID := NULL;
XPHONE1 := NULL;
XPHONE2 := NULL;
XEMAIL := NULL;
XFAX := NULL;
XDEPT_ID := NULL;
XTIMEZONE_ID := 't';
XCUSTOM5 := NULL;
XCUSTOM6 := NULL;
XCUSTOM7 := NULL;
XCUSTOM8 := NULL;
XCUSTOM9 := NULL;
XNEWTS := '2';
TP2.TPP_LOCATIONS_INS
( XID, XTIME_STAMP, XLOC_NO, XLOC_NAME, XENABLED, XCREATED_ID, XCREATED_BY, XCREATED_ON, XUPDATED_BY, XUPDATED_ON, XSPLIT, XADDR1, XADDR2, XCITY, XSTATE, XZIP, XCOUNTRY, XCUSTOM0, XCUSTOM1, XCUSTOM2, XCUSTOM3, XCUSTOM4, XCONTACT_ID, XPHONE1, XPHONE2, XEMAIL, XFAX, XDEPT_ID, XTIMEZONE_ID, XCUSTOM5, XCUSTOM6, XCUSTOM7, XCUSTOM8, XCUSTOM9, XNEWTS );
END;
/

[davey23uk]

you need to manually work out which column is causing you the problem, as one of them is

[praveena]

Hi ,

Thanks for your help.I found the error,It is not becuase of the Column .The way i executed the procedure was wrong.When I uesd the following statement it worked fine.

exec tpp_locations_ins ( '2', '2', '17', 'test1', '1', 'e', 'a', sysdate, 'a', sysdate, 'd', NULL, NULL, NULL, NULL, nULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, nULL, nULL, NULL,'tzone000000000000002', NULL, NULL, NULL, NULL, NULL, '2') ;

Regards,
Praveena