You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Oracle Apex RTF模板展示多Blob图片遇ORA错误求助

avatar
DevOps 小满
2026-6-24
问题：Oracle Apex RTF模板展示Blob图片时的ORA错误解决

问题背景

需在Oracle Apex的RTF模板中展示DETALLES_DE_OFERTA表内3个Blob字段的图片，通过自定义PDF2IMG函数将Blob转换为Base64编码的CLOB，结合LISTAGG聚合后在RTF模板中配置展示标签，但执行时先后出现以下错误：

  1. ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  2. ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (current: 125072, maximum: 32767)

已确认所有图片大小均不超过35KB。

相关代码

初始查询语句

SELECT
    LISTAGG(CAST(OBJETIVO_DEL_PROYECTO AS VARCHAR2(3999)), CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY CAST(OBJETIVO_DEL_PROYECTO AS VARCHAR2(3999))) AS OBJETIVO_DEL_PROYECTO,
    LISTAGG(CAST(ALCANCE AS VARCHAR2(3999)), CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY CAST(ALCANCE AS VARCHAR2(3999))) AS ALCANCE,
    LISTAGG(CAST(METODOLOGIA AS VARCHAR2(3999)), CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY CAST(METODOLOGIA AS VARCHAR2(3999))) AS METODOLOGIA,
    LISTAGG(INTEGRANTES_ROLES, CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY INTEGRANTES_ROLES) AS INTEGRANTES,
    LISTAGG(CAST(CONTEXTO AS VARCHAR2(3999)), CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY CAST(CONTEXTO AS VARCHAR2(3999))) AS DETALLE_CONTEXTO,
    LISTAGG(PDF2IMG(LINEA_DE_TIEMPO_IMAGEN)) WITHIN GROUP (ORDER BY LINEA_DE_TIEMPO_IMAGEN) AS IMAGE,
    LISTAGG(PDF2IMG(METODOLOGIA_IMG)) WITHIN GROUP (ORDER BY METODOLOGIA_IMG) AS IMAGE2,
    LISTAGG(PDF2IMG(ALCANCE_IMG)) WITHIN GROUP (ORDER BY ALCANCE_IMG) AS IMAGE3,
    LISTAGG(LINEA_DE_TIEMPO_TEXTO) WITHIN GROUP (ORDER BY LINEA_DE_TIEMPO_TEXTO) AS TIMELINE_TEXTO,
    ID_AUX
FROM DETALLES_DE_OFERTA WHERE ID_AUX = 5 GROUP BY ID_AUX

PDF2IMG转换函数

create or replace function "PDF2IMG"(p_blob IN BLOB)
RETURN CLOB
IS
l_clob CLOB;
l_step PLS_INTEGER := 12000; -- 需设置为3的倍数，且不超过24573
BEGIN
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_blob) - 1 )/l_step) LOOP
l_clob := l_clob || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_blob,l_step, i * l_step + 1)));
END LOOP;
RETURN l_clob;
END;
/

RTF模板图片配置

<fo:instream-foreign-object content-type="image/jpg" height="3 in" width="4 in">
<xsl:value-of select="IMAGE"/>
</fo:instream-foreign-object>

调整后的查询语句

