Thanks David.. for checking.
Please ignore the fill_tab.
Its only updating fk for pk value.
Modified code is as below -
DECLARE
CURSOR rec_cur IS
SELECT *
FROM test4;
TYPE num_tab_t IS TABLE OF NUMBER(38);
pk_tab NUM_TAB_T;
fk_tab NUM_TAB_T;
BEGIN
OPEN rec_cur;
LOOP
FETCH rec_cur BULK COLLECT INTO pk_tab, fk_tab LIMIT 100000;
EXIT WHEN pk_tab.COUNT() = 0;
FORALL i IN pk_tab.FIRST .. pk_tab.LAST
UPDATE test
SET fk = fk_tab(i)
WHERE pk = pk_tab(i);
DBMS_OUTPUT.put_line('Updated '|| pk_tab.count || ' rows of test4
table');
commit;
END LOOP;
CLOSE rec_cur;
commit;
END;
/
I did verify the total records that matches the PK. it should have done all
those records, but it let go 27893 records not updated in first round. Here
is the stats -
1st execution didn't update 27893 rows
2nd execution didn't update 1573 rows
3rd execution didn't update 3 rows
Finally 4th execution completed all the rows.
On Sat, Mar 23, 2013 at 9:19 AM, ddf <orat...@msn.com> wrote:
>
>
> On Friday, March 22, 2013 3:00:08 PM UTC-7, GD wrote:
>>
>> Hi All,
>>
>> Have a question related to FORALL Bulk Update:
>>
>> I have a similar code as below -
>>
>> DECLARE
>> CURSOR rec_cur IS
>> SELECT *
>> FROM test4;
>>
>> TYPE num_tab_t IS TABLE OF NUMBER(38);
>> TYPE vc2_tab_t IS TABLE OF VARCHAR2(4000);
>>
>> pk_tab NUM_TAB_T;
>> fk_tab NUM_TAB_T;
>> fill_tab VC2_TAB_T;
>> BEGIN
>> OPEN rec_cur;
>> LOOP
>> FETCH rec_cur BULK COLLECT INTO pk_tab, fk_tab, fill_tab LIMIT
>> 100000;
>> EXIT WHEN pk_tab.COUNT() = 0;
>>
>> FORALL i IN pk_tab.FIRST .. pk_tab.LAST
>> UPDATE test
>> SET fk = fk_tab(i)
>> , fill = fill_tab(i)
>> WHERE pk = pk_tab(i);
>>
>> DBMS_OUTPUT.put_line('Updated '|| pk_tab.count || ' rows of test4
>> table');
>>
>> commit;
>>
>> END LOOP;
>> CLOSE rec_cur;
>> commit;
>> END;
>> /
>>
>> There are totally 531503 row to update.
>>
>> Even though dbms output show that it updated all the rows.
>>
>> Update 100000 rows of adjitem table
>> Update 100000 rows of test4 table
>> Update 100000 rows of test4 table
>> Update 100000 rows of test4 table
>> Update 100000 rows of test4 table
>> Update 31503 rows of test4 table
>>
>> There are some records those didn't get updated.
>>
>> 1st execution didn't update 27893 rows
>> 2nd execution didn't update 1573 rows
>> 2rd execution didn't update 3 rows
>>
>> What would be the reason, that its not updating everything in first
>> execution?
>>
>> Thanks in advance!
>> G
>>
>> You do not mention which tables are not getting 'complete' updates -- I
> can only presume the updates are for the pk table, the fk table and the
> fill table in that order. You're also selecting from a fourth table --
> there is no guarantee that every record from test4 matches keys in the pk
> table, the fk table and the fill table.
>
> You need to check your data in all four tables before you start thinking
> your code has problems.
>
>
> David Fitzjarrell
>
> --
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
> ---
> You received this message because you are subscribed to the Google Groups
> "Oracle PL/SQL" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to oracle-plsql+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
---
You received this message because you are subscribed to the Google Groups
"Oracle PL/SQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to oracle-plsql+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
