我有一个下面的包，它给出了错误

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

请让我知道这里出了什么问题。

CREATE OR REPLACE PACKAGE BODY PKG_H

IS

PROCEDURE PROC_SUBMIT_H

(

Pout_Rqst_Id OUT NVARCHAR2,

Pout_err_cd OUT VARCHAR2,

Pout_err_msg OUT VARCHAR2,

Pin_Rqst_Type_Id IN NUMBER,

Pin_Attachment IN NVARCHAR2,

Pin_Brand_Id IN NVARCHAR2,

Pin_Prop_Id IN NVARCHAR2,

-- Pin_Htl_Stat_Rqst_Typ_ID IN NUMBER,

Pin_Orcl_Acct_Num IN NVARCHAR2, -- NUMBER, /* Changed on 22.09.2011,as stated by FIS Team */

Pin_ORCL_User_Name IN NVARCHAR2,

Pin_Rstn_Id IN NUMBER,

Pin_Rstn_Name IN NVARCHAR2,

Pin_Rstn_Start_Date IN DATE,

Pin_Rstn_End_Date IN DATE,

-- Pin_Change_Type_Ind IN NVARCHAR2,

Pin_Trans_Time_Orcl IN TIMESTAMP,

Pin_Fis_Acct_Stat_Prsnt_Id IN NUMBER,

Pin_Fis_Acct_Future_Stat IN NUMBER,

Pin_Auto_Ind IN NVARCHAR2,

-- Pin_Stat_Change_Resn_ID IN NUMBER, /* changed due to ETL requirement as on 17.09.2011 */

Pin_Stat_Change_Resn_Desc IN NVARCHAR2, /* changed due to ETL requirement as on 17.09.2011 */

Pin_Brand_Dot_Com_Ind IN NVARCHAR2,

-- Pin_Expdt_Ind IN NVARCHAR2,

-- Pin_Expdt_Dt IN DATE,

Pin_Rqstr_Id IN NVARCHAR2,

Pin_Impn_Id IN NUMBER,

-- Pin_Agent_Id IN NVARCHAR2, /* Changed as on 22.09.2011 */

-- Pin_Agent_Name IN NVARCHAR2, /* Changed as on 22.09.2011 */

Pin_File_Name IN NVARCHAR2,

Pin_Prov_Date IN DATE

-- Pin_Rqst_Stat_ID IN NUMBER

-- Pin_Prov_Time IN DATE

)

IS

-- lv_err_cd VARCHAR2(10);

-- lv_err_msg VARCHAR2(4000);

Ln_Cnt NUMBER;

DUP_VAL EXCEPTION;

lv_rqst_id NVARCHAR2(20);

Ln_rqst_stat_id_it NUMBER;

Ln_rqst_stat_id_Q NUMBER;

Ln_rqst_category_id NUMBER;

Ln_Stat_Change_Resn_Id NUMBER;

-- Ln_Htl_Stat_Rqst_Typ_Id NUMBER;

lt_data_01 STRINGTABLETYPE := STRINGTABLETYPE();

lt_data_02 STRINGTABLETYPE := STRINGTABLETYPE();

BEGIN

SELECT fnc_gen_request_id

INTO Lv_rqst_id

FROM dual;

SELECT rqst_stat_id

INTO Ln_rqst_stat_id_it

FROM rqst_stat_mst

WHERE rqst_stat_desc = 'In Transmit';

SELECT rqst_stat_id

INTO Ln_rqst_stat_id_Q

FROM rqst_stat_mst

WHERE rqst_stat_desc = 'Pending';

SELECT COUNT(1)

INTO Ln_Cnt

FROM HOTEL_STAT_RQST

WHERE FILE_NAME=Pin_File_Name;

IF Ln_Cnt >0 then

RAISE DUP_VAL;

END IF;

IF Pin_Stat_Change_Resn_Desc IS NOT NULL THEN

SELECT STAT_CHANGE_RESN_ID

INTO Ln_Stat_Change_Resn_Id

FROM STAT_CHANGE_RESN_MST

WHERE UPPER(STAT_CHANGE_RESN_DESC)=UPPER(TRIM(Pin_Stat_Change_Resn_Desc));

END IF;

DELETE

FROM HOTEL_STAT_RQST

WHERE RQST_ID=lv_rqst_id;

INSERT INTO HOTEL_STAT_RQST

(RQST_ID

,RQST_TYPE_ID

,RQST_STAT_ID

,BRAND_ID

,PROPERTY_ID

,STAT_CHANGE_RESN_ID

-- ,HOTEL_STAT_RQST_TYPE_ID

,ORCL_ACCT_NUM

,ORCL_USER_NAME

,TRANS_TIME_ORCL

,FIS_ACCOUNT_STATUS_PRESENT_ID

,FIS_ACCT_FUTURE_STAT

,RSTCTN_ID

,RSTCTN_NAME

,RSTCTN_STRT_DT

,RSTCTN_END_DT

-- ,RSTCTN_PREV_STRT_DT /* SCHEMA CHANGED */

-- ,RSTCTN_PREV_END_DT /* SCHEMA CHANGED */

-- ,PREV_RSTN_ID /* SCHEMA CHANGED */

,AUTO_IND

-- ,CHANGE_TYPE_IND

,BRAND_DOT_COM_IND

,RQSTR_ID

,IMPN_ID

,EXPDT_IND

,EXPDT_DT

-- ,PROVSN_STAT /* SCHEMA CHANGED */

-- ,PROVSN_TIME /* SCHEMA CHANGED */

,CREATED_ON

,UPDATED_BY

,UPDATED_ON

,FILE_NAME

,PROV_DATE

)

