I created a dummy database for learning purposes, and I purposefully created some duplicated records in one of the tables. In every case I want to flag one of the duplicated records as Latest='Y', and the other record as 'N', and for every single record the Latest flag would be 'Y'.
I tried to use PlSQL to go through all of my records, but when I try to use the previously calculated value (which would tell that its a duplicated record) it says that:
ORA-06550: line 20, column 17: PLS-00201: identifier 'COUNTER' must be declared
Here is the statement I try to use:
DECLARE
CURSOR cur
IS
SELECT order_id, order_date, person_id,
amount, successfull_order, country_id, latest, ROWCOUNT AS COUNTER
FROM (SELECT order_id,
order_date,
person_id,
amount,
successfull_order,
country_id,
latest,
ROW_NUMBER () OVER (PARTITION BY order_id, order_date,
person_id, amount, successfull_order, country_id
ORDER BY order_id, order_date,
person_id, amount, successfull_order, country_id) ROWCOUNT
FROM orders) orders
FOR UPDATE OF orders.latest;
rec cur%ROWTYPE;
BEGIN
FOR rec IN cur
LOOP
IF MOD (COUNTER, 2) = 0
THEN
UPDATE orders
SET latest = 'N'
WHERE CURRENT OF cur;
ELSE
UPDATE orders
SET latest = 'Y'
WHERE CURRENT OF cur;
END IF;
END LOOP;
END;
I am new to PlSQL so I tried to modify the statements I found here: http://www.adp-gmbh.ch/ora/plsql/cursors/for_update.html
What should I change in my statement, or should I use a different approach?
Thanks for your answers in advance! Botond
Your refer the ROWNUM as COUNTER in your cursor.
While fetching, you should be accessing it from the cursor reference like MOD (rec.COUNTER, 2)