SELECT
    LISTAGG(CAST(OBJETIVO_DEL_PROYECTO AS VARCHAR2(3999))) WITHIN GROUP (ORDER BY CAST(OBJETIVO_DEL_PROYECTO AS VARCHAR2(3999))) AS OBJETIVO_DEL_PROYECTO,
    LISTAGG(CAST(ALCANCE AS VARCHAR2(3999))) WITHIN GROUP (ORDER BY CAST(ALCANCE AS VARCHAR2(3999))) AS ALCANCE,
    LISTAGG(CAST(METODOLOGIA AS VARCHAR2(3999))) WITHIN GROUP (ORDER BY CAST(METODOLOGIA AS VARCHAR2(3999))) AS METODOLOGIA,
    LISTAGG(INTEGRANTES_ROLES, CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY INTEGRANTES_ROLES) AS INTEGRANTES,
    LISTAGG(CAST(CONTEXTO AS VARCHAR2(3999)), CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY CAST(CONTEXTO AS VARCHAR2(3999))) AS DETALLE_CONTEXTO,
    LISTAGG(PDF2IMG(LINEA_DE_TIEMPO_IMAGEN)) WITHIN GROUP (ORDER BY LINEA_DE_TIEMPO_IMAGEN) AS IMAGE,
    LISTAGG(PDF2IMG(ALCANCE_IMG)) WITHIN GROUP (ORDER BY ALCANCE_IMG) AS IMAGE_ALCANCE,
    LISTAGG(LINEA_DE_TIEMPO_TEXTO) WITHIN GROUP (ORDER BY LINEA_DE_TIEMPO_TEXTO) AS TIMELINE_TEXTO,
    ID_AUX
FROM DETALLES_DE_OFERTA
WHERE ID_AUX = 5 
GROUP BY ID_AUX

错误原因

  1. ORA-06502：LISTAGG函数默认返回VARCHAR2类型，最大长度为32767字节。35KB的Blob转换为Base64编码后体积会增加约1/3（约47KB），远超VARCHAR2上限，即使PDF2IMG返回CLOB，LISTAGG仍会隐式将其转换为VARCHAR2导致溢出。
  2. ORA-22835：本质与上一错误相同，LISTAGG处理CLOB时强制转换为VARCHAR2，超过32767字节的限制触发错误。

解决方案

方案一：移除图片字段的LISTAGG聚合（优先推荐）

如果每个ID_AUX对应的图片字段仅存在一条记录，无需使用LISTAGG聚合，直接返回PDF2IMG的CLOB结果即可：

SELECT
    LISTAGG(CAST(OBJETIVO_DEL_PROYECTO AS VARCHAR2(3999)), CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY CAST(OBJETIVO_DEL_PROYECTO AS VARCHAR2(3999))) AS OBJETIVO_DEL_PROYECTO,
    LISTAGG(CAST(ALCANCE AS VARCHAR2(3999)), CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY CAST(ALCANCE AS VARCHAR2(3999))) AS ALCANCE,
    LISTAGG(CAST(METODOLOGIA AS VARCHAR2(3999)), CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY CAST(METODOLOGIA AS VARCHAR2(3999))) AS METODOLOGIA,
    LISTAGG(INTEGRANTES_ROLES, CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY INTEGRANTES_ROLES) AS INTEGRANTES,
    LISTAGG(CAST(CONTEXTO AS VARCHAR2(3999)), CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY CAST(CONTEXTO AS VARCHAR2(3999))) AS DETALLE_CONTEXTO,
    PDF2IMG(LINEA_DE_TIEMPO_IMAGEN) AS IMAGE,
    PDF2IMG(METODOLOGIA_IMG) AS IMAGE2,
    PDF2IMG(ALCANCE_IMG) AS IMAGE3,
    LISTAGG(LINEA_DE_TIEMPO_TEXTO) WITHIN GROUP (ORDER BY LINEA_DE_TIEMPO_TEXTO) AS TIMELINE_TEXTO,
    ID_AUX
FROM DETALLES_DE_OFERTA 
WHERE ID_AUX = 5 
GROUP BY ID_AUX, LINEA_DE_TIEMPO_IMAGEN, METODOLOGIA_IMG, ALCANCE_IMG;

说明：GROUP BY子句中添加图片字段，确保每个分组仅对应一条图片记录，直接返回CLOB类型的Base64编码，避免隐式转换。

方案二：自定义支持CLOB的聚合函数

若确实需要聚合多条图片记录，可创建自定义聚合函数处理CLOB，避免LISTAGG的隐式转换限制：

1. 创建聚合类型及函数

CREATE OR REPLACE TYPE clob_agg_type AS OBJECT
(
    total_clob CLOB,
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT clob_agg_type) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT clob_agg_type, value IN CLOB) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateTerminate(self IN clob_agg_type, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT clob_agg_type, ctx2 IN clob_agg_type) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY clob_agg_type IS
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT clob_agg_type) RETURN NUMBER IS
    BEGIN
        sctx := clob_agg_type(NULL);
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT clob_agg_type, value IN CLOB) RETURN NUMBER IS
    BEGIN
        IF value IS NOT NULL THEN
            IF self.total_clob IS NULL THEN
                DBMS_LOB.CREATETEMPORARY(self.total_clob, TRUE);
            END IF;
            DBMS_LOB.APPEND(self.total_clob, value);
            -- 如需添加分隔符，取消下方注释并修改为所需分隔符
            -- DBMS_LOB.APPEND(self.total_clob, CHR(10)||CHR(9)||CHR(13));
        END IF;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(self IN clob_agg_type, returnValue OUT CLOB, flags IN NUMBER) RETURN NUMBER IS
    BEGIN
        returnValue := self.total_clob;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT clob_agg_type, ctx2 IN clob_agg_type) RETURN NUMBER IS
    BEGIN
        IF ctx2.total_clob IS NOT NULL THEN
            IF self.total_clob IS NULL THEN
                DBMS_LOB.CREATETEMPORARY(self.total_clob, TRUE);
            END IF;
            DBMS_LOB.APPEND(self.total_clob, ctx2.total_clob);
        END IF;
        RETURN ODCIConst.Success;
    END;
END;
/

CREATE OR REPLACE FUNCTION clob_listagg(p_clob CLOB) RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING clob_agg_type;
/

2. 修改查询语句使用自定义聚合函数

SELECT
    LISTAGG(CAST(OBJETIVO_DEL_PROYECTO AS VARCHAR2(3999)), CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY CAST(OBJETIVO_DEL_PROYECTO AS VARCHAR2(3999))) AS OBJETIVO_DEL_PROYECTO,
    LISTAGG(CAST(ALCANCE AS VARCHAR2(3999)), CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY CAST(ALCANCE AS VARCHAR2(3999))) AS ALCANCE,
    LISTAGG(CAST(METODOLOGIA AS VARCHAR2(3999)), CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY CAST(METODOLOGIA AS VARCHAR2(3999))) AS METODOLOGIA,
    LISTAGG(INTEGRANTES_ROLES, CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY INTEGRANTES_ROLES) AS INTEGRANTES,
    LISTAGG(CAST(CONTEXTO AS VARCHAR2(3999)), CHR(10) || CHR(9) || CHR(13)) WITHIN GROUP (ORDER BY CAST(CONTEXTO AS VARCHAR2(3999))) AS DETALLE_CONTEXTO,
    clob_listagg(PDF2IMG(LINEA_DE_TIEMPO_IMAGEN)) WITHIN GROUP (ORDER BY LINEA_DE_TIEMPO_IMAGEN) AS IMAGE,
    clob_listagg(PDF2IMG(METODOLOGIA_IMG)) WITHIN GROUP (ORDER BY METODOLOGIA_IMG) AS IMAGE2,
    clob_listagg(PDF2IMG(ALCANCE_IMG)) WITHIN GROUP (ORDER BY ALCANCE_IMG) AS IMAGE3,
    LISTAGG(LINEA_DE_TIEMPO_TEXTO) WITHIN GROUP (ORDER BY LINEA_DE_TIEMPO_TEXTO) AS TIMELINE_TEXTO,
    ID_AUX
FROM DETALLES_DE_OFERTA 
WHERE ID_AUX = 5 
GROUP BY ID_AUX;

注意事项

  • 确认ID_AUX分组下图片字段的记录数，优先使用方案一，性能更优。
  • PDF2IMG函数中l_step需设置为3的倍数，避免Base64编码出现截断错误。
  • RTF模板的content-type需与实际图片类型匹配（如image/png对应PNG图片）。

内容的提问来源于stack exchange，提问作者Juan Diego Céspedes Ortegon

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.06.24 18:55:54