Home » SQL & PL/SQL » SQL & PL/SQL » Using COPY in a LOOP
Using COPY in a LOOP [message #36368] Wed, 21 November 2001 14:07 Go to next message
Alex Ponce
Messages: 4
Registered: November 2001
Junior Member
I need to move Rows from a table that contains a LONG column. I've tried seveal approaches but the only one that will get all the content from the LONG columns seems to be the SQL*Plus COPY command.

My problem now is using the COPY command in a LOOP. I can't get it to accept parameters. This is requiered since I'm moving lots of rows at once.

If I run the COPY instruction directly in SQL*Plus I don't get any errors and it runs fine. If I use it in a LOOP I get the following message.

COPY TO USERNAME/PASSWORD@SID INSERT TABLE USING SELECT * FROM TABLE WHERE REFERENCE = Row.REFERENCE;
*
ERROR at line 15:
ORA-06550: line 15, column 16:
PLS-00103: Encountered the symbol "TO" when expecting one of the following:
:= . ( @ % ;
The symbol ":= was inserted before "TO" to continue.
ORA-06550: line 15, column 49:
PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:
. ( * @ & = - + ; < / > in mod not rem an exponent (**)
<> or != or ~= >= <= <> and or like between is null is not ||
is dangling

-- THIS IS THE SCRIPT
DECLARE
CURSOR curArchiveCalls IS
SELECT NUMBER, REFERENCE
FROM TABLE
WHERE NUMBER IN (1228546,1237619);

Row curArchiveCalls%RowType;

BEGIN

OPEN curArchiveCalls;
LOOP
FETCH curArchiveCalls INTO Row;
EXIT WHEN curArchiveCalls%NOTFOUND;
COPY TO USERNAME/PASSWORD@SID INSERT TABLE USING SELECT * FROM TABLE WHERE ASSET_REF = Row.COLUMN;
DBMS_OUTPUT.PUT_LINE(Row.CALL_NUMBER);

END LOOP;
CLOSE curArchiveCalls;
END;
/

Any help will be greatly appreciated!

AP

----------------------------------------------------------------------
Re: Using COPY in a LOOP [message #36385 is a reply to message #36368] Fri, 23 November 2001 08:11 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I think the copy command is exclusive to sql/pls, so an anonomous pl/sql block won't understand it. If it's not exclusively then using dynamic sql would be worth a try:
execute immediate 'copy...= Row.COLUMN';

Alternately if it is a sqlplus script, then spool the commands to a file and then run that.
set echo off feedback off pagesize 0
spool copy_tabs.sql
select 'copy.....'||table_name||'...;' from TABLE
WHERE NUMBER IN (1228546,1237619);
spool off
@copy_tabs.sql

----------------------------------------------------------------------
Previous Topic: HOW TO SET VALUE TO A VARIALBLE DECLARED IN THE PACKAGE WITH THE HELP OF ORACLE STORED PROCEDURE THA
Next Topic: sqlloader
Goto Forum:
  


Current Time: Thu Feb 20 22:56:53 CST 2020