CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqlprocedure
pininfarina
pininfarina

Reputation: 133

How can I write a PL/SQL procedure to copy tables and contents from another account

I need to write a PL/SQL procedure to create tables that match the ones in another account(I have access to that account). They need to have same columns and types. Also, they need to be filled with the same data

Help me!

EDIT:

SQL> CREATE OR REPLACE PROCEDURE MakeTables
  2  AS
  3  BEGIN
  4  EXECUTE IMMEDIATE
  5  'CREATE TABLE Table1 AS (SELECT * FROM ANOTHER_ACCT.Table1);
  6  CREATE TABLE Table2 AS (SELECT * FROM ANOTHER_ACCT.Table2);
  7  CREATE TABLE Table3 AS (SELECT * FROM ANOTHER_ACCT.Table3);
  8  CREATE TABLE Table4 AS (SELECT * FROM ANOTHER_ACCT.Table4)';
  9  END;
 10  /

Procedure created.

But when I run this I get this error:

SQL> BEGIN
  2  MakeTables;
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "BS135.MAKETABLES", line 4
ORA-06512: at line 2

Upvotes: 1

Views: 5465

Answers (1)

OldProgrammer
OldProgrammer

Reputation: 12169

When you say, another "account", do you mean, another "user/schema"? If so, this can be simple. Go read/google about "oracle create table as select". This lets you create a table from a select statement, so you could issue a statement such as 

create table new_table as select * from other_schema.old_table

You don't need any PL/SQL unless you wanted to automate the process for creating many tables. Then you could query the data dictionaries as a driver.

(also, please read on how to ask proper questions here: https://stackoverflow.com/questions/how-to-ask )

Upvotes: 2

Related Questions