Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00900: invalid SQL statement
ORA-00900: invalid SQL statement [message #298588] Wed, 06 February 2008 11:00 Go to next message
ramsatish
Messages: 8
Registered: November 2006
Junior Member
Hi,

I am trying to copy some data from remote database using SQL*Plus COPY Command.For that I created a procedure

CREATE OR REPLACE PROCEDURE aa_test
AS
V_CREATE VARCHAR2(1700);
BEGIN
V_CREATE:='copy from src/srcpas@srcdatasource
to dest/destpass@destsource creata aaa using select nname from ak.person';
EXECUTE IMMEDIATE V_CREATE;
END;

Procedure was created Successfully

when I try to execute the procedure(exec aa_test) it is giving the following error

ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "TARUN.AA_TEST", line 7
ORA-06512: at line 1


Please help me out in resolving this error.

Thanks in Advance

Regards.
Sriram Satish

[Updated on: Wed, 06 February 2008 11:01]

Report message to a moderator

Re: ORA-00900: invalid SQL statement [message #298590 is a reply to message #298588] Wed, 06 February 2008 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
COPY is only valid within SQL*Plus, AFAIK.
Re: ORA-00900: invalid SQL statement [message #298594 is a reply to message #298588] Wed, 06 February 2008 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is a (obsolete) SQL*Plus command.

Regards
Michel
Re: ORA-00900: invalid SQL statement [message #298719 is a reply to message #298594] Thu, 07 February 2008 05:52 Go to previous messageGo to next message
ramsatish
Messages: 8
Registered: November 2006
Junior Member
Hi Michel.
Thank you for your response.
Could you please guide me how to use Sql* plus copy command in an oracle stored procedure.

Thanks In Advance

Regards,
Sriram Satish

Re: ORA-00900: invalid SQL statement [message #298729 is a reply to message #298719] Thu, 07 February 2008 06:19 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I am trying to copy some data from remote database

I'd use database link (or perhaps export/import utilities).
Re: ORA-00900: invalid SQL statement [message #298768 is a reply to message #298719] Thu, 07 February 2008 11:05 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use a SQL*Plus command in a procedure.
You have to create a database link and use "insert ... select ... from mytable@mydblink".

Edit: I already told you that in your previous topic: http://www.orafaq.com/forum/mv/msg/96748/298027/102589/#msg_298027

Regards
Michel

[Updated on: Thu, 07 February 2008 11:07]

Report message to a moderator

Previous Topic: More than 4000 characters in PL/QL variable
Next Topic: A Query Which Looks Ahead....
Goto Forum:
  


Current Time: Sun Dec 04 00:47:00 CST 2016

Total time taken to generate the page: 0.26617 seconds