Oracle: Insert rowtype data into another table

Question

I have one table called event, and created another global temp table tmp_event with the same columns and definition with event. Is it possible to insert records in event to tmp_event using this ?

DECLARE
   v_record event%rowtype;
BEGIN 
   Insert into tmp_event values v_record;
END;

There are too many columns in event table, I want to try this because I don't want to list all the columns.

Forget to mention: I will use this in the trigger, can this v_record be the object :new after insert on EVENT table ?

Coagulase

updated the answer with use in trigger. – Arias

I have answered on similar question here: [https://mcmap.net/q/1170582/-in-an-oracle-trigger-can-i-assign-new-and-old-to-a-rowtype-variable][1] [1]: https://mcmap.net/q/1170582/-in-an-oracle-trigger-can-i-assign-new-and-old-to-a-rowtype-variable – Moya

Answer 1

To insert one row-

DECLARE
   v_record event%rowtype;
BEGIN 
   SELECT * INTO v_record from event where rownum=1; --or whatever where clause
   Insert into tmp_event values v_record;
END;

Or a more elaborate version to insert all rows from event-

DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF event%ROWTYPE;

  l_tab t_bulk_collect_test_tab;

  CURSOR c_data IS
    SELECT *
    FROM event;
BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data
    BULK COLLECT INTO l_tab LIMIT 10000;
    EXIT WHEN l_tab.count = 0;

    -- Process contents of collection here.
    Insert into tmp_event values v_record;
  END LOOP;
  CLOSE c_data;
END;
/

In a trigger, yes it is possible but its like the chicken or the egg. You have to initialize every field of the rowtype with the :new column values like-

v_record.col1 := :new.col1;
v_record.col2 := :new.col2;
v_record.col3 := :new.col3;
....

Apparently, the PLSQL examples above cannot be used in a trigger since it would throw a mutating trigger error. And there is no other way for you to get the entire row in the trigger other than accessing each column separately as I explain above, so if you do all this why not directly use :new.col in the INSERT into temp_event itself, will save you a lot of work.

---

Also since you say it's a lot of work to mention all the columns, (in Oracle 11gR2) here's a quick way of doing that by generating the INSERT statement and executing it dynamically (although not tested for performance).

CREATE OR REPLACE TRIGGER event_air --air stands for "after insert of row"
AFTER INSERT ON EVENT
FOR EACH ROW
   L_query varchar2(2000);   --size it appropriately
BEGIN

   SELECT 'INSERT INTO tmp_event VALUES ('|| listagg (':new.'||column_name, ',') 
                                           WITHIN GROUP (ORDER BY column_name) ||')' 
     INTO l_query
     FROM all_tab_columns
    WHERE table_name='EVENT';

   EXECUTE IMMEDIATE l_query;

EXCEPTION
    WHEN OTHERS THEN
        --Meaningful exception handling here
END;

Arias

I store the data into the tmp_event table is to avoid mutating trigger error. I update or insert into event table, and also add the new record into tmp_event table, then visit the content in tmp_table, will this work? – Coagulase

Yes it will. As long as you don't select from the table where the trigger is, it will not throw the mutating trigger error. – Arias

@LucM What feature? Mutating Trigger is an error that applies to all versions of Oracle database. – Arias

@Annjawn Forget it, I should have delete my question, I misread the answer. – Pathfinder

@Annjawn values should be ordered by column_id, not by column_name, otherwise you will insert them in wrong columns – Bluet

@Bluet sure, you can order it however you want, really depends on the target table’s structure. My target table could be a framework based generic logging table for all intents and purposes, and I might not care about column order at all, or I may care about it. Again, depends on what you’re trying to achieve. My answer is a general guidance on how to go about achieving what OP wanted. – Arias

When you order by column_id you will have the same order as in the source table (which is equivalent to inserting the whole row as record, what OP wanted), otherwise the colums will be mixed up. – Bluet

@Bluet as I said, depends on your target table and Your requirements. – Arias

These are in fact OP requirements: "I have one table called event, and created another global temp table tmp_event with the same columns and definition with event". – Bluet

@Annjawn There seems to be a typo in your 2nd code block. Insert into tmp_event values v_record; but the variable v_record is not declared. I think it is supposed to be Insert into tmp_event values l_tab; instead. Can you confirm? – Psychomotor

@plasmaTonic, in fact it needs to be something like FORALL i IN 1..l_tab.COUNT INSERT INTO tmp_event VALUES l_tab(i); – Bluet

Answer 2

There is a way to insert multiple rows into table with %Rowtype.

checkout below example.

DECLARE
TYPE v_test IS   TABLE OF TEST_TAB%rowtype;
  v_test_tab v_test ;


 EXECUTE immediate ' SELECT  * FROM TEST_TAB ' bulk collect INTO v_test_tab ;
  dbms_output.put_line('v_test_tab.count -->'||v_test_tab.count);
  FOR i IN 1..v_test_tab.count
  LOOP
    INSERT INTO TEST_TAB_1 VALUES v_test_tab
      (i
      ) ;
  END LOOP;

END;

Heavyarmed

Answer 3

sum up to full working excample ...

DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF event%ROWTYPE;
  l_tab t_bulk_collect_test_tab;
  CURSOR c_data IS SELECT * FROM event;
BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data
    BULK COLLECT INTO l_tab LIMIT 10000;
    EXIT WHEN l_tab.count = 0;
    FORALL i IN 1..l_tab.count
        Insert into tmp_event values l_tab(i);
    commit;
  END LOOP;
  CLOSE c_data;
END;
/

Gogh