Hi I have the following problem which I'm trying to solve.I tried but for some reason the update did not reflect
I have a table named " test_tab "
This table does not have a primary key column and I need to use rowid in the for all bulk updates.
So the algo is
1: Select all rows from test_tab for batch_id 10
2: make some other procedure calls for each row and then set the process_Flag column in the collection to S
3: after all processing update the test_tab.process_flag column in the table with the values updated in the collection.
4: Wanna use rowid in the forall update...
Can someone point out something wrong in this piece of code...?
It completed but no values were updated...
==============================================================
SET SERVEROUTPUT ON;
DECLARE
CURSOR get_gl_codes
IS
SELECT a.rowid, a.* from test_tab a
WHERE a.BATCH_ID = 10;
TYPE gl_tab_type IS TABLE OF get_gl_codes%ROWTYPE;
l_tab gl_tab_type;
c_limit PLS_INTEGER := 1000;
BEGIN
-- Retrieve the existing data into a collection.
dbms_output.put_line('Starting Bulk collect --- ');
OPEN get_gl_codes;
LOOP
FETCH get_gl_codes
BULK COLLECT INTO l_tab
LIMIT c_limit;
EXIT WHEN l_tab.COUNT = 0;
END LOOP;
close get_gl_codes;
-- Alter the data in the collection.
FOR i IN 1 .. l_tab.COUNT
LOOP
l_tab(i).process_flag := 'S';
--dbms_output.put_line('Processed rec was : '); --||l_tab(i).concatenated_segments);
END LOOP;
-- Update the table using the collection.
FORALL i IN 1 .. l_tab.COUNT
UPDATE test_tab
SET process_flag = l_tab(i).process_flag
,result_description = 'TESTING BULKS'
WHERE concatenated_segments = l_tab(i).concatenated_segments; -- use rowid = l_tab(i).rowid instead of this where clause
dbms_output.put_line('Ending Bulk collect --- ');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERRORS : '||SQLERRM);
END;
/
===============================================================