COPY command in PL/SQL

I need to move data between two dbs on a daily basis. It's not a large amount of data and I was using the
COPY TO/FROM function from sql*plus without problems. ex.

COPY TO otherdb/user_at_password INSERT mytable -
(field1, field2...) using SELECT * from myothertable;

This worked fine from within a simple sql script. I wanted to package it as procedure and it's no longer working from within the stored procedure--though it compiles without error. I'm using Oracle 8i and have tried using EXECUTE IMMEDIATE and putting the command string in quotes. I've also tried putting the semi-colon inside the quotes and without the semi-colon. ex.

EXECUTE IMMEDIATE 'COPY TO otherdb/user_at_password INSERTmytable
(field1, field2...) using SELECT * from myothertable';

Everytime I run the procedure I get the following error: *
ERROR at line 1:
ORA-00900: invalid SQL statement

Any ideas why this doesn't work in stored procedures? Is there another way to accomplish this within a stored procedure?

Thanks in advance,

Steven Fabijanski Received on Fri Oct 26 2001 - 19:40:50 CEST