Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Bind variable not declared - please help

529810Oct 12 2006 — edited Oct 12 2006
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 9 2006
Added on Oct 12 2006
9 comments
2,133 views