Hi All,
Below is a PL/SQL anonymous block dealing with huge volume of data. I have placed COMMIT statement after LOOP, or do I need to keep it after UPDATE statement.
I would like to COMMIT after updating 1000 records. Please let me know
DECLARE
CURSOR c_keywords IS -- This returns more than 3 crores of record
SELECT kw.id
FROM console.client cl
JOIN console.account ac ON ac.client_fk = cl.id
JOIN console.search_engine se ON se.account_fk = ac.sid
JOIN console.keyword kw ON kw.search_engine_fk = se.sid
WHERE cl.status = 1 AND ac.status = 1 AND se.status = 1 AND se.publisher_fk = 1
AND (kw.keyword_url IS NOT NULL OR kw.original_keyword_url 11 IS NOT NULL);
......
BEGIN
OPEN c_keywords;
LOOP
FETCH c_keywords BULK COLLECT INTO l_keywords LIMIT 1000;
FORALL i IN 1 .. l_keywords.COUNT
SAVE EXCEPTIONS
UPDATE console.keyword SET keyword_url = null,
original_keyword_url = null WHERE id = l_keywords(i).id;
EXIT WHEN c_keywords%NOTFOUND;
END LOOP;
COMMIT;
CLOSE c_keywords;
EXCEPTION
...........
END;