Re: Syntax Question

From: Dereck L. Dietz <>
Date: Sat, 9 Feb 2008 18:56:29 -0500
Message-ID: <s6rrj.8408$>

"Vladimir M. Zakharychev" <> wrote in message
> On Feb 9, 9:12 pm, "Dereck L. Dietz" <> wrote:
>> Oracle
>> 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?
>> 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)

Does this look workable?

  • TYPE definitions


     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;


  nt_claims_rec := t_source_tab();


  • 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

  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; Received on Sat Feb 09 2008 - 17:56:29 CST

Original text of this message