0

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!

wolφi
  • 8,091
  • 2
  • 35
  • 64

1 Answers1

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.

Andrew Sayer
  • 2,296
  • 1
  • 7
  • 9
  • Thank you for your answer, but this is not what I am looking for. SUBSTR(B) would obviously work, but, please read the question carefully, it runs smootly for three years, even without SUBSTR, so there must be another solution. – wolφi Nov 21 '20 at 05:31
  • 2
    Was the MView originally created before you upgraded the source DBs max string size? The defined length of the column is used by Oracle when deciding how large to define the columns on your local side, when the column size is upgraded, there is no DDL attempted on your local side so Oracle doesn’t care. You can manually create your MView table first with the old definition (varchar2(4000 char), which will be treated as 4000 byte), populate it manually and then use the “on prebuilt table” clause of the create MView statement. – Andrew Sayer Nov 21 '20 at 09:29
  • Good idea, and many thanks for your continued help! I checked the logs. In 2017, the source database was changed a week before we last changed the MView. I guess it took us this week to find the workaround, which, for some unclear reason, stopped working. – wolφi Nov 21 '20 at 11:17