title: How can I combine rowid into a collection of the same rowtype as a table?
body: <blockquote>
    <p><strong>Background</strong></p>
    
    <p>From Nicholas Krasnov and krokodilko's answer in the question
    <a href="https://stackoverflow.com/questions/40958623/how-can-i-create-a-collection-of-the-same-type-as-a-record-in-a-table">How can I create a collection of the same type as a record in a table?</a>,
    I found that you can use the %ROWTYPE attribute to create a collection
    of the same type as a table.</p>
    
    <p>However, I need to also capture the ROWID of the table in the
    collection, as I will be using it to delete those rows.</p>
  </blockquote>
  
  <hr>
  
  <blockquote>
    <p><strong>Question</strong></p>
    
    <p>How can I combine ROWID into a collection of the same rowtype as a
    table?</p>
  </blockquote>
  
  <hr>
  
  <blockquote>
    <p><strong>What I've already tried</strong></p>
    
    <p>I created a nested type with the original type that is of the same
    rowtype, with an additional field containing the rowid.
    Code Sample below:</p>
  </blockquote>
  
  <pre><code>create or replace procedure p_test as
  l_cur_limit pls_integer := 500;
  
  type rec_with_rowid is record
  (
      t1 mytable%rowtype,
      row_id rowid
  );
  
  type tab_to_be_moved is table of rec_with_rowid;
  
  l_to_be_moved tab_to_be_moved;
  
  cursor c_get_to_be_moved is
  select mytable.*, mytable.rowid
  from mytable;
  
  begin  
  
  open c_get_to_be_moved;
  loop
      fetch c_get_to_be_moved
      bulk collect into l_to_be_moved limit l_cur_limit; --Fails here, as the collection
                                                         --is not of the same type
      exit when l_to_be_moved.count = 0;      
  
      for i in 1.. l_to_be_moved.count loop
          begin
              insert into mytable@remotedb values l_to_be_moved(i);
          exception
              when others then
                  dbms_output.put_line(sqlerrm);
                  l_to_be_moved.delete(i);
          end;    
      end loop;
  --        forall i in 1.. l_to_be_moved.count
  -- Need rowid to delete, as the audit tables don't have PK for performance reasons
      commit;
  end loop;          
  close c_get_to_be_moved;
  
  exception
      when others then
          dbms_output.put_line(sqlerrm);
  
  end;
  
  /
  </code></pre>