Hello,
I have wrote a PL/SQL script to update some order_id's in a table. I have declared all my variables but get an error -- bind variable not declared. Can anyone tell me what the problem might be?
DECLARE
last_ship DATE ;
last_order NUMBER;
last_cust NUMBER;
VARIABLE curr_item NUMBER;
VARIABLE curr_order NUMBER;
aorder_id CHAR(6);
aitem_id CHAR(2);
acust_id NUMBER(6);
aship_date DATE;
CURSOR c1 IS
SELECT
order_id,
item_id,
ship_date,
cust_id
FROM
test_sales
ORDER BY
1,2,3,4,5;
BEGIN
SELECT
MAX(order_id)
INTO
curr_order
FROM
sales_order;
OPEN c1;
LOOP
FETCH c1 INTO aorder_id, aitem_id, aship_date, acust_id;
EXIT WHEN c1%NOTFOUND;
last_cust := c1.cust_id; -- Saves the last values to check if processing same order
last_order := c1.aorder_id; --
last_ship := c1.aship_date; --
IF c1.aorder_id = last_order AND c1.cust_id = last_cust AND c1.ship_date = last_ship THEN
curr_item := curr_item +1;
UPDATE test_sales SET c1.aorder_id = :curr_order;
UPDATE test_sales SET c1.aitem_id = :curr_item;
ELSE
curr_order := curr_order + 1;
curr_item := 1;
UPDATE test_sales SET c1.aorder_id = :curr_order;
UPDATE test_sales SET c1.aitem_id = :curr_item;
END IF;
END LOOP;
CLOSE c1;
END;
/
Cheers
Mike