Re: Syntax Question

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Sun, 10 Feb 2008 02:38:15 -0800 (PST)
Message-ID: <0ffc31f3-2d24-4479-8e54-a2a91947e131@p69g2000hsa.googlegroups.com>


On Feb 10, 2:56 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote in messagenews:9e618f1c-1faf-4e17-9645-43b4d385bbdd_at_q21g2000hsa.googlegroups.com...
>
>
>
> > On Feb 9, 9:12 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> >> Oracle 10.2.0.3.0
> >> Windows Server 2003
>
> >> Sorry for this question but our database is currently unavailable so I
> >> can't
> >> try it myself.
>
> >> Is the following syntax valid for loading a nested table in bulk from a
> >> table function?
>
> >> SELECT *
> >> BULK COLLECT INTO nt_claims_rec
> >> FROM TABLE(get_claims() );
>
> >> Thanks.
>
> > Yes, but why? If nt_claims_rec is of the same nested table type as
> > get_claims() returns (and most probably this is so,) simple assignment
> > will do:
>
> > nt_claims_rec := get_claims();
>
> > Regards,
> > Vladimir M. Zakharychev
> > N-Networks, makers of Dynamic PSP(tm)
> > http://www.dynamicpsp.com
>
> Does this look workable?
>
> ----------------------------------------
> -- TYPE definitions
> ----------------------------------------
>
> CREATE TYPE t_hmp_claim AS OBJECT
> (
> clm_id VARCHAR2(14),
> clm_line_nbr NUMBER(4),
> clm_src CHAR(1),
> clm_type VARCHAR2(5),
> clmdtl_key VARCHAR2(50),
> cpt_hcpcs VARCHAR2(11),
> cpt_mod1 VARCHAR2(2),
> cpt_mod2 VARCHAR2(2),
> cpt_mod3 VARCHAR2(2),
> cpt_mod4 VARCHAR2(2),
> diag_i_1 VARCHAR2(10),
> diag_i_2 VARCHAR2(10),
> diag_i_3 VARCHAR2(10),
> diag_i_4 VARCHAR2(10),
> diag_i_5 VARCHAR2(10),
> diag_i_6 VARCHAR2(10),
> diag_i_7 VARCHAR2(10),
> diag_i_8 VARCHAR2(10),
> diag_i_9 VARCHAR2(10),
> diag_i_10 VARCHAR2(10),
> drg_submit VARCHAR2(4),
> dtl_diag VARCHAR2(10),
> hcfa_pos VARCHAR2(2),
> birth_date DATE,
> member_key VARCHAR2(50),
> gender CHAR(1),
> member_system_key VARCHAR2(50),
> person_id NUMBER(10),
> proc_i_1 VARCHAR2(7),
> proc_i_2 VARCHAR2(7),
> proc_i_3 VARCHAR2(7),
> rev_cd VARCHAR2(4),
> srv_cd VARCHAR2(2),
> srv_date_from DATE,
> srv_prv_id VARCHAR2(12),
> srv_prv_nbr VARCHAR2(16),
> sys_clmhdr_id NUMBER,
> sys_date DATE,
> valid_member CHAR(1)
> );
>
> CREATE OR REPLACE TYPE t_hmp_claim_tab AS TABLE OF t_hmp_claim;
>
> ----------------------------------------
> -- procedure code section (partial code)
> ----------------------------------------
>
> LOOP
>
> nt_claims_rec := get_hmp_claims( v_max_claims, 0, C_SYS_DATE );
>
> EXIT WHEN nt_claims_rec.COUNT = 0;
>
> v_read_cnt := v_read_cnt + nt_claims_rec.COUNT;
>
> load_pass_one;
> load_pass_two;
> load_pass_three;
>
> nt_claims_rec := t_source_tab();
>
> END LOOP;
>
> ----------------------------------------
> -- table function
> ----------------------------------------
>
> FUNCTION get_hmp_claims( p_max_claims IN DATE,
> p_person_id IN PLS_INTEGER,
> p_sys_date IN DATE )
> RETURN t_hmp_claim_tab
> PIPELINED
> IS
>
> nt_load_rec t_hmp_claim := t_hmp_claim();
>
> BEGIN
>
> OPEN cr_claims( p_max_claims, p_person_id, p_sys_date );
>
> LOOP
>
> FETCH cr_claims
> BULK COLLECT INTO nt_load_rec
> LIMIT 100000;
>
> EXIT WHEN nt_load_rec.COUNT = 0;
>
> PIPE ROW( nt_load_rec );
>
> nt_load_rec := t_hmp_claim();
>
> END LOOP;
>
> CLOSE cr_claims;
>
> RETURN;
>
> END get_hmp_claims;

Hmm... Not sure, but to me it looks like the answer is "no." You're mixing a few different concepts here: pipelined functions are good for multi-stage (pipelined) processing, mostly using SQL (that is, SELECT FROM TABLE(pipelined_function(<a cursor or another pipelined function>)). They provide no real performance or resource consumption benefits for PL/SQL-only bulk processing as BULK COLLECT or a nested table assignment will not return from the function until all rows are fetched anyway, and there go both time and memory. Besides, your pipelined table function uses BULK COLLECT INTO <collection_element>, which is not correct, too - BULK COLLECT should fetch into collections. The function might look like this:

FUNCTION get_hmp_claims( p_max_claims IN DATE,
                         p_person_id  IN PLS_INTEGER,
                         p_sys_date   IN DATE )
    RETURN t_hmp_claim_tab
    PIPELINED
IS
  nt_load_rec t_hmp_claim;
BEGIN   OPEN cr_claims( p_max_claims, p_person_id, p_sys_date );   LOOP
    FETCH cr_claims INTO nt_load_rec;
    EXIT WHEN cr_claims%NOTFOUND;
    PIPE ROW( nt_load_rec );
  END LOOP;   CLOSE cr_claims;
  RETURN; END get_hmp_claims;

Your other processing stages should be pipelined, too - they should accept ref cursors and process them row at a time. You could then pipeline them like this:

stage_two(cursor(select * from table(stage_one(....)))

BULK COLLECT doesn't fit here as it serializes processing until it fetches required number of rows or hits the last row.

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Sun Feb 10 2008 - 04:38:15 CST

Original text of this message