Forum Stats

  • 3,782,054 Users
  • 2,254,587 Discussions
  • 7,879,903 Comments

Discussions

ORA-01410: invalid ROWID

660136
660136 Member Posts: 27
edited Oct 6, 2008 9:30AM in SQL & PL/SQL
Hi,

Once again I'm faced with the confusing PL/SQL errors in my code. Having tried every permutation I am stuck.

When I run the piece of code below I get the following error
declare
      cursor cust is
                      select * from ph2 
                      order by order_date, customer_id, ship_date, order_id, item_id
                      for update of order_id, item_id;
                      
      neworder_id  ph2_item.order_id%type;
      multicust multi_site_cust.cust%type;
      newitem_id ph2_item.item_id%type;
      
      
begin
        /*Initialising neworder_id */
        select 
              max(order_id)
        into
              neworder_id
        from
              sales_order;     
        
         /*Initialising newitem_id*/
         newitem_id := 0;
                  
         for c_rec in cust loop
         neworder_id := neworder_id + 1;
             
                   if
                         ( c_rec.verify = 1 )
                   then
                          newitem_id := newitem_id + 1;
                   end if;    
                          
                   update 
                          ph2_item p
                        set
                            p.order_id = neworder_id,
                            p.item_id = newitem_id      
                   where current of cust;
             
               
          end loop;
end;
/
ORA-01410: invalid ROWID
ORA-06512: at line 33
*01410. 00000 - "invalid ROWID"*

Does anyone have any insights?
Tagged:

Best Answer

  • Gurjas
    Gurjas Member Posts: 1,190
    Accepted Answer
    here you can use "where current of" . because where current of will only work when the cursor table & updating tables are same.

    like
    CREATE OR REPLACE Function FindCourse
       ( name_in IN varchar2 )
       RETURN number
    IS
        cnumber number;
    
        CURSOR c1
        IS
           SELECT course_number
            from courses_tbl
            where course_name = name_in
            FOR UPDATE of instructor;
    
    BEGIN
    
    open c1;
    fetch c1 into cnumber;
    
    if c1%notfound then
         cnumber := 9999;
    
    else
         UPDATE courses_tbl
            SET instructor = 'SMITH'
            WHERE CURRENT OF c1;
    
        COMMIT;
    
    end if;
    
    close c1;
    
    RETURN cnumber;
    
    END; 
    if you change the table name then the rowids will be different for the cursor table & updating table.
    you have to modify the code like
    declare
          cursor cust is
                          select * from ph2 
                          order by order_date, customer_id, ship_date, order_id, item_id
                          for update of order_id, item_id;
                          
          neworder_id  ph2_item.order_id%type;
          multicust multi_site_cust.cust%type;
          newitem_id ph2_item.item_id%type;
          
          
    begin
            /*Initialising neworder_id */
            select 
                  max(order_id)
            into
                  neworder_id
            from
                  sales_order;     
            
             /*Initialising newitem_id*/
             newitem_id := 0;
                      
             for c_rec in cust loop
             neworder_id := neworder_id + 1;
                 
                       if
                             ( c_rec.verify = 1 )
                       then
                              newitem_id := newitem_id + 1;
                       end if;    
                              
                       update 
                              ph2_item p
                            set
                                p.order_id = neworder_id,
                                p.item_id = newitem_id      
                       where customer_id = c_rec.customer_id;
                 
                   
              end loop;
    end;
    /  

Answers

  • Gurjas
    Gurjas Member Posts: 1,190
    Accepted Answer
    here you can use "where current of" . because where current of will only work when the cursor table & updating tables are same.

    like
    CREATE OR REPLACE Function FindCourse
       ( name_in IN varchar2 )
       RETURN number
    IS
        cnumber number;
    
        CURSOR c1
        IS
           SELECT course_number
            from courses_tbl
            where course_name = name_in
            FOR UPDATE of instructor;
    
    BEGIN
    
    open c1;
    fetch c1 into cnumber;
    
    if c1%notfound then
         cnumber := 9999;
    
    else
         UPDATE courses_tbl
            SET instructor = 'SMITH'
            WHERE CURRENT OF c1;
    
        COMMIT;
    
    end if;
    
    close c1;
    
    RETURN cnumber;
    
    END; 
    if you change the table name then the rowids will be different for the cursor table & updating table.
    you have to modify the code like
    declare
          cursor cust is
                          select * from ph2 
                          order by order_date, customer_id, ship_date, order_id, item_id
                          for update of order_id, item_id;
                          
          neworder_id  ph2_item.order_id%type;
          multicust multi_site_cust.cust%type;
          newitem_id ph2_item.item_id%type;
          
          
    begin
            /*Initialising neworder_id */
            select 
                  max(order_id)
            into
                  neworder_id
            from
                  sales_order;     
            
             /*Initialising newitem_id*/
             newitem_id := 0;
                      
             for c_rec in cust loop
             neworder_id := neworder_id + 1;
                 
                       if
                             ( c_rec.verify = 1 )
                       then
                              newitem_id := newitem_id + 1;
                       end if;    
                              
                       update 
                              ph2_item p
                            set
                                p.order_id = neworder_id,
                                p.item_id = newitem_id      
                       where customer_id = c_rec.customer_id;
                 
                   
              end loop;
    end;
    /  
This discussion has been closed.