Updating row using cursor oracle
The import process was slow and had to be done in small chunks for reasons I won’t get into now.It wasn’t until after sleeping on it that I realized I had put myself into the ATG box unnecessarily. I didn’t know much PL/SQL but some Googling (I love how that’s a common verb now, btw) led me to write up this: declare i number := 0; begin for r in (select id from dps_user) loop update dps_user set password = lower(password) where id = r.id; i := i 1; if mod(i, 10000) = 0 THEN -- Commit every 10000 records COMMIT; end if; end loop; commit; end; I’m no Oracle DBA, but it works!
For examples, see the Fire DAC\Samples\DBMS Specific\Oracle\Nested Cursors demo.
To refresh the REF CURSOR or nested cursor records, application must reexecute the main query.
l_id_LOOP SELECT rowid INTO l_rowid FROM rowid_test WHERE object_id = l_id_tab(i) FOR UPDATE; UPDATE rowid_test SET object_name = object_name WHERE rowid = l_rowid; END LOOP; DBMS_OUTPUT.put_line('Rowid (' || l_id_tab.count || ' rows) : ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs'); END; / Primary Key (72699 rows): 501 hsecs Rowid (72699 rows) : 448 hsecs PL/SQL procedure successfully completed.
Have you ever had one of those moments where you’ve just completed importing 1.7 million user records from flat files into an normalized set of ATG Personalization tables, plus some custom tables, and you only then realize that the passwords from the old system, while in the compatible md5sum format, are all UPPERCASE, while ATG uses all lowercase hashes? I’m happy to override ATG behavior when I should, but this just felt wrong.
SET SERVEROUTPUT ON DECLARE TYPE t_id_tab IS TABLE OF rowid_test.object_id%TYPE; l_id_tab t_id_tab; l_rowid ROWID; l_start NUMBER; BEGIN SELECT object_id BULK COLLECT INTO l_id_tab FROM rowid_test; l_start := DBMS_UTILITY.get_time; FOR i IN l_id_tab.first ..