storing rows from table using pl/sql

I have to call a procedure multiple times that then populates a tab开发者_开发百科le. Problem is the procedure truncates the table after each call. I have to store the results of the table for each run. I looked up the documentation and couldnt get an idea how to do this in pl/sql. Any ideas is much appreciated.

thanks a lot!

After each call of the procedure, copy the resulting data into another table with the same columns:

INSERT INTO TABLE_B
SELECT *
FROM TABLE_A;

When you're finished calling the procedure, all your data is in TABLE_B.

In PL/SQL, it looks like this:

BEGIN
  FOR I IN 1..10 LOOP

    PROC(I);

    INSERT INTO TABLE_B
    SELECT *
    FROM TABLE_A;

  END LOOP;

  PROCESS_ALL_DATA();
END;

Update:

If you don't have permissions to create tables, then you could store the partial results in a PL/SQL table (in memory):

DECLARE
  TYPE T_T_A IS TABLE OF A%TYPE;

  L_IMED_TABLE T_T_A;

BEGIN
  FOR I IN 1..10 LOOP

    PROC(I);

    SELECT * BULK COLLECT INTO L_IMED_TABLE
    FROM A;

  END LOOP;

  FOR I IN L_IMED_TABLE.FIRST .. L_IMED_TABLE.LAST LOOP
    PROCESS_RESULT_ROW( L_IMED_TABLE(I) );
  END LOOP;

END;

You can have trigger that gets triggered on insert to the main table and populates the same data to a archive table, where you can add more columns like timestamp and call counter and more.