How to run a curser for update "with hold2 in PL/SQL ?Example:declarecursor CSRtest is select * from emp for update of lastname;beginfor i in CSRtest loop...do something...call procfedure with "commit"update emp set lastname = "..." where current of CSRtest;endloop;Results in "ORA-01002: fetch out of sequence" because curser is closed after "Commit"How to "save state of curser" ?Any workaround?

Interview Candidate
Dec 18th, 2006
3
2366

Showing Answers 1 - 3 of 3 Answers

gangadharam.p
Profile Answers by gangadharam.p Questions by gangadharam.p

Jan 5th, 2007

Hi,
It is very good query u asked.The work around is, it is not advisable to use a COMMIT inside the loop as it invalidates the cursor which is declared for update.If u want to use commit then dont declare the cursor for update instead use the PK of the table in WHERE clause.it stimulates WHERE CURRENT OF clause.if the table is not having a PRIMARY KEY then use ROWID instead.U can fetch the rowid of the each col into PL/SQL variable and use that in where clause.
Thanks.