报错

使用ora2pg导出报错如下：
ERROR: ORA-24345: A Truncation or null fetch error occurred (DBD SUCCESS_WITH_INFO: OCIStmtFetch, LongReadLen too small and/or LongTruncOk not set)

分析

首先确认clob里面文本的字节大小

select max(dbms_lob.getlenth(restore_detail))/1024/1024 as size_mb from hs_dbt.dbt_restore;
复制

过程很残忍，官方文档说，添加LONGREADLEN就能解决，虽然我指定了LONGREADLEN 170Mb仍然报这个错误，最终是加的USE_LOB_LOCATOR 1，虽然说这个默认值就是1，但是必须显示的指定，我也是醉了。最后也验证了下，clob里面的内容也没被截断。
cat ora2pg.conf

# Oracle 客户端的 ORACLE_HOME
ORACLE_HOME /home/oracle
# 源端连接串  MySQL如:   dbi:mysql:host=192.168.1.10;database=tpch;port=3306
ORACLE_DSN  dbi:Oracle:host=10.20.137.153;sid=orcl;port=1521
# dbi:Oracle:host=192.168.1.29;service_name=pdb1;port=1521

PG_VERSION 13
TRUNCATE_TABLE       1 #If set 1, a TRUNCATE TABLE instruction will be add before loading data. This is usable only during INSERT or COPY export type.
# PG_NUMERIC_TYPE  1 # PG_NUMERIC_TYPE为1时，ora2pg会将Oracle中的NUMBER(p, s)近似地转换为PG的内部类型，如 real 和 float 类型。这种转换可能导致一些舍入误差。所以我们一般不会设置为1
PG_NUMERIC_TYPE      0
PG_INTEGER_TYPE      0
#PG_INTEGER_TYPE     1 #如果设置为1，则将numeric替换为PG内部类型。Oracle数据类型NUMBER(n)或NUMBER将根据精度值转换为smallint、integer或bigint PG数据类型。如果不带精度的NUMBER设置为DEFAULT_NUMERIC。

EXPORT_SCHEMA        1 #ALTER SCHEMA fund60trans1 OWNER TO fund60trans1;  SET search_path = fund60trans1,public;
DROP_IF_EXISTS       1 #CREATE SCHEMA IF NOT EXISTS fund60trans1;
PREFIX_PARTITION     1 #导出的分区表加上主表文件名前缀
PREFIX_SUB_PARTITION 1 #同上，针对的对象是子分区
FILE_PER_CONSTRAINT  1 #将导出的约束单独放在一个文件中
FILE_PER_INDEX       1 #将导出的索引单独放在一个文件中
FILE_PER_FKEYS       1 #将导出的外键放在单独的文件中
USE_RESERVED_WORDS   1 #如果oracle中导出的表名或列名有关键字，则导出时自动为其加上双引号，尽量询问应用看能否更改PG中的表名或字段名
TRANSACTION readonly   #设置为只读事务，避免误操作Oracle端数据
DISABLE_UNLOGGED     1 #禁止转换unlogged表，避免出现unlogged表
REPLACE_AS_BOOLEAN   0 #设置为1 number(1) 转换成 boolean, 要指定为0

#DEFAULT_NUMERIC     float
# 只有当 PG_INTEGER_TYPE 为true时，不带精度的NUMBER才会默认转换为bigint。您可以将该值改写为任何PG类型，如整数或浮点。
DEFAULT_NUMERIC      numeric
varchar2:varchar2
JOBS                 6
USER_GRANTS          1 #resolve HINT: you should activate USER_GRANTS for a connection without DBA privilege.

USE_LOB_LOCATOR      1 #clob large ERROR: ORA-24345: A Truncation or null fetch error occurred (DBD SUCCESS_WITH_INFO: OCIStmtFetch, LongReadLen too small and/or LongTruncOk not set)
LONGREADLEN          170Mb
# LONGTRUNKOK          1
NLS_LANG    AMERICAN_AMERICA.UTF8
复制

https://ora2pg.darold.net/documentation.html

LONGREADLEN
Use this directive to set the database handle's 'LongReadLen' attribute to a value that will be the larger than the expected size of the LOBs. The default is 1MB witch may not be enough to extract BLOBs or CLOBs. If the size of the LOB exceeds the 'LongReadLen' DBD::Oracle will return a 'ORA-24345: A Truncation' error. Default: 1023*1024 bytes.

Take a look at this page to learn more: http://search.cpan.org/~pythian/DBD-Oracle-1.22/Oracle.pm#Data_Interface_for_Persistent_LOBs

Important note: If you increase the value of this directive take care that DATA_LIMIT will probably needs to be reduced. Even if you only have a 1MB blob, trying to read 10000 of them (the default DATA_LIMIT) all at once will require 10GB of memory. You may extract data from those table separately and set a DATA_LIMIT to 500 or lower, otherwise you may experience some out of memory.

LONGTRUNKOK
If you want to bypass the 'ORA-24345: A Truncation' error, set this directive to 1, it will truncate the data extracted to the LongReadLen value. Disable by default so that you will be warned if your LongReadLen value is not high enough.

USE_LOB_LOCATOR
Disable this if you want to load full content of BLOB and CLOB and not use LOB locators. In this case you will have to set LONGREADLEN to the right value. Note that this will not improve speed of BLOB export as most of the time is always consumed by the bytea escaping and in this case export is done line by line and not by chunk of DATA_LIMIT rows. For more information on how it works, see http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#Data_Interface_for_LOB_Locators

Default is enabled, it use LOB locators.
复制

结果

最终当然是解决啦，要不然我写这篇文章干啥尼