ORA-1406: Fetched Column Value was Truncated When Selecting Remote Column into Local BLOB Variable (Doc ID 459557.1)

Last updated on FEBRUARY 06, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.1.0.7 [Release 10.2 to 11.1]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7
**Checked for relevance on 26-Dec-2013***


Symptoms

Materialized views can be used as an alternative solution to able to retrieve BLOB columns greater than 32KB - 1 in size through a database link .

Since when selecting  a remote column into local BLOB variable in which the length of remote column have a length of 2 Mb and more,the following error occurs:
ORA-01406 : fetched column value was truncated

DECLARE
my_ad LONG RAW;
my_tid VARCHAR2(20);
BEGIN
SELECT tid, fileobject INTO my_tid, my_ad FROM pbrs_v_document_instance_00 WHERE ROWNUM=1;
DBMS_OUTPUT.ENABLE(10000);
DBMS_OUTPUT.PUT_LINE ( ' Le tid s�lectionn� est ' || my_tid);
END;
/

 

Reference

Oracle� Database Application Developer's Guide - Large Objects 10g Release 2 (10.2)
What's New in Large Objects?
Restrictions Removed in Oracle Database 10g Release 2

Starting from 10g the  select from a lob object through a database link is supported by  receiving the LOB objects into variables defined as CHAR or RAW.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms