You Asked
Hi Tom,
I am facing this issue with COPY command. I am trying to copy data from remote database to local database and the table has more than 90 fields and again i am using TO_CHAR() in SELECT statements. I am getting the below error.. Could you please suggest a work around?
Example:
---------
SPOOL C:\test1.log;
SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY HH:MI:SS') FROM DUAL;
SELECT * FROM PSDBOWNER;
Show User;
SET ECHO ON
SET LINE 5000
SET PAGESIZE 1000
SET LONG 100000000
select name from v$database;
TRUNCATE TABLE PS_TEST_ARCH_BKP01;
COPY FROM USERID/xxxxx@PRD TO USERID/xxxxx@DEV -
insert PS_TEST_ARCH_BKP01 (TST_PC_ROWNUM,-
PROCESS_INSTANCE,-
LASTUPDDTTM,-
LASTUPDOPRID,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
COLUMN,-
PHONE)-
USING SELECT TST_PC_ROWNUM,-
PROCESS_INSTANCE,-
TO_CHAR(LASTUPDDTTM),-
LASTUPDOPRID,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
COLUMN_VAL,-
TO_CHAR(PS_TIMESTAMP),-
PHONE from PS_TEST_ARCH WHERE PROCESS_INSTANCE IN (3352563);
SPOOL OFF;
Note: -
DATETIME and PS_TIMESTAMP columns are DateTime data type fields but converting those two columns to VARCHAR2 before copying data.. while copying I am using TO_CHAR() for those two data types.
ERROR:
======
SP2-0027: Input is too long (> 2499 characters) - line ignored
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 100000000. (long is 100000000)
SP2-0502:
SP2-0503: *
SP2-0501: Error in SELECT statement: ORA-00936: missing expression
SQL> SP2-0734: unknown command beginning " " - rest of line ignored.
and Connor said...
Unfortunately COPY is a single line command, ie, unlike a SELECT statement, in SQL Plus it must be on one line.
And a single line is limited to 2499 chars (v11) and 4999 (v12)
But easily solved - rather than using COPY, just use normal insert-select
insert into t (
col1,
col2,
col3
...
)
select
col1,
col2,
col3
...
from t
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment