Oracle管道函数查询远程库触发ORA-06502错误的排查过程

触发的报错 #

调用部署在本地库、用于拉取远程库数据的管道函数时，持续抛出以下错误：

ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

这类报错的常见触发逻辑非常明确：比如表字段定义为VARCHAR2(10)时，尝试写入超过10字节长度的内容就会触发该异常，但本次场景下按照常规排查逻辑始终找不到问题根源。

初期排查过程 #

• 首先核对本地与远程数据库的参数配置：两侧库的NLS_LENGTH_SEMANTICS均设置为BYTE——其中本地库是存储管道函数的数据库，远程库是提供源业务数据的数据库。
• 逐一核对代码与关联类型定义：
  1. 本地部署的管道函数逻辑为通过DBLink拉取远程PALETTE表全量数据，逐行构造对象后管道输出：

  create or replace function f_get_pl_data return tb_pl_palette pipelined is
  begin
      for i in (select p.*, 123 LAGER from palette@myremotedb p)
      loop
        pipe row(tt_pl_palette(i.pid,i.bereich,i.regal,i.fach,i.ebene,i.vol_klasse,i.lhm_typ,i.zustand,i.neu_datum,
                 i.neu_zeit,i.neu_usr,i.aender_datum,i.aender_zeit,i.aender_usr,i.verl_datum,i.tournr,
                 i.fil_nr,i.retournr,i.fz_nr,i.fahrer_nr,i.eroeff_auswahl,i.tpa_knz,i.lfsaender_knz,
                 i.verladen_am,i.verladen_um,i.verladen_von,i.verladen_von2,i.leer_gew,i.soll_gew,
                 i.ist_gew,i.lager));
      end loop;
      
      return;
  end;
  

  2. 本地关联的自定义对象类型、嵌套表类型定义：

  CREATE OR REPLACE TYPE "TT_PL_PALETTE" AS OBJECT (
    pid            VARCHAR2(14),
    bereich        VARCHAR2(2),
    regal          VARCHAR2(2),
    fach           VARCHAR2(3),
    ebene          VARCHAR2(2),
    vol_klasse     INTEGER,
    lhm_typ        INTEGER,
    zustand        INTEGER,
    neu_datum      DATE,
    neu_zeit       VARCHAR2(11),
    neu_usr        VARCHAR2(4),
    aender_datum   DATE,
    aender_zeit    VARCHAR2(11),
    aender_usr     VARCHAR2(4),
    verl_datum     DATE,
    tournr         VARCHAR2(6),
    fil_nr         VARCHAR2(4),
    retournr       VARCHAR2(10),
    fz_nr          INTEGER,
    fahrer_nr      INTEGER,
    eroeff_auswahl INTEGER,
    tpa_knz        VARCHAR2(1),
    lfsaender_knz  VARCHAR2(1),
    verladen_am    DATE,
    verladen_um    VARCHAR2(11),
    verladen_von   VARCHAR2(4),
    verladen_von2  VARCHAR2(4),
    leer_gew       NUMBER(7,3),
    soll_gew       NUMBER(7,3),
    ist_gew        NUMBER(7,3),
    lager          NUMBER
    )
  

  CREATE OR REPLACE TYPE "TB_PL_PALETTE"  as TABLE OF TT_PL_PALETTE
  

  3. 最初通过PL/SQL Developer看到的远程PALETTE表建表规范：

  create table PALETTE
  (
    pid            VARCHAR2(14) not null,
    bereich        VARCHAR2(2) not null,
    regal          VARCHAR2(2) not null,
    fach           VARCHAR2(3) not null,
    ebene          VARCHAR2(2) not null,
    vol_klasse     INTEGER,
    lhm_typ        INTEGER,
    zustand        INTEGER,
    neu_datum      DATE default trunc(sysdate),
    neu_zeit       VARCHAR2(11) default to_char(sysdate, 'HH24:MI:SS'),
    neu_usr        VARCHAR2(4),
    aender_datum   DATE,
    aender_zeit    VARCHAR2(11),
    aender_usr     VARCHAR2(4),
    verl_datum     DATE,
    tournr         VARCHAR2(6),
    fil_nr         VARCHAR2(4),
    retournr       VARCHAR2(10),
    fz_nr          INTEGER,
    fahrer_nr      INTEGER,
    eroeff_auswahl INTEGER,
    tpa_knz        VARCHAR2(1) default '0',
    lfsaender_knz  VARCHAR2(1) default 'N',
    verladen_am    DATE,
    verladen_um    VARCHAR2(11),
    verladen_von   VARCHAR2(4),
    verladen_von2  VARCHAR2(4),
    leer_gew       NUMBER(7,3),
    soll_gew       NUMBER(7,3),
    ist_gew        NUMBER(7,3)
  )
  

按照上述展示的表结构对齐本地类型定义后，管道函数仍持续抛出报错。后续补充排查确认：本地与远程数据库的NLS_CHARACTERSET均为AL32UTF8。

问题根因 #

最终定位到异常来源是两款数据库IDE的字段长度语义展示差异：

• 数据库层面配置的NLS_LENGTH_SEMANTICS是建表时未显式指定长度语义的全局默认规则，不会覆盖单字段创建时显式标注的长度语义。
• 使用Allround Automations出品的PL/SQL Developer连接远程库时，对PALETTE表字段的长度语义展示存在缺陷：所有字段创建时显式指定的CHAR计量规则都被省略不展示，仅显示字段数值长度，让查看者误以为字段使用全局默认的BYTE计量规则。
• 切换为Oracle SQL Developer查看同一张表时，能看到字段实际定义为显式的字符语义计量：

  CREATE TABLE "PSTEDI"."PALETTE" 
     (    "PID" VARCHAR2(14 CHAR) NOT NULL ENABLE, 
      "BEREICH" VARCHAR2(2 CHAR) NOT NULL ENABLE, 
      "REGAL" VARCHAR2(2 CHAR) NOT NULL ENABLE, 
      "FACH" VARCHAR2(3 CHAR) NOT NULL ENABLE, 
      "EBENE" VARCHAR2(2 CHAR) NOT NULL ENABLE, 
      "VOL_KLASSE" NUMBER(*,0), 
  )
  

  结合两侧库都使用AL32UTF8字符集的前提，单个中文字符会占用3字节存储，VARCHAR2(2 CHAR)定义的字段实际可存储最大长度为6字节。而本地自定义类型是按照PL/SQL Developer展示的信息，用默认的BYTE语义创建的VARCHAR2(2)，最大仅能容纳2字节内容，一旦远程拉取的数据中包含中文字符，就会因为长度超限触发ORA-06502。

按照Oracle SQL Developer展示的实际字段定义调整本地自定义类型的长度语义后，问题彻底解决。

内容的提问来源于stack exchange，提问作者Dietz