Oracle pipelined function: ORA-06502 numeric or value error

Question

I wrote a pipelined function to query data from a remote database. I keep getting

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

I think I do understand when this error would occur, for example when a table column is defined as VARCHAR2(10) and you try to insert something bigger than 10 byte. But in this case, I really don't see whats wrong.

Perhaps first I show the parameters of the local and the remote database. I think it might be important, that on both DBs NLS_LENGTH_SEMANTICS is set to BYTE.

Local DB (where the pipelined function is stored): params local db

Remote DB (from where data is queried): params remote db

Now the code in local db:

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;

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

And this is the table specifications on remote db:

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)
)

So you can see, when I created the type in local db I choosed the exact sizes of the columns in remote db. But when I execute/query the pipelined function, I get this error (sorry it's german, but I wrote english in title): error message

How can this happen? Do you have any idea what's wrong? Appreciate any help, thanks!

EDIT 2021-03-02: @ShaunPeterson Thanks for reply, NLS_CHARACTERSET is set to AL32UTF8 on both DBs, local and remote.

I just found out, the problem seems to be caused by 2 different IDEs that are used in our company. I am using PL/SQL Developer from allround automations. When I posted the table specifications on remote DB (table "PALETTE"), I connected to remote DB using that IDE, and it showed the column types/sizes as you can see in my original post. I repeat the first 6 columns:

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,

But when Oracle SQL Developer is used, then it looks like this:

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),

So it seems PL/SQL Developer just shows wrong specifications. I have no idea why it is like that, and I would like to know, but that's another question. I solved my problem using the column types and sizes Oracle SQL Developer shows.

Thank you.

consider using table markdown or plain text for those DB info ( just in case the picture link dead, at least include some maybe important info with plain text ), also rewrite the error message in English and plain text, don't use picture or other language. — T. Peter
@T.Peter thanks for your comment, but I wrote everything important in plain text and in english. The pictures are additional information, I think readers can understand everything without them. The 2 pics about DB parameter info show that NLS_LENGTH_SEMANTICS is set to BYTE, what I also wrote in plain text. The pic of the error message is in german, because I am german and my environment is set to german. I wrote the error message in plain text 2 times, in title and at start of my post: "I keep getting ORA-06502: PL/SQL: numeric or value error: character string buffer too small". Thanks. — Dietz
Bit of a stab in the dark here as nothing jumps out but.... since your NLS_LENGTH_SEMANTICS are set to byte then it could be a character set issue. If one database has a 8 bit character set and one has a 16 bit character set then you could run into this issue. To see the character set use... select * from nls_database_parameters where parameter='NLS_CHARACTERSET'; — Shaun Peterson
@ShaunPeterson Thanks for reply, pls have a look at my edited post above. — Dietz

Michael Milligan Michael Milligan · Accepted Answer · 2021-03-02T16:00:17

@Dietz, what you said was fine for me.

I'm having the same problem as you, I am wondering if it's a bug. Like you:

I created types in ADT exactly as in their respective table columns.
I set my NLS_LENGTH_SEMANTICS.

However:

I set a default in SQL Developer.
I am not accessing a remote DB.
I don't believe it's related to either of these.

FYI, I'm going to try one more thing and if it doesn't work, I'll open a ticket with Oracle Support. I will update here with what I find or workaround from Oracle Support.

Oracle pipelined function: ORA-06502 numeric or value error

1 Answers