I come from a MS SQL Server background so if I am going about this completely wrong please let me know.

I have a table of Lists, each of these Lists have Tasks. These tasks have dependencies on each other. We want to copy a List and its Tasks and those Tasks dependencies. There are other associated records for each Task as well.

A previous Oracle developer created a procedure to carry out the copying. Along with other stuff there is a loop from a Cursor which gets all the Tasks in the List being copied.

It loops through the cursor creating the new tasks and anything associated with them based on the old tasks. I have recently introduced the dependencies table.

A row would be: Id, SuccessorId, PredecessorId, DependencyType.

Now working on what I have already if I was in SQL Server I would create a table variable of OldId, NewId and with each new Task inserted based on an old one I would also insert a row into the table variable. Then I could INSERT SELECT the old dependencies using the table variable to get the new Ids.

In Oracle I have this at the package level:

```
  TYPE old_id_new_id_row       IS RECORD(old_id list_tasks.id%TYPE, new_id list_tasks.id%TYPE);
  TYPE t_old_task_ids_new_task_ids IS TABLE OF old_id_new_id_row INDEX BY BINARY_INTEGER;

```

Then in the proc declaration

```
    lt_old_ids_to_new_ids  t_old_task_ids_new_task_ids;

```

And I am trying to add a record to this a I go through the loop but there doesn't seem to be a way to do it. I can `BULK COLLECT INTO` it say the values of the old Ids for the dependencies but I'm not sure then how to update the `new_id` value for each row.

This example [here Example 5-21](https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm#BABFBHII) is about as close as I can find but I am struggling to see an example with records being added one at a time. So it could be:

```
SELECT lt.id AS old_id, -1 AS new_id
    BULK COLLECT INTO lt_old_ids_to_new_ids
    FROM list_tasks lt
    WHERE lt.list_id = 96;

```

But as I insert the new Task records in and get their Ids how would I get the New Id into match the Old Id?

As I say I am open to completely different, clever methods of doing this but I am working with the code I have. This feels like it should be a lot more simple than it has proved so far.

Edit: Apologies for my question not being clear. This isn't the procedure in question as it's too complex and too much irrelevant stuff going on, this is me trying to understand the concepts. Hopefully illustrates roughly what I am trying to do. I've updated in light of the one answer given as of this moment. This won't compile currently because of TABLE(mycoll) which I swear worked before with the Table Type collection.

```
    PROCEDURE test_temp_table(list_id       IN lists.id%TYPE
                            , io_cursor   IN OUT t_ref_cursor) IS

    CURSOR lt (pc_list_id    lists.id%TYPE) IS
        SELECT id
        FROM list_tasks
        WHERE list_id = pc_list_id
        ;        

    TYPE old_id_new_id_row2 IS RECORD(old_id NUMBER, new_id NUMBER);
    TYPE t_old_task_ids_new_task_ids2 IS TABLE OF old_id_new_id_row2 INDEX BY PLS_INTEGER;
    tt_old_task_ids_new_task_ids t_old_task_ids_new_task_ids2;
    c PLS_INTEGER;

  BEGIN
    DBMS_OUTPUT.ENABLE;

    FOR ltr IN lt(list_id) LOOP  
        tt_old_task_ids_new_task_ids(tt_old_task_ids_new_task_ids.count +1).old_id := ltr.id;
        --This would be set the new Id for an inserted task. 
        tt_old_task_ids_new_task_ids(tt_old_task_ids_new_task_ids.count +1).new_id := ltr.id + 100;

    END LOOP;

    FOR i IN tt_old_task_ids_new_task_ids.FIRST..tt_old_task_ids_new_task_ids.LAST
    LOOP
       --This works, could insert in loop but seems very inefficient and messy?
       DBMS_OUTPUT.PUT_LINE(tt_old_task_ids_new_task_ids(i).old_id || ' ' || tt_old_task_ids_new_task_ids(i).new_id);      
    END LOOP;

    --This would be the Inset Select rather than the returned cursor. 
    --INSERT INTO list_task_dependencies(
           --predecessor_task_id
           --, successor_task_id
          --, dependency_type
    --)    
    OPEN io_cursor FOR
    --Bits commented out that don't compile...
    SELECT DISTINCT --predecessor.new_id
           --, successor.new_id
           --, 
           ltd.dependency_type
    FROM list_task_dependencies ltd
          --TABLE(tt_old_task_ids_new_task_ids) ERRORS =
          --Error: PLS-00382: expression is of wrong type
          --Error: PL/SQL: ORA-22905: cannot access rows from a non-nested table item
         --JOIN TABLE(tt_old_task_ids_new_task_ids) predecessor ON predecessor.old_id = ltd.predecessor_task_id
         --JOIN TABLE(tt_old_task_ids_new_task_ids) successor ON successor.old_id = ltd.successor_task_id
    ;
  END test_temp_table;  

```

To help you understand where I am coming from it may be helpful to look at [here](https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-variables/) to see how table variables work in MS sql server. Essentially I want a variable which holds relational data that I can query like a table. I know about global temporary tables obviously but that seems like a very messy solution to me.