使用Oracle返回ORA-01406将海量表从oracle导入到PostgreSQL

Question

我从事一个项目，将数据从Oracle数据库传输到PostgreSQL数据库，用bash & SQL脚本创建数据仓库。为了访问Oracle数据库，我使用了PostgreSQL扩展oracle-fdw。

我的一个脚本从一个庞大的表中导入数据(大约每天10万个新行)。此表被分区，每个分区包含1天的数据。用于导入数据的查询如下所示：

INSERT INTO postgre_target_table (some_fields)
SELECT some_aggregated_fields -- (~150 fields)
FROM oracle_source_table
WHERE partition_id = :v_partition_id AND some_others_filters
GROUP BY primary_key;

在DEV服务器上，查询工作正常(此服务器上的数据要少得多)，但是在PREPROD中，它返回错误ORA-01406: fetched column value was truncated。

在一些帖子中，人们说输出字段可能太小，但是如果我试图发送一个简单的SELECT查询，而没有INSERT或GROUP BY，我也有相同的错误。

我在另一篇文章中发现的另一个想法是创建Oracle侧视图，但在我的查询中，我使用了视图中不能使用的多个参数。

我发现的最后一个想法是创建一个Oracle存储过程，它用聚合数据填充一个表，然后从这个表中导入数据，但是Oracle数据库是关键的，我的客户希望避免在其中添加更多的数据。

现在，我开始认为没有解决办法，而且这不是个好办法.

PostgreSQL版本: 12.4 / Oracle版本: 11.2

更新

看来我的问题比我想象的要复杂。

应用Laurenz提供的修改后，查询在PGAdmin上正确运行，但是当我使用psql命令时仍然会出现问题。此外，另一个查询似乎也有同样的问题。另一个查询不使用与第一个查询相同的源表，它使用4个连接表，没有任何分区。这些查询之间的共同点是结构。

我在最初的文章中省略的细节是，这两个查询的目的都是为了使表枢轴。他们看起来是这样的：

SELECT osr.id,
   MIN(CASE osr.category
      WHEN 123 THEN
         1
      END) AS field1,
   MIN(CASE osr.category
      WHEN 264 THEN
         1
      END) AS field2,
   MIN(CASE osr.category
      WHEN 975 THEN
         1
      END) AS field3,
...
FROM oracle_source_table osr
WHERE osr.category IN (123, 264, 975, ...)
GROUP BY osr.id;

现在我已经详细介绍了查询的样子，我可以给出第二个的一些结果，而不需要更改max_long的值(这个查询比第一个查询要轻)：

1. 有时它可以工作(~10%)，有时它在PGadmin上失败(~90%)，但是它从不使用psql命令。
2. 如果我删除WHERE，它总是工作的。

我不明白为什么删除WHERE会改变一些事情，这个子句中使用的字段是在0到2500之间的数字(6，0)，它仍然在SELECT子句中使用.哦，在此查询使用的4个Oracle表中，不存在长数据类型，只使用编号数据类型。

在我的20个查询中，只有这两个有问题，它们的结构相似，我不相信巧合。

Answer 1

别绝望！

将外部表上的max_long选项设置得足够大，使您的所有超大数据都适合。

文献资料有详细信息：

• max_long (可选，默认为"32767")

Oracle表中任何长、长原始列和XMLTYPE列的最大长度。可能的值是1到1073741823之间的整数(PostgreSQL中字节的最大大小)。这样的内存量至少会被分配两次，所以大的值会消耗大量的内存。

如果max_long小于检索到的最长值的长度，则将收到错误消息。

ORA-01406:获取的列值被截断

示例：

ALTER FOREIGN TABLE my_tab OPTIONS (ADD max_long '1000000');