Re: COPY command in PL/SQL

steven.fabijanski_at_allfirst.com (Steven Fabijanski) wrote in message news:<e627a433.0110260940.760789bb_at_posting.google.com>...
> 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

COPY is a sql*plus command not a sql command.

If don't have longs in your table you could use just as well
insert into <table>_at_database
select * from <table>

Tbis will be slower though.

Hth
Sybrand Bakker
Senior Oracle DBA Received on Tue Oct 30 2001 - 00:32:56 CET