Using COPY FROM with EXECUTE IMMEDIATE

[aswolff]

Hello I have a variable:

v_CPY_STMT := 'COPY FROM user/password@FP00.world....etc'

then I try in PL/SQL block:

EXECUTE IMMEDIATE v_CPY_STMT;

And get error:
ORA-00900: invalid SQL statement
ORA-06512: at line 33

Is use of COPY FROM forbidden in EXECUTE IMMEDIATE? What is my other option?


Thanks,

 

[SantaMufasa]

Aswolff, You would have to type a lot before you reach the limit in Tek-Tips to the number of characters they allow, so, please post the entire "COPY" statement with which you are having problems. (There may be something in the missing/"et cetera" code that accounts for your problem.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[aswolff]

set echo off;
set serveroutput on size 900000;
spool query.out;

DECLARE
   v_TBLNAME_D          VARCHAR2(20);
   v_TBLNAME_S          VARCHAR2(20);
   v_CPY_STMT           VARCHAR2(100);

   CURSOR sat_cursor
   IS
      SELECT sat.table_name
       FROM sys.all_tables sat
       WHERE sat.owner = v_SCHEMA_D
       AND   sat.table_name = 'CUCODES'
       ORDER BY TABLE_NAME;

   sat_data sat_cursor%ROWTYPE;

BEGIN

   OPEN sat_cursor;
   LOOP
           FETCH sat_cursor
           INTO sat_data;

           EXIT when sat_cursor%NOTFOUND;

           v_TBLNAME_D := sat_data.table_name;
           v_TBLNAME_S := sat_data.table_name;
           v_CPY_STMT := 'COPY FROM scott/tiger@FP00.world REPLACE ' || v_
TBLNAME_D || ' using SELECT * FROM ' || v_TBLNAME_S || ';';
           dbms_output.put_line(v_CPY_STMT);
           EXECUTE IMMEDIATE v_CPY_STMT;
   END LOOP;

   CLOSE sat_cursor;
END;
/
EXIT

 

[SantaMufasa]

aswolff,

Now that I have cleared the morning cobwebs from my head, I can clarify that:

1) The "EXECUTE IMMEDIATE" command executes only SQL commands, and equally (if not more) importantly,

2) The "COPY..." command is not SQL...it is SQL*Plus, and, therefore, EXECUTE IMMEDIATE cannot process such a command. (It would be similar to our expecting EXECUTE IMMEDIATE to process "COLUMN...", "BREAK ON...", "SET FEEDBACK ON", or any other SQL*Plus commands.

As an alternative to the "COPY" command, PL/SQL allows you to use database links. That's what I use in you circumstance.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[aswolff]

Ok...I had started using DBLINK but ran into TEMP space problems.

First I would truncate the table then do:

INSERT INTO table_1 from (SELECT * FROM table_2@dblink)

Some of these tables have 500,000+ rows so I'd like to commit every 2000 rows or so.

How can I get it to commit every 2000 rows?

Thanks for the feedback.

 

[SantaMufasa]

Any method that attempts to manage COMMITs every n number of rows would be rather costly (in performance). What were the TEMP space problems that you were running into? How often are you performing these types of 500K-row INSERTs?...And why? (I'm not prying...I'm just wanting to help you achieve your objectives in the "best" way possible.)


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[aswolff]

We have a production ERP system that needs to be copied over weekly to our TEST environment. So it's 3500+ tables with 100+ tables in the 500K row range and growing.

 

[SantaMufasa]

Then would not a simple Oracle exp/imp/data pump solution be far more efficient than network-based INSERTs?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[aswolff]

I've looked at that option but the databases are on different servers and I do not want to manually be moving files around.

 

[SantaMufasa]

What operating systems are involved? Are files on Server A not directly mappable for access (without moving) to Server B?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[aswolff]

Solaris. No NFS mounts based on company policy. I'd have to write some ftp script to move the dump file before doing the imp.