Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2008
    Posts
    3

    Unanswered: pl/sql cpy table

    Hello,

    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.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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:
    Code:
    SQL> declare
      2    str varchar2(200);
      3  begin
      4    str := 'create table dept_copy as select * from dept';
      5    execute immediate (str);
      6  end;
      7  /
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from dept_copy;
    
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL>

  3. #3
    Join Date
    Feb 2008
    Posts
    3
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by kostecky
    Main problem was caused, that source is not table but materialized view and I really need to create copy as backup
    create table dept_copy as select * from dept should work with a MV as the source as well.
    What is the error that you are getting?

  5. #5
    Join Date
    Feb 2008
    Posts
    3
    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.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by kostecky
    but in execute immediate it writes error message I described in my original post.
    Your original post contained invalid SQL (the SELECT part must not be enclosed in brackets)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •