| Using COPY in a LOOP [message #36368] |
Wed, 21 November 2001 14:07  |
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  |
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
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
