Re: Syntax Question
Date: Sat, 9 Feb 2008 18:56:29 -0500
Message-ID: <s6rrj.8408$Rg1.5963@nlpi068.nbdc.sbc.com>
"Vladimir M. Zakharychev" <vladimir.zakharychev_at_gmail.com> wrote in message
news: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; Received on Sat Feb 09 2008 - 17:56:29 CST