Ask Question Forum:
C
O
M
P
U
T
E
R
2
8
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Y-position of the mouse cursor
Attachment:===
Bulk collect and forall insert, questions on construct of this

So I have the basic understanding from here (FORALL Insert)
http://psoug.org/reference/array_processing.html
a. Can I do both an insert and an update in the same forall ?
b. can I put my big exception processing under forall ?
when I first compiled, it choked on the first line after the forall: "curr_seqno := mbp.memb_seqno(indx);
- it said "encountered the symbol CURR_SEQNO ... ... ... @%&-+/
then I commented them out and it said all the fields in my values clause "must be declared"
so I commented all of them out and just said
values mbp(indx) and it said "not enough values" (even though the cursor and the values have identical set of fields.
====================== Original code ==========================PROCEDURE mm_copy(source_cd varchar2)IS curr_seqno ods.mem_memb.memb_seqno%TYPE; curr_srce_sys_cd ods.mem_memb.memb_srce_sys_cd%TYPE; curr_memb_id_nbr ods.mem_memb.memb_id_nbr%TYPE; rec_cnt INTEGER := 0; tab_nam VARCHAR2(25);-- cursor to read ods.mem_memb table CURSOR cursmm is SELECT * FROM ods.mem_memb WHERE memb_srce_sys_cd = source_cd AND ods_stus_cd IS NULL ORDER BY memb_seqno;BEGIN-- initialize io_err := FALSE; curr_seqno := 0; curr_srce_sys_cd := NULL; curr_memb_id_nbr := NULL; rec_cnt := 0; tab_nam := 'MEM_MEMB';-- loop for each eligible record FOR mem_memb_rec IN cursmm LOOP-- save identifying data for err_tbl, if necessary; curr_seqno := mem_memb_rec.memb_seqno; curr_srce_sys_cd := mem_memb_rec.memb_srce_sys_cd; curr_memb_id_nbr := mem_memb_rec.memb_id_nbr; srce_tot_in := srce_tot_in + 1;-- insert into CDR (stage3) mem_memb table; INSERT INTO stage3.mem_memb m1_copy (memb_seqno ,memb_id_nbr ,memb_chngd_dt ,memb_srce_sys_cd ,subr_memb_seqno ,subr_memb_id_nbr ,subr_memb_srce_sys_cd ,medl_rec_nbr ,memb_home_phon_nbr ,memb_home_fax_nbr ,memb_home_emal_addr ,memb_orig_efft_from_dt ,memb_contus_cov_reas_cd ,memb_contus_cov_efft_dt ,memb_prfx_nm ,memb_1st_nm ,memb_mid_nm ,memb_last_nm ,memb_sufx_nm ,memb_sex_cd ,memb_ssn ,memb_etncy_cd ,memb_brth_dt ,memb_dth_dt ,memb_marl_stus_cd ,memb_caus_of_dth_cd ,memb_pl_of_dth_cd ,memb_lang_cd ,memb_home_phon_ext_nbr ,memb_home_fax_ext_nbr) VALUES (mem_memb_rec.memb_seqno ,mem_memb_rec.memb_id_nbr ,mem_memb_rec.memb_chngd_dt ,mem_memb_rec.memb_srce_sys_cd ,mem_memb_rec.subr_memb_seqno ,mem_memb_rec.subr_memb_id_nbr ,mem_memb_rec.subr_memb_srce_sys_cd ,mem_memb_rec.medl_rec_nbr ,mem_memb_rec.memb_home_phon_nbr ,mem_memb_rec.memb_home_fax_nbr ,mem_memb_rec.memb_home_emal_addr ,mem_memb_rec.memb_orig_efft_from_dt ,mem_memb_rec.memb_contus_cov_reas_cd ,mem_memb_rec.memb_contus_cov_efft_dt ,mem_memb_rec.memb_prfx_nm ,mem_memb_rec.memb_1st_nm ,mem_memb_rec.memb_mid_nm ,mem_memb_rec.memb_last_nm ,mem_memb_rec.memb_sufx_nm ,mem_memb_rec.memb_sex_cd ,mem_memb_rec.memb_ssn ,mem_memb_rec.memb_etncy_cd ,mem_memb_rec.memb_brth_dt ,mem_memb_rec.memb_dth_dt ,mem_memb_rec.memb_marl_stus_cd ,mem_memb_rec.memb_caus_of_dth_cd ,mem_memb_rec.memb_pl_of_dth_cd ,mem_memb_rec.memb_lang_cd ,mem_memb_rec.memb_home_phon_ext_nbr ,mem_memb_rec.memb_home_fax_ext_nbr);-- update ODS record; UPDATE ods.mem_memb SET ods_stus_cd = 'L' WHERE memb_seqno = mem_memb_rec.memb_seqno; srce_tot_out := srce_tot_out + 1;-- check to commit, rec_cnt := rec_cnt + 1; IF rec_cnt >= c_commit_limit THEN COMMIT; rec_cnt := 0; END IF; END LOOP;-- set indicator to end table load finish_load := TRUE; EXCEPTION WHEN dup_val_on_index THEN UTL_FILE.PUT_LINE(out_file, 'Duplicate record in CDR mem_memb for ODS SEQNO = ' || to_char(curr_seqno)); UPDATE ods.mem_memb SET ods_stus_cd = 'D' WHERE memb_seqno = curr_seqno; WHEN no_parent_key THEN UTL_FILE.PUT_LINE(out_file, 'NO PARENT KEY ERROR IN CDR ' || tab_nam ||' for ODS SEQNO = ' || to_char(curr_seqno)); UTL_FILE.PUT_LINE(out_file, sqlerrm); UPDATE ods.mem_memb SET ods_stus_cd = 'I' WHERE memb_seqno = curr_seqno; WHEN old_snapshot THEN UTL_FILE.PUT_LINE(out_file, 'SNAPSHOT TOO OLD ERROR IN ODS ' || tab_nam ||' FOR ODS SEQNO = ' || to_char(curr_seqno)); UTL_FILE.PUT_LINE(out_file, sqlerrm); ROLLBACK; WHEN others THEN UTL_FILE.PUT_LINE(out_file, 'ERROR IN PROCEDURE MM_COPY FOR ODS SEQNO = ' || to_char(curr_seqno)); UTL_FILE.PUT_LINE(out_file, sqlerrm); insert_err_tbl(curr_srce_sys_cd, curr_memb_id_nbr, curr_seqno, 'MEM_MEMB', sqlcode, '109'); UPDATE ods.mem_memb SET ods_stus_cd = 'E' WHERE memb_seqno = curr_seqno; io_err := TRUE;END mm_copy;============== Forall, bulk collect =================PROCEDURE mm_copy_bulk_insert(source_cd varchar2)IS curr_seqno ods.mem_memb.memb_seqno%TYPE; curr_srce_sys_cd ods.mem_memb.memb_srce_sys_cd%TYPE; curr_memb_id_nbr ods.mem_memb.memb_id_nbr%TYPE; rec_cnt INTEGER := 0; tab_nam VARCHAR2(25);-- cursor to read ods.mem_memb table CURSOR cursmm is SELECT * FROM ods.mem_memb WHERE memb_srce_sys_cd = source_cd AND ods_stus_cd IS NULL ORDER BY memb_seqno; type member_bulk is table of cursmm%rowtype; mbp member_bulk; -- "member bulk process"BEGIN-- initialize io_err := FALSE; curr_seqno := 0; curr_srce_sys_cd := NULL; curr_memb_id_nbr := NULL; rec_cnt := 0; tab_nam := 'MEM_MEMB'; curr_srce_sys_cd := source_cd; open cursmm; loop fetch cursmm bulk collect into mbp limit 50000; forall indx in 1 .. mbp.count -- save identifying data for err_tbl, if necessary; -- curr_seqno := mbp.memb_seqno(indx); -- curr_memb_id_nbr := mbp.memb_id_nbr(indx); -- srce_tot_in := srce_tot_in + 1; INSERT INTO stage3.mem_memb m1_copy (memb_seqno ,memb_id_nbr ,memb_chngd_dt ,memb_srce_sys_cd ,subr_memb_seqno ,subr_memb_id_nbr ,subr_memb_srce_sys_cd ,medl_rec_nbr ,memb_home_phon_nbr ,memb_home_fax_nbr ,memb_home_emal_addr ,memb_orig_efft_from_dt ,memb_contus_cov_reas_cd ,memb_contus_cov_efft_dt ,memb_prfx_nm ,memb_1st_nm ,memb_mid_nm ,memb_last_nm ,memb_sufx_nm ,memb_sex_cd ,memb_ssn ,memb_etncy_cd ,memb_brth_dt ,memb_dth_dt ,memb_marl_stus_cd ,memb_caus_of_dth_cd ,memb_pl_of_dth_cd ,memb_lang_cd ,memb_home_phon_ext_nbr ,memb_home_fax_ext_nbr) VALUES mbp(indx) --(mbp.memb_seqno(indx) ,mbp.memb_id_nbr(indx) ,mbp.memb_chngd_dt(indx) ,mbp.memb_srce_sys_cd(indx) ,mbp.subr_memb_seqno(indx) --,mbp.subr_memb_id_nbr(indx) ,mbp.subr_memb_srce_sys_cd(indx) ,mbp.medl_rec_nbr(indx) ,mbp.memb_home_phon_nbr(indx) --,mbp.memb_home_fax_nbr(indx) ,mbp.memb_home_emal_addr(indx) ,mbp.memb_orig_efft_from_dt(indx) ,mbp.memb_contus_cov_reas_cd(indx) --,mbp.memb_contus_cov_efft_dt(indx) ,mbp.memb_prfx_nm(indx) ,mbp.memb_1st_nm(indx) ,mbp.memb_mid_nm(indx) ,mbp.memb_last_nm(indx) --,mbp.memb_sufx_nm(indx) ,mbp.memb_sex_cd(indx) ,mbp.memb_ssn(indx) ,mbp.memb_etncy_cd(indx) ,mbp.memb_brth_dt(indx) --,mbp.memb_dth_dt(indx) ,mbp.memb_marl_stus_cd(indx) ,mbp.memb_caus_of_dth_cd(indx) ,mbp.memb_pl_of_dth_cd(indx) --,mbp.memb_lang_cd(indx) ,mbp.memb_home_phon_ext_nbr(indx) ,mbp.memb_home_fax_ext_nbr(indx)); update ods.mem_memb SET ods_stus_cd = 'L' WHERE memb_seqno = mbp.memb_seqno(indx); srce_tot_out := srce_tot_out + 1; commit; exit when sursmm%notfound; end loop; close cursmm; -- set indicator to end table load finish_load := TRUE;END mm_copy_bulk_insert;
1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:32:33:34:35:36:37:38:39:40:41:42:43:44:45:46:47:48:49:50:51:52:53:54:55:56:57:58:59:60:61:62:63:64:65:66:67:68:69:70:71:72:73:74:75:76:77:78:79:80:81:82:83:84:85:86:87:88:89:90:91:92:93:94:95:96:97:98:99:100:101:102:103:104:105:106:107:108:109:110:111:112:113:114:115:116:117:118:119:120:121:122:123:124:125:126:127:128:129:130:131:132:133:134:135:136:137:138:139:140:141:142:143:144:145:146:147:148:149:150:151:152:153:154:155:156:157:158:159:160:161:162:163:164:165:166:167:168:169:170:171:172:173:174:175:176:177:178:179:180:181:182:
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;
oh, oops again, I figured it out, sorry about that.
this was missing the table_name . . . . type memb_chngd_dt is table of ods.memb_chngd_dt%type index by pls_integer;