update statement in inner loop with pl sql procedure

Question

I tried to update my table into field1 with the following pl sql procedure, the compilation and execution done without any error, put when I call this procedure the update didn't work I don't know why! I used (for update of .... current of) statement with the cursor and the code is below

create or replace procedure p1 is
r1 table1%rowtype;
r2 table2%rowtype;
cursor c1 is select * from table1 for update of field1;
cursor c2 is select * from table2;
begin
open c1;
loop <<outer>>
    fetch c1 into r1;
    open c2;
        loop <<inner>>
            fetch c2 into r2;
               if condition then
               dbms_output.put_line('ok');            
               update table1
               set field1= 1
               where current of c1;                    
               end if;
        exit when c2%notfound;
        end loop inner;       
    close c2; 
    exit when c1%notfound;
end loop outer;
close c1;
end;
/

Note: the condition in the IF statement is correct because when I execute the procedure the statement (dbms_output.put_line('ok');) executed successfully each time the loop executed when I remove the update statement and (for update of....current of) statement, but when I put the update statement with (for update of....current of) statement with the same condition, the update statement doesn't work.

You don't need a loop at all. As far as I can tell from your obfuscated example, this could probably all be done with a single update statement. — user330315
– user330315, Commented Jun 10, 2013 at 13:36
+1 to @a_horse_with_no_name. i would also like to add that it makes no sense to update every row of c1 for each row of c2. you would be updating the same row over and over again. you dont need plsql here. — haki
– haki, Commented Jun 10, 2013 at 19:32

the_slk · Accepted Answer · 2013-06-10 12:40:44Z

1)

    open c2;
        loop <<inner>>
            fetch c2 into r2;
               if condition then
               dbms_output.put_line('ok');            
               update table1
               set field1= 1
               where current of c1;  -- <---- YOU CANNOT USE THAT
               end if;
        exit when c2%notfound;
        end loop inner;       
    close c2; 

2) Rewrite to plain SQL:

CREATE TABLE table1(field1 NUMBER, field2 NUMBER);
CREATE TABLE table2(field1 NUMBER, field2 NUMBER);

INSERT INTO table1(field1, field2) VALUES(111, 121);
INSERT INTO table1(field1, field2) VALUES(112, 122);
INSERT INTO table1(field1, field2) VALUES(113, 123);
INSERT INTO table1(field1, field2) VALUES(114, 124);
INSERT INTO table1(field1, field2) VALUES(115, 125);

INSERT INTO table2(field1, field2) VALUES(111, 121);
INSERT INTO table2(field1, field2) VALUES(112, 122);
INSERT INTO table2(field1, field2) VALUES(213, 123);
INSERT INTO table2(field1, field2) VALUES(214, 124);
INSERT INTO table2(field1, field2) VALUES(215, 125);
COMMIT;

UPDATE  table1
SET     field1 = 1
WHERE   EXISTS
(
        SELECT  1
        FROM    table2
        WHERE   table1.field1 = table2.field1
        AND     table1.field2 = table2.field2
);
-- 2 rows updated.

SELECT * FROM table1;
/*
1   121
1   122
113 123
114 124
115 125
*/

Rene · Accepted Answer · 2013-06-10 12:23:41Z

0

How do you check if the update has worked?

If you query the table in another session you will not see any changes your code has made until you execute a commit statement after your pl/sql code.

answered Jun 10, 2013 at 12:23

Rene

10.6k5 gold badges37 silver badges48 bronze badges

3 Comments

user2470764 Over a year ago

thanks for the replay, this procedure will take so many time, so I tried to run the procedure for about 15 minutes and then I canceled the execution but at least the procedure should print (OK) in the dbms windows each time the loop executed, but when I canceled the run there is no output!!!!!

user2470764 Over a year ago

and I put commit statement between where condition in update and end if like that update cdr_table set ex_flag= 1 where current of cdr_cur; commit; end if; and it doesn't work!!!!!!

ThinkJet Over a year ago

@user2470764 Looks like you blocked rows retrieved by c1 cursor in another open session.

Collectives™ on Stack Overflow

update statement in inner loop with pl sql procedure

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related