ORA-23605 reported by the Streams Apply when replicating a > 4K BLOB column and using an Add_Column Transformation (Doc ID 1483264.1)

Last updated on AUGUST 13, 2012

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.5.0 to 11.1.0.7 [Release 10.2 to 11.1]
Information in this document applies to any platform.

Symptoms

Streams Apply process fails with ORA-23605 for LCRs which contain LOB column values greater than 4K in size.

The source table has a BLOB column in it and a declarative transformation which adds a column to the table and specifies the new column value as 'OLD'. In addition to that the added column is part of a primary key on the target table.

On printing the transaction there is an old value in a LOB WRITE LCR which corresponds to the column added in the declarative transformation as detailed below

STRMADMIN@rbmlq4 > exec print_transaction('151.33.36510');
----- Local Transaction ID: 151.33.36510                                        
----- Source Database: SGIBA                                                    
----Error in Message: 3                                                        
----Error Number: 23605                                                        
----Message Text: ORA-23605: valor inválido "" para o parâmetro STREAMS        
command_type

--message: 3                                                                    
type name: SYS.LCR$_ROW_RECORD                                                  
source database: SGIBA                                                          
owner: INMETRO                                                                  
object: HW_FUNCIONARIOS                                                        
is tag null: Y                                                                  
command_type: LOB WRITE                                                        
old(1): SRV_UNF_ID        <<<<<<<<<<<<<<<<<<<< Problem old column value which should not be present                                              
30                                                                              
new(1): FUN_ID                                                                  
5405                                                                            
new(2): IM_ASSINATURA                                                          
BLOB Value                                                                      
new(3): SRV_UNF_ID                                                              
30

This is also confirmed from the apply trace in which you get

knllrvalint: ORA-23605 LOB_WRITE should have zero old cols
                     AND non-zero new columns

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