Oracle Procedure to Copy data view into table

Question

I would need to copy or insert data from my_view (that is based on 3 joined tables). Before that, I've created table my_table based on my_view . For the procedure I've used the below :

create or replace procedure view_copy(
            my_view in varchar2, 
            my_table in varchar2)
is
begin
     execute immediate 'insert into '||my_table||' (select * from '||my_view||')';
end;

The data are not copied to my_table. Is it possible to copy data from view to table? How can I write the procedure differently?

Of course it is allowed. But if you don't specify the columns, then your SQL is probably generating an error. — Gordon Linoff
Would a materialized view work instead of a view? Then you would not need to copy from the view to a table. — MT0

Aleksej Aleksej · Accepted Answer · 2016-02-24T22:43:36

Your procedure works; if you can't find data in your target table, maybe you should check your view. A simple test on your procedure:

SQL> create table table1 ( id1 number)
  2  /

Table created.

SQL> create table table2 ( id2 number)
  2  /

Table created.

SQL> create or replace view v_t1_t2 as select id1, id2 from table1 cross join table2
  2  /

View created.

SQL> create table table1_2 ( id1 number, id2 number)
  2  /

Table created.

SQL> create or replace procedure view_copy(
  2              my_view in varchar2,
  3              my_table in varchar2)
  4  is
  5  begin
  6       execute immediate 'insert into '||my_table||' (select * from '||my_view||')';
  7  end;
  8  /

Procedure created.

SQL> insert into table1 values (1);

1 row created.

SQL> insert into table2 values (2);

1 row created.

SQL> exec view_copy('v_t1_t2', 'table1_2');

PL/SQL procedure successfully completed.

SQL> select * from table1_2;

       ID1        ID2
---------- ----------
         1          2

Oracle Procedure to Copy data view into table

1 Answers