CODEWITHSUNDEEP
oracle-databasecursor
Phate01
Phate01

Reputation: 1795

oracle cursor is not updating data

I made an example to better explain the situation:

I have two tables

CREATE TABLE AA
(
    ID NUMBER NOT NULL PRIMARY KEY,
    REFERENCE NVARCHAR2(100) NULL
)

CREATE TABLE BB
(
    ID NUMBER NOT NULL PRIMARY KEY,
    IDA NUMBER NOT NULL,
    REFERENCE NVARCHAR2(100) NULL,

    FOREIGN KEY (IDA) REFERENCES AA(ID)
)

and some records

INSERT INTO AA VALUES(1, NULL)
INSERT INTO AA VALUES(2, NULL)
INSERT INTO AA VALUES(3, NULL)
INSERT INTO AA VALUES(4, NULL)
INSERT INTO AA VALUES(5, NULL)

INSERT INTO BB VALUES(6, 1, 'AAA')
INSERT INTO BB VALUES(7, 2, 'BBB')
INSERT INTO BB VALUES(8, 3, 'CCC')

I have to set REFERENCE field of AA with the value that's in BB by IDA.

So I decided to use a cursor

DECLARE 
    idA NUMBER;
    reference NVARCHAR2(100);

    CURSOR ref_cursor
    IS
        SELECT IDA, REFERENCE FROM BB;
BEGIN

    OPEN ref_cursor;

    LOOP
        FETCH ref_cursor into idA, reference;
        EXIT WHEN ref_cursor%NOTFOUND;

        UPDATE AA 
        SET REFERENCE = reference 
        WHERE ID = idA;

        DBMS_OUTPUT.put_line('reference of movement ' || idA || ' updated with ' || reference);

    END LOOP;

    CLOSE ref_cursor;
END;

By executing it, I get an output

reference of movement 1 updated with AAA
reference of movement 2 updated with BBB
reference of movement 3 updated with CCC

That's exactly what I want, but when I check the data by star-selecting the table I found the REFERENCE column still NULL.

So I found this other cursor

DECLARE 
    CURSOR ref_cursor
    IS 
       SELECT IDA, REFERENCE FROM BB;

BEGIN
    FOR ref_c IN ref_cursor
    LOOP
         UPDATE AA SET REFERENCE = ref_c.REFERENCE WHERE ID = ref_c.IDA;            
    END LOOP;
END;

This one is actually doing the update. Why are these 2 cursors different?

Upvotes: 0

Views: 655

Answers (2)

Alessandro Vecchio
Alessandro Vecchio

Reputation: 186

When a SQL statement is processed inside a PL/SQL block, the SQL name resolution takes priority so, table fields have precedence over variables with the same name. To solve this issue you have two ways:

  • change the variable name.

  • add a prefix to the variable name as you already did in your second query.

Upvotes: 1

SSD
SSD

Reputation: 369

In the first cursor you wrote 

UPDATE AA 
        SET REFERENCE = reference 
        WHERE ID = idA;

I think you should rename the variable to different name

Upvotes: 0

Related Questions