I need the fastest way how to copy a table in PL/SQL
My first opinion was to use standart
CREATE TABLE A as (SELECT * FROM B);
So in PL/SQL
execute immediate 'CREATE TABLE A as (SELECT * FROM B)';
but this ends with error:
ERROR on row 1:
ORA-06550: row 1, column 17:
PLS-00103: found symbol "create table a as (select * from application_log)" in situation when was expected one of following possibilities:
:= . ( @ % ;
I'm sorry but i was author of translation because I have our national version of client.
First ask yourself do you REALLY want to do that? As it is your first post here, it seems that you are an Oracle newbie. Do you, perhaps, have an MS SQL Server background? I'm asking this question because many MS SQL Server users tend to create "temporary tables" in Oracle just like they used to do before. Oracle works differently, so - maybe you should explain WHY would you want to create a table using PL/SQL procedure?
If you, however, insist on doing that, here's an example:
2 str varchar2(200);
4 str := 'create table dept_copy as select * from dept';
5 execute immediate (str);
PL/SQL procedure successfully completed.
SQL> select * from dept_copy;
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
I'm sorry, I have to put my next posts more carefully.
Main problem was caused, that source is not table but materialized view and I really need to create copy as backup, because I need to be able to track changes and that materialized view has full refresh (not my idea and the second database is not under my control ). That's the reason that this doesn't work. And that's also not possible to transfer data more time through databases.
Now I have no time for deep investigation because I have a lot of other work. But I will describe finale solution later. But thanks for help. Any ideas are welcomed.
Yes I agree it should work. It works normally when executed as normal sql,
but in execute immediate it writes error message I described in my original post.
As I tried execute it through dbms_sql package I got not enough privileges error message. For me problem is solved by workaround which is slower by i don't care about it much now. Later (I hope till end of week) I will solve it better way and let all know solution.