I think your issue is related to data type mismatches or inconsistencies during the data transfer process. The copy process fails when you have a character not represented in VARCHAR2.
Ensure that the Oracle database's character set supports the characters present in your Azure SQL data. Unicode characters might not be correctly represented in databases with non-Unicode character sets. For instance, AL32UTF8 is a common Unicode character set for Oracle databases.
Also, heck if any rows in your Azure SQL data have values in the NVARCHAR(4000) column that exceed 4000 characters when represented in Oracle's character set. If yes, you'll need to trim, split, or otherwise manage these values.
Instead of directly copying data from Azure SQL to Oracle, consider copying the data into an intermediate format like Parquet or CSV. From there, you can load the data into Oracle. This might give you more flexibility in terms of data transformation and cleansing.
In the ADF copy activity, you can define data type mapping. Define the source type
NVARCHAR to map to the Oracle
VARCHAR2(4000 CHAR). Ensure that the data does not contain characters that are not supported in the Oracle VARCHAR2 datatype.