OGG-00665 OCI Error writing LOB (small) for column BODY (2), stmt INSERT INTO...

ALLSTARS_ORACLE

test用户的mail表的body字段是clob类型，然后报错
ERROR   OGG-00665  Oracle GoldenGate Delivery for Oracle, rep1.prm:  OCI Error writing LOB (small) for column BODY (2), stmt INSERT INTO "TEST"."MAIL"  (status = 24801-ORA-24801: illegal parameter value in OCI lob function)

Maclean Liu(刘相兵

Oracle GoldenGate Replicat fails with "OGG-00665 OCI Error Writing LOB (small) For Column <column name>"

Oracle GoldenGate - Version 11.2.1.0.0 and later
Information in this document applies to any platform.
Symptoms

Replicat fails with following error

2012-06-12 13:37:22 ERROR OGG-00665 OCI Error writing LOB (small) for column ANSWER (6), stmt INSERT INTO "TLC"."LC$LOAN_QA"
(status = 24801-ORA-24801: illegal parameter value in OCI lob function), SQL.




Cause

1) This is due to incorrect NLS_LANG character set value used by the Replicat.

2) Bug 15923407
Solution

1) Check your replicat report file to look for following section.

Database Language and Character Set:
NLS_LANG = ".US7ASCII" <-- Denotes the value used by the replicat
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "AL32UTF8" <-- Denotes the value of the target db setting

The NLS_LANG in the replicat parameter file should match the SOURCE database/ Extract NLS_LANG character set value.

Source extract was using AL32UTF8 correctly matching the databse NLS_LANG character set
Replicat was using the incorrect NLS_LANG character set causing the issue. This can be resolved by using
SETENV (NLS_LANG="<value>") in the replicat parameter

The position of SETENV parameter does matter. It should be BEFORE the USERID like below

replicat <replicat name>
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
userid <id> password <pw>

2) If replicat is correctly using the source NLS_LANG then this is due to bug 15923407 .

Fix is included in 11.2.1.0.6. Please check with OGG support team if the fix is needed on earlier versions of OGG.

Maclean Liu(刘相兵

该问题 可能与 SETENV  的位置有关 应当放在 userid 之前

replicat <replicat name>
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
userid <id> password <pw>