Updating specific row in cursor

08-Jun-2016 03:49

externally - from any one elses perspective, it is not any different then any other transaction. they appear no differently." The ORACLE doc A76965-01 for 8i says "Once invoked, an autonomous transaction is totally independent of the main transaction that called it.

It does not see any of the uncommitted changes made by the main transaction and does not share any locks or resources with the main transaction.

Then, I tried the same thing with one of primary key disabled in the WHERE clause of the plsql.

declare cursor c1 is select PRS_BSNS_SGMNT, PRS_BSNS_SUB_SGMNT, PRS_DATE, PRS_WOO_PRCS_ID,prs_sts from prcs_sts where PRS_BSNS_SGMNT = 'FRX' AND PRS_BSNS_SUB_SGMNT = 'USDINR' -- AND PRS_DATE = '28-MAR-02' AND PRS_WOO_PRCS_ID = 'PF31' for update of prs_sts; begin for c1_rec in c1 loop update prcs_sts set prs_sts = 'Y' where current of c1; end loop; end; / PL/SQL procedure successfully completed.

breaking the cursor up into lots of pieces would be a really bad idea in my opinion and experience. I tried your sample code: I got the error ORA-06519: active autonomous transaction detected and rolled back The v$transaction showed no rows.

the probability of them happening is extremely small.I'll re-run the pf31 block again as an AUTONOMOUS (sub or nested transaction -- runs AS IF it were in another session) transaction to show that it would be blocked. WORLD declare 2 PRAGMA AUTONOMOUS_TRANSACTION; 3 cursor c1 is 4 select PRS_WOO_PRCS_ID,prs_sts 5 from prcs_sts 6 where PRS_WOO_PRCS_ID = 'PF32' 7 for update of prs_sts; 8 begin 9 for c1_rec in c1 loop 10 update prcs_sts 11 set prs_sts = 'Y' 12 where current of c1; 13 end loop; 14 COMMIT; 15 end; 16 / PL/SQL procedure successfully completed. The only difference I can see in your plsql and mine is the kind of data that was selected for update.I modified my WHERE clause to include all the columns of my primary key in the table (PRS_BSNS_SGMNT, PRS_BSNS_SUB_SGMNT, PRS_DATE, PRS_WOO_PRCS_ID). declare cursor c1 is select PRS_BSNS_SGMNT, PRS_BSNS_SUB_SGMNT, PRS_DATE, PRS_WOO_PRCS_ID,prs_sts from prcs_sts where PRS_BSNS_SGMNT = 'FRX' AND PRS_BSNS_SUB_SGMNT = 'USDINR' AND PRS_DATE = '28-MAR-02' AND PRS_WOO_PRCS_ID = 'PF31' for update of prs_sts; begin for c1_rec in c1 loop update prcs_sts set prs_sts = 'Y' where current of c1; end loop; end; / PL/SQL procedure successfully completed.GET_VAL", line 13 ORA-06512: at line 1 1 ) 12 then 13 raise program_error; 14 end if; Where is program_error coming from..there wasn't an exception declared... if we didn't find a row, someone called us with a sequence that does not exist -- how could you possibly continue processing?????? "BUT" we don't want to stop the whole process..few values..can be dealt with later with an exception report.it is an error, it must be raised, it should be raised, if not raised -- you'll just get an error elsewhere (assuming that this sequence function is use to generate keys and keys cannot be null) You'll have moved the error to another piece of code, and when it happens you'll have 5 people sitting staring at a piece of code wondering "how could that be null there? Do you really have code that is expected to call this with an undefined sequence value???? May 26, 2004 - pm UTC but the batch will stop won't it.

the probability of them happening is extremely small.

I'll re-run the pf31 block again as an AUTONOMOUS (sub or nested transaction -- runs AS IF it were in another session) transaction to show that it would be blocked. WORLD declare 2 PRAGMA AUTONOMOUS_TRANSACTION; 3 cursor c1 is 4 select PRS_WOO_PRCS_ID,prs_sts 5 from prcs_sts 6 where PRS_WOO_PRCS_ID = 'PF32' 7 for update of prs_sts; 8 begin 9 for c1_rec in c1 loop 10 update prcs_sts 11 set prs_sts = 'Y' 12 where current of c1; 13 end loop; 14 COMMIT; 15 end; 16 / PL/SQL procedure successfully completed. The only difference I can see in your plsql and mine is the kind of data that was selected for update.

I modified my WHERE clause to include all the columns of my primary key in the table (PRS_BSNS_SGMNT, PRS_BSNS_SUB_SGMNT, PRS_DATE, PRS_WOO_PRCS_ID). declare cursor c1 is select PRS_BSNS_SGMNT, PRS_BSNS_SUB_SGMNT, PRS_DATE, PRS_WOO_PRCS_ID,prs_sts from prcs_sts where PRS_BSNS_SGMNT = 'FRX' AND PRS_BSNS_SUB_SGMNT = 'USDINR' AND PRS_DATE = '28-MAR-02' AND PRS_WOO_PRCS_ID = 'PF31' for update of prs_sts; begin for c1_rec in c1 loop update prcs_sts set prs_sts = 'Y' where current of c1; end loop; end; / PL/SQL procedure successfully completed.

GET_VAL", line 13 ORA-06512: at line 1 1 ) 12 then 13 raise program_error; 14 end if; Where is program_error coming from..there wasn't an exception declared... if we didn't find a row, someone called us with a sequence that does not exist -- how could you possibly continue processing?????? "BUT" we don't want to stop the whole process..few values..can be dealt with later with an exception report.

it is an error, it must be raised, it should be raised, if not raised -- you'll just get an error elsewhere (assuming that this sequence function is use to generate keys and keys cannot be null) You'll have moved the error to another piece of code, and when it happens you'll have 5 people sitting staring at a piece of code wondering "how could that be null there? Do you really have code that is expected to call this with an undefined sequence value???? May 26, 2004 - pm UTC but the batch will stop won't it.

here we can and did update t2, but not t1 since t1.x was the "for update" column hopefully -- that shows you the consequences? If the order is not fixed then this perhaps has consequences in terms of deadlock (one session getting it in order (t1, t2) and the other getting it in the order (t2, t1). August 21, 2003 - pm UTC consider it "atomic" for all intents and purposes. the order for a given query would be given the same plans and all -- but it'll be a function of how the data is accessed. PICKING_LINE_DETAIL_ID FROM WSH_DEPARTURES DEP, WSH_DELIVERIES DEL, SO_LINE_DETAILS LD, SO_PICKING_LINE_DETAILS PLD WHERE DEP. The solution to this query is to specify the tables to be locked in the FOR UPDATE clause via the FOR option, or break the query into separate cursors such that each cursor locks a single table only.