Oracle SQL / PL/SQL: 'update' in loop fails

Question

This is the situation: I have a table in an Oracle SQL DB. That table is used for reporting purposes. It's a test/development environement. I want to test a new grouping feature in our report tools using this DB, but I have to update and replace all of the 'numbers' (not being the key for that table). Several data sets can share the same 'numbers'. The updated numbers should follow a certain pattern to be auto-testable. I thought of something like an integer that is increased for each a new number. So here is waht I did so far: At first I query all unique 'numbers' in that table, then I loop over the result table to replace each of 'number' found with the generated 'new_number'. Then 'new_number' is increased by one before taking the next 'number' of the result table to be exchanged in the live table, and so on.

Here is the code:

DECLARE
    Cursor MyCursor IS
        SELECT DISTINCT NUMBER
            FROM TABLE
            ORDER BY NUMBER;

    entry MyCursor%ROWTYPE;
    new_number NUMBER := 111111;

    BEGIN
    FOR entry IN MyCursor LOOP
        IF MyCursor%FOUND THEN
            UPDATE TABLE
                SET
                NUMBER = new_number,
                WHERE  NUMBER = ENTRY.NUMBER;
        new_number := new_number + 1;
        ELSIF myCursor%NOTFOUND THEN
             EXIT;
        END IF;
    END LOOP;
END;

Now the problem is, the script runs BUT I am told there there was only one row updated (the last one, it seems).

Why is that?

I can put a

DBMS_OUTPUT.PUT_LINE(entry.number);

inside the loop and it will display all unique numbers found in TABLE with each iteration, so the loop itself seems to be working.

And just mabye...is there a way to do it without loop? Couldn't come up with anything.

This script will be executed only once (as soon as it works) in preparation of test runs.

Thanks a lot im advance!

How are you determining that only one row was updated? Have you checked the data? The ROWCOUNT is probably returning the count from the last update — FJT
– FJT, Commented Dec 4, 2012 at 15:40
You shouldn't be using a loop for this. It's extremely in-efficient. This can be done with a single MERGE statement much better. — user330315
– user330315, Commented Dec 4, 2012 at 15:45
I am Java dev and usually all I had to do with SQL so far doesn't go beyond cookie-cutter SELECTs. To be honest, I never even heard of MERGE before but I kind of intuitivle thought there was a better solution — that's why I asked for it. Will look into MERGE for other occasions in the future. — m00hk00h
– m00hk00h, Commented Dec 4, 2012 at 16:12

dani herrera · Accepted Answer · 2012-12-04 15:38:48Z

3

Quoting oracle docs:

%FOUND: Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.

You really need to check for cursor inserts, updates, deletes?

It seems that your are looking for:

FOR entry IN MyCursor LOOP

    UPDATE TABLE
            SET
            NUMBER = new_number,
            WHERE  NUMBER = ENTRY.NUMBER;
    new_number := new_number + 1;

END LOOP;

Or

FOR entry IN MyCursor LOOP

    UPDATE TABLE
            SET
            NUMBER = new_number,
            WHERE  NUMBER = ENTRY.NUMBER;
    IF SQL%FOUND THEN
       new_number := new_number + 1;
    END IF;
END LOOP;

answered Dec 4, 2012 at 15:38

dani herrera

52k11 gold badges133 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

m00hk00h Over a year ago

Actuall I don't need those checks, I implemented them after I noticed that someting failed in hopes of catching the error. +1 for shorting it down to the esentials.

m00hk00h · Accepted Answer · 2012-12-04 16:06:58Z

1

Actually, all of you were right - as was even I in the first place. Thanks for your answers anyways, I appreciate it!

The DBMS I am using (Hora) was configured to manually confirm every change. With every iteration it 'overwrote' the prompt to confirm/commit and only asked for the last change that occured. I had it changed and the script runs and does what it was supposed to now. Although Hora still only displays one row to be changed, it changes all of them now when commited.

Thanks again.

answered Dec 4, 2012 at 16:06

m00hk00h

5172 gold badges7 silver badges21 bronze badges

Collectives™ on Stack Overflow

Oracle SQL / PL/SQL: 'update' in loop fails

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related