"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.
Received on Thu Nov 02 2006 - 18:51:46 CST