VALUES

(

lv_rqst_id

,Pin_Rqst_Type_Id

,Ln_rqst_stat_id_it

,Pin_Brand_Id

,Pin_Prop_Id

,Ln_Stat_Change_Resn_Id /* changed due to ETL requirement as on 17.09.2011 */

-- ,Pin_Htl_Stat_Rqst_Typ_ID

,Pin_Orcl_Acct_Num

,Pin_ORCL_User_Name

,Pin_Trans_Time_Orcl

,Pin_Fis_Acct_Stat_Prsnt_Id

,Pin_Fis_Acct_Future_Stat

,Pin_Rstn_Id

,Pin_Rstn_Name

,Pin_Rstn_Start_Date

,Pin_Rstn_End_Date

-- ,NULL /* SCHEMA CHANGED */

-- ,NULL /* SCHEMA CHANGED */

-- ,NULL /* SCHEMA CHANGED */

,Pin_Auto_Ind

-- ,Pin_Change_Type_Ind

,Pin_Brand_Dot_Com_Ind

,Pin_Rqstr_Id

,Pin_Impn_Id

,NULL

,NULL

-- ,NULL /* SCHEMA CHANGED */

-- ,Pin_Prov_Time /* SCHEMA CHANGED */

,SYSDATE

,Pin_Rqstr_Id

,SYSDATE

,Pin_File_Name

,Pin_Prov_Date

);

IF Pin_Attachment IS NOT NULL THEN

DELETE

FROM attach_ref

WHERE rqst_id=lv_rqst_id;

SELECT CAST(SPLIT(Pin_Attachment,'|') AS STRINGTABLETYPE) INTO lt_data_01 FROM DUAL;

FOR i_outer IN 1..lt_data_01.COUNT LOOP

SELECT CAST(SPLIT(lt_data_01(i_outer),'~')AS STRINGTABLETYPE) INTO lt_data_02 FROM DUAL;

INSERT INTO attach_ref

(

rqst_id,

attach_id, -- SEQUENCE

attach_ind,

attach_file_name,

file_path,

ord_num

)

VALUES(

lv_rqst_id,

attach_id_seq.NEXTVAL, -- SEQUENCE

'REQUESTOR',

lt_data_02(1),

lt_data_02(2),

i_outer

);

END LOOP;

END IF;

DELETE FROM rqst_queue WHERE rqst_id=lv_rqst_id;

INSERT INTO rqst_queue

(

rqst_id,

prnt_rqst_id,

queu_start_time,

queu_end_time,

agnt_id,

agnt_name,

property_id,

src_sys,

tgt_sys,

queu_stat_ind

)

VALUES

(

lv_rqst_id,

NULL,

SYSDATE,

NULL,

NULL,

NULL,

Pin_Prop_Id,

'RQT',

'SFDC',

Ln_rqst_stat_id_Q

);

IF Pin_Rqst_Type_Id IS NOT NULL THEN

SELECT rqst_category_id

INTO ln_rqst_category_id

FROM rqst_type_mst

WHERE rqst_type_id = Pin_Rqst_Type_Id;

END IF;

DELETE

FROM rqst_sumry

WHERE rqst_id = lv_rqst_id;

INSERT INTO rqst_sumry

(

rqst_id,

rqst_type_id,

prnt_rqst_id,

brand_id,

property_id,

expdt_ind,

expdt_dt,

rqstr_id,

rqst_stat_id,

compln_dt,

estm_compln_time,

rqst_category_id,

submission_dt

)

VALUES

(

lv_rqst_id,

Pin_Rqst_Type_Id,

NULL,

Pin_Brand_Id,

Pin_Prop_Id,

NULL,

NULL,

Pin_Rqstr_Id,

Ln_rqst_stat_id_it,

NULL,

NULL,

ln_rqst_category_id,

SYSDATE

);

COMMIT;

Pout_Rqst_Id := lv_rqst_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

ROLLBACK;

Pout_err_cd := SQLCODE;

Pout_err_msg := 'STAT_CHANGE_RESN_MISMATCH';

WHEN DUP_VAL THEN

Pout_err_cd := SQLCODE;

Pout_err_msg := 'DUPLICATE';

WHEN OTHERS THEN

ROLLBACK;

Pout_err_cd := SQLCODE;

Pout_err_msg := SUBSTR(SQLERRM, 1 , 4000);

END PROC_SUBMIT_H;

END PKG_H;

/