In Oracle SQL Plus, using Copy command while creating table/inserting record always converting Table CHAR /VARCHAR2 Columns to BYTE format and causing issue in length mismatch, so im not able to re copy to my original tables.
Tried below setting in SESSION and sqlnet.ora, but no help
ALTER SESSION SET NLS_LENGTH_SEMANTICS = CHAR;
connected.
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)
Actual should be replica of DDD1 Table.
The documentation for the COPY command says:
The COPY command is not being enhanced to handle datatypes or features introduced with, or after Oracle8i. The COPY command is likely to be made obsolete in a future release.
Character length semantics were introduced in Oracle 9i:
Oracle9i introduces character semantics. It is useful for defining the storage ...
So while it's maybe a bit disappointing, it maybe isn't surprising that COPY doesn't honour that 'new' feature; and it isn't something they're likely to add in now. You are creating a new connection/session to the remote DB, and that new session would have to have the alter session re-issued after the connection is made. As that isn't happening it's defaulting to the target's database initialisation parameter value, which is BYTE.
It's perhaps slightly more surprising that SQL Developer does apparently re-issue that alter session. Perhaps more usefully for you, if you want to use a command-line tool for this instead of a GUI, so does SQLcl. You'll have to use one of those rather than SQL*Plus if you want to utilise that command and preserve the source semantics.
Another option may be to start from your current target database, set the session parameter there, and copy from the current source database instead. Or, if you can't do that, you could perhaps have a DB trigger that issues an alter session, but that's probably getting a bit to complicated now...
It's also worth noting MoS document ID 2914.1 "Common SQL*Plus Questions and Answers" which says:
Q: What is the purpose of the COPY command ?
A: Copy command in SQL*Plus is useful when you want to transfer data between Oracle and NON-Oracle databases. If you want to copy Oracle table to another Oracle table, use "create table .. as select .." or "create table, insert into .. select .." statements. Columns may lose precision if you copy from Oracle rdbms to Oracle rdbms.
While that doesn't really give an alternative for copy to another database, you could use a database link; or export/import.