Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Raju.

Asked: July 17, 2017 - 8:44 pm UTC

Last updated: July 26, 2017 - 3:45 am UTC

Version: 11 G

Viewed 1000+ times

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

Comments

Raju, July 25, 2017 - 12:17 am UTC

Thanks so much for your time. The solution which you provided is an InsertSelect won't work in my case as we don't want to use DB links between databases. I need to use only COPY command to copy the data from remote database to Local database. Please do help me if you have workaround for this issue (COPY) command issue.
Connor McDonald
July 25, 2017 - 7:28 am UTC

"as we don't want to use DB links between databases"

Why ?

That's like me saying: "Remove some data but I dont want to use the DELETE command"


A reader, July 25, 2017 - 7:40 am UTC

Thanks for the quick reply. DB Links are ruled out in this case as per organization policy. That's why I have chosen COPY command.
Connor McDonald
July 26, 2017 - 3:45 am UTC

The organization policy is wrong.

To "A reader" : some options

J. Laurindo Chiappa, July 26, 2017 - 7:25 pm UTC

See, the database link is just a connection to another database : Connor is right, no sense in forbidding connections... Maybe someone heard that passwords are visible in clear-text inside the database (it was true in very very old versions/releases, from 10gR2 iirc it is simply not true) but for fear of the unknow all the dblink are banned, for no reason in recebt versions.... No sense (and nonsense) at all...

Anyway, if dblinks are out of order and you need to 'dig without a showel', ie, work without the correct tool, you can try :

a) execute the COPY command in SQL Developer (running via script, with F5 key) or in SQLcl , both iirc donĀ“t have the same line limit as sqlplus

b) write a Java stored procedure that connect in the remote database without database link and do the needed actions

And let me say : BOTH options imho are much much LESS secure than dblinks, because you will type the user/password and destination db host and port - in a dblink all these things exists but they are encripted inside link$ and other system tables/views...

Regards,

J. Laurindo Chiappa

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.