Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL (ORA-1410) Deleting Several Hundred Thousand Record
PL/SQL (ORA-1410) Deleting Several Hundred Thousand Record [message #36609] Thu, 06 December 2001 04:02 Go to next message
kriser
Messages: 11
Registered: October 2001
Junior Member
All

I am trying to delete several hundred thousand records from a table using the following syntax. I keep getting the following error:

-1410 ORA-01410: invalid ROWID.

Does anyone see anything wrong with this script?
Does anyone have a better way of deleting several hundered thousand rows from a table at a time.

-------------------------------------------------------
CURSOR del_rvnstg_cur
IS
SELECT ROWID
FROM TEGRVN_STG
WHERE xt_src_cd = ext_src_in
AND file_id = file_id_in
AND bal_dt = bal_dt_in
FOR UPDATE;

del_rec del_rvnstg_cur%ROWTYPE;

-------------------------------------------------------

FOR del_rec IN del_rvnstg_cur
LOOP
FETCH del_rvnstg_cur INTO del_rec;

DELETE
FROM TEGRVN_STG
WHERE CURRENT OF del_rvnstg_cur;

IF MOD (del_rvnstg_cur%rowcount, 100000) = 0
THEN
COMMIT;
END IF;

END LOOP;
-------------------------------------------------------

----------------------------------------------------------------------
Re: PL/SQL (ORA-1410) Deleting Several Hundred Thousand Record [message #36610 is a reply to message #36609] Thu, 06 December 2001 04:26 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
remove fetch inside the loop. For loop automatically handles fetches record.

----------------------------------------------------------------------
Re: PL/SQL (ORA-1410) Deleting Several Hundred Thousand Record [message #36614 is a reply to message #36610] Thu, 06 December 2001 05:06 Go to previous message
kriser
Messages: 11
Registered: October 2001
Junior Member
Suresh

Thank you for your help!

Kyle

----------------------------------------------------------------------
Previous Topic: using inline subquery in a PL/SQL cursor
Next Topic: First Unused Row
Goto Forum:
  


Current Time: Fri Mar 29 02:55:12 CDT 2024