| 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 -> Nested Table Functions
I'm trying to get a table function to work but when I tried and tested it,
it seemed as if the queries were taking longer than the non-table function
versions. I've never programmed TABLE FUNCTIONS before much less nested
ones so I'm at a loss as to what I'm goofing up.
In the code below, please be aware of the fact I was just trying to actually get the code to work so I know there could be tweaks made to it.
I'm working with Oracle 10g Release 2 (version 1.02 I believe). Any corrections/advice on how to code nested table functions would be appreciated as I'm interested in using them for the datawarehouse loading.
Thanks
FUNCTION get_person_id( p_curs IN SYS_REFCURSOR )
RETURN stg_elig_rec_t
PIPELINED
v_elig_row stg_elig%ROWTYPE;
BEGIN LOOP
FETCH p_curs INTO v_elig_row;
EXIT WHEN p_curs%NOTFOUND;
BEGIN
SELECT person_id INTO v_elig_row.person_id
FROM rpt.bo_mbr_addr
WHERE mbr_contrived_key = v_elig_row.mbr_contrived_key;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_elig_row.person_id := 0;
END;
PIPE ROW(stg_elig_rec_o( v_elig_row.person_id,
v_elig_row.sys_contract_mbr,
v_elig_row.mbr_contrived_key,
v_elig_row.subscriber,
v_elig_row.suffix,
v_elig_row.group_id,
v_elig_row.subgroup_id,
v_elig_row.class_id,
v_elig_row.class_desc,
v_elig_row.product,
v_elig_row.product_id,
v_elig_row.product_desc,
v_elig_row.elig_class_prod_cat,
v_elig_row.elig_ind,
v_elig_row.elig_plan_id,
v_elig_row.product_category,
v_elig_row.member_type,
v_elig_row.product_type,
v_elig_row.plan_certificate,
v_elig_row.plan_suffix,
v_elig_row.gender,
v_elig_row.birth_date,
v_elig_row.mbr_eff_date,
v_elig_row.mbr_term_date,
v_elig_row.sys_date ) );
END LOOP;
RETURN;
EXCEPTION
WHEN OTHERS THEN
global.sql_error('bo_load.get_person_id',SQLCODE);
RAISE global.e_unhandled_exception;
END get_person_id;
FUNCTION read_ext_elig( p_curs IN SYS_REFCURSOR )
RETURN stg_elig_rec_t
PIPELINED
v_elig_row ext_elig%ROWTYPE;
v_member_type stg_elig.member_type%TYPE;
v_plan_certificate stg_elig.plan_certificate%TYPE;
v_plan_suffix stg_elig.plan_suffix%TYPE;
v_product stg_elig.product%TYPE;
v_product_category stg_elig.product_category%TYPE;
v_product_type stg_elig.product_type%TYPE;
v_suffix stg_elig.suffix%TYPE;
v_sys_contract_mbr stg_elig.sys_contract_mbr%TYPE;
BEGIN LOOP
FETCH p_curs INTO v_elig_row;
EXIT WHEN p_curs%NOTFOUND;
v_member_type := rules.member_type(
v_elig_row.inp_elig_plan_id );
v_plan_certificate := rules.plan_certificate(
v_elig_row.inp_elig_plan_id );
v_plan_suffix := rules.plan_suffix(
v_elig_row.inp_elig_plan_id );
v_product := rules.product( v_elig_row.inp_elig_plan_id );
v_product_category := rules.product_category(
v_elig_row.inp_elig_plan_id );
v_product_type := rules.product_type(
v_elig_row.inp_elig_plan_id );
v_suffix := rules.suffix( v_elig_row.inp_suffix );
v_sys_contract_mbr := rules.sys_contract_mbr(
v_elig_row.inp_subscriber,
v_elig_row.inp_suffix );
PIPE ROW(stg_elig_rec_o( NULL,
v_sys_contract_mbr,
v_elig_row.inp_contrived_key,
v_elig_row.inp_subscriber,
v_suffix,
v_elig_row.inp_group_id,
v_elig_row.inp_subgroup_id,
v_elig_row.inp_class_id,
v_elig_row.inp_class_desc,
v_product,
v_elig_row.inp_product_id,
v_elig_row.inp_product_desc,
v_elig_row.inp_elig_class_prod_cat,
v_elig_row.inp_elig_ind,
v_elig_row.inp_elig_plan_id,
v_product_category,
v_member_type,
v_product_type,
v_plan_certificate,
v_plan_suffix,
v_elig_row.inp_gender,
v_elig_row.inp_birth_date,
v_elig_row.inp_effective_date,
v_elig_row.inp_terminated_date,
v_elig_row.sys_date ) );
END LOOP;
RETURN;
EXCEPTION
WHEN OTHERS THEN
global.sql_error('bo_load.read_ext_elig',SQLCODE);
RAISE global.e_unhandled_exception;
END read_ext_elig;
PROCEDURE function_test
IS
BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE stg_elig';
INSERT INTO stg_elig
SELECT person_id,
sys_contract_mbr,
mbr_contrived_key,
subscriber,
suffix,
group_id,
subgroup_id,
class_id,
class_desc,
product,
product_id,
product_desc,
elig_class_prod_cat,
elig_ind,
elig_plan_id,
product_category,
member_type,
product_type,
plan_certificate,
plan_suffix,
gender,
birth_date,
mbr_eff_date,
mbr_term_date,
sys_date
FROM TABLE ( get_person_id
( CURSOR( SELECT *
FROM TABLE ( read_ext_elig
( CURSOR( SELECT *
FROM
ext_elig ) )))));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
global.sql_error('bo_load.function_test',SQLCODE);
RAISE global.e_unhandled_exception;
END function_test; Received on Mon Oct 16 2006 - 18:05:16 CDT
 |
 |