Oracle: Cannot copy data from a max_string_size=extended to a max_string_size=standard database

Question

We have a Oracle 12.1 database where MAX_STRING_SIZE has been set to EXTENDED, enabling the use of VARCHAR2(32767) columns. We copy data via a materialized view and a database link to an Oracle 11.2 database. Everything runs smootly for three years, until we drop and recreate the mview (in dev) and get an error

ORA-00910: specified length too long for its datatype

A minimal test case has a table on the source database (12.1). Please note that the actual values are really short and don't use the declared length. So this is only about the declaration and not about the data:

CREATE TABLE sematest (
  vc_char VARCHAR2(4000 CHAR),
  vc_byte VARCHAR2(4000 BYTE)
);
INSERT INTO sematest VALUES ('char','byte');

The columns are defined as:

SELECT column_name,data_type,data_length,char_length,char_used 
  FROM user_tab_columns 
 WHERE table_name = 'SEMATEST';

COLUMN_NAME DATA_TYPE DATA_LENGHT CHAR_LENGTH CHAR_USED
VC_CHAR     VARCHAR2        16000        4000         C
VC_BYTE     VARCHAR2         4000        4000         B

The target database (11.2, but it is identical on 12.1 if the extended datatypes are not enabled) can easily copy the data_length=4000 column, but not the data_lenght=16000 column:

CREATE MATERIALIZED VIEW test_char BUILD DEFERRED REFRESH COMPLETE ON DEMAND AS
SELECT vc_char FROM sematest@dblink;
ORA-00910: specified length too long for its datatype

CREATE MATERIALIZED VIEW test_byte BUILD DEFERRED REFRESH COMPLETE ON DEMAND AS
SELECT vc_byte FROM sematest@dblink;
Materialized view TEST_BYTE created.

To confuse things more, this behaviour depends on BUILD DEFERRED. With BUILD IMMEDIATE, everything is fine:

CREATE MATERIALIZED VIEW test_char BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS
SELECT vc_char FROM sematest@dblink;
Materialized view TEST_BYTE created

The really upsetting bit is that we have solved this problem three years ago, but forgot how. Please do help!

Answer 1

You can use SUBSTRB (Docs) to take a substring of the remote 16000 byte column limited to 4000 bytes. Note that if you do have any rows that have values with character length <= 4000 but byte length > 4000 (due to mutlibyte characters) then you will loose that additional data (you've got nowhere to put it in a varchar2).

In newer versions, you could probably cast the column as a CLOB but I don't think that will work on your 11.2 DB. You'd need to upgrade to 12.2 so that clobs are more easily transferred over a database link. If you are planning on upgrading any time soon, you might consider using the extended string size for the MVIEW DB instead.