Thanks for the question, Pradeep.
Asked: July 29, 2019 - 2:55 pm UTC
Last updated: July 30, 2019 - 8:14 am UTC
Version: 11gR2
Viewed 1000+ times
You Asked
In the Table, 1 Character is being converted to 4 Bytes while using Oracle copy command to copy data.
While inserting back to 1 Char column facing issue as Column value too long.
SQL> DESC DDD1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROW_ID NOT NULL VARCHAR2(15 CHAR)
PRIV_FLG NOT NULL CHAR(1 CHAR)
SQL> COPY TO USER/PASS@DB CREATE DDD12 USING SELECT * FROM DDD1;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table DDD12 created.
5036 rows selected from DEFAULT HOST connection.
5036 rows inserted into DDD12.
5036 rows committed into DDD12 at USER/PASS@DB.
SQL> DESC DDD12;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROW_ID NOT NULL VARCHAR2(60)
PRIV_FLG NOT NULL CHAR(4)
SQL> COPY TO USER/PASS@db INSERT DDD1 USING SELECT * FROM DDD12;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
ERROR:
ORA-12899: value too large for column "R4EIMUSER"."DDD1"."PRIV_FLG" (actual: 4,
maximum: 1)
and Connor said...
From the docs:
"12.16 COPY
The COPY command is not being enhanced to handle datatypes or features introduced with, or after Oracle8i. The COPY command is likely to be deprecated in a future release."So it won't handle the newer syntax etc. The workaround, as I'm sure you've already discovered is to precreate the table. The other option is use SQLCL:
SQL> COPY TO xxxxxx/xxxxxxx@db18_pdb1 CREATE t1 USING SELECT * FROM t;
Array fetch/bind size is 15 (less if there are longs or blobs). (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table T1 created.
1 rows selected from DEFAULT HOST connection.
1 rows inserted into T1.
1 rows committed into T1 at xxxxxx@db18_pdb1.
SQL> desc t1
Name Null? Type
---- ----- -----------------
X VARCHAR2(15 CHAR)