Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

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

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 02 Nov 2006 18:05:36 -0800
Message-ID: <1162519532.700982@bubbleator.drizzle.com>


Dereck L. Dietz wrote:

> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1162510808.155487_at_bubbleator.drizzle.com...
>> 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
> 
> What I don't understand however, is that if I cut and paste the insert with
> the cursor text underneath it into a SQL*PLUS window and run it it will run
> to completion. I only get this error when I run it from within my PL/SQL > package.

One obvious difference is that in SQL*Plus you are not doing a SELECT INTO v_in and piping v_out. Look at how these are defined.

-- 
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
Received on Thu Nov 02 2006 - 20:05:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US