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

Dereck L. Dietz wrote:
> 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
> IS
>
> 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;
>
> -- -------------------------------------------------------------------------

Without line numbers in your code I'm not going to try to figure out where it is happening ... but suffice to say that it is and you are concatenating something, or aggregating something, and Oracle is giving it to you straight.

That said what is the point of doing any of this as a Pipelined Table Function?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org