| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> ORA-06502: PL/SQL: numeric or value error: character string buffer too small
I'm working under Oracle 10g R2.
I have a table function which is giving me the error below. I've checked
all the variable/field sizes
and the sizes all match. I'm not entirely sure if this error is referring
to a single field or the
whole record which gets sent back.
If the buffer IS filling up what can I do to prevent it so the function will
work and not abort with
the error?
ORA-06512: at "BCN_HMP.HMP_REF_PRESCRIPT_05", line 255 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 2 -- -------------------------------------------------------------------------
--------------------------------
FUNCTION load_ref_prescript05_clm_rx RETURN ref_prescript_05_tab PIPELINED
CURSOR cr_cursor
IS
SELECT a.person_id,
a.mbr_key,
b.hmp_disease_code,
a.natl_drug_code,
a.pch_date,
SUM(a.alt_prsc_qty) AS alt_prsc_qty,
MAX(a.bus_unit_num) AS bus_unit_num,
0 AS canister_cnt,
a.contract_num,
SUM(a.daily_supply_num) AS daily_supply_num,
MAX(a.docu_num) AS docu_num,
a.file_source_code,
a.gen_cd_num,
a.mbr_sys_key,
a.mem_num_id,
SUM(a.pmt_amt) AS pmt_amt,
MAX(a.prov_num) AS prov_num,
SUM(a.qty_disp_num) AS qty_disp_num,
SUM(a.rx_cnt_num) AS rx_cnt_num,
0 AS script_cnt,
b.service_type_code,
b.spfc_thpy_class_code,
MAX(a.updt_date) AS updt_date,
MAX(a.yr_mth) AS yr_mth
FROM clm_rx_temp a,
ref_ndc_temp b
WHERE a.natl_drug_code = b.natl_drug_code
GROUP BY a.person_id,
a.mbr_key,
b.hmp_disease_code,
a.natl_drug_code,
a.pch_date,
a.contract_num,
a.file_source_code,
a.gen_cd_num,
a.mbr_sys_key,
a.mem_num_id,
b.service_type_code,
b.spfc_thpy_class_code
HAVING SUM(a.daily_supply_num) > 0
AND SUM(a.pmt_amt) > 0;
v_out ref_prescript_05_obj := ref_prescript_05_obj(
NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL );
v_in cr_cursor%ROWTYPE;
BEGIN OPEN cr_cursor;
LOOP
FETCH cr_cursor INTO v_in;
EXIT WHEN cr_cursor%NOTFOUND;
v_out.person_id := v_in.person_id;
v_out.mbr_key := v_in.mbr_key;
v_out.hmp_disease_code := v_in.hmp_disease_code;
v_out.natl_drug_code := v_in.natl_drug_code;
v_out.pch_date := v_in.pch_date;
v_out.alt_prsc_qty := v_in.alt_prsc_qty;
v_out.bus_unit_num := v_in.bus_unit_num;
v_out.canister_cnt := v_in.canister_cnt;
v_out.contract_num := v_in.contract_num;
v_out.daily_supply_num := v_in.daily_supply_num;
v_out.docu_num := v_in.docu_num;
v_out.file_source_code := v_in.file_source_code;
v_out.gen_cd_num := v_in.gen_cd_num;
v_out.mbr_sys_key := v_in.mbr_sys_key;
v_out.mem_num_id := v_in.mem_num_id;
v_out.pmt_amt := v_in.pmt_amt;
v_out.prov_num := v_in.prov_num;
v_out.qty_disp_num := v_in.qty_disp_num;
v_out.rx_cnt_num := v_in.rx_cnt_num;
v_out.script_cnt := v_in.script_cnt;
v_out.service_type_code := v_in.service_type_code;
v_out.spfc_thpy_class_code := v_in.spfc_thpy_class_code;
v_out.updt_date := v_in.updt_date;
v_out.yr_mth := v_in.yr_mth;
PIPE ROW ( v_out );
END LOOP; CLOSE cr_cursor;
RETURN;
EXCEPTION
WHEN OTHERS THEN
global.sql_error('hmp_ref_prescript_05.load_ref_prescript05_clm_rx',SQLCODE)
;
RAISE global.e_unhandled_exception;
END load_ref_prescript05_clm_rx;
PROCEDURE build_ref_prescript_clm_rx
IS
BEGIN INSERT INTO ref_prescript_05
SELECT person_id,
mbr_key,
hmp_disease_code,
natl_drug_code,
pch_date,
alt_prsc_qty,
bus_unit_num,
canister_cnt,
contract_num,
daily_supply_num,
docu_num,
file_source_code,
gen_cd_num,
mbr_sys_key,
mem_num_id,
pmt_amt,
prov_num,
qty_disp_num,
rx_cnt_num,
script_cnt,
service_type_code,
spfc_thpy_class_code,
updt_date,
yr_mth
FROM TABLE ( load_ref_prescript05_clm_rx() )
COMMIT;
EXCEPTION
WHEN OTHERS THEN
global.sql_error('hmp_ref_prescript_05.build_ref_prescript_clm_rx',SQLCODE);
RAISE global.e_unhandled_exception;
END build_ref_prescript_clm_rx;
 |
 |