ORA-06502 error when calling a procedure from another procedure, works fine directly

Question

Background

I am debugging an issue with a legacy Oracle procedure that converts GHS info like this:

H315;H319;H317;H360Fd;H335;H373;H410;EUH211;P201;P260;P273;P280;P333+P313;P337+P313;PROF USE

Into the actual texts that match those codes, delimited by a comma and space, so like:

Causes skin irritation, Causes serious eye irritation, ...

It's available on a general purpose schema, let's say:

COMMON.GetGHSInformation()

It does all this by building an output text (is output variable) like this:

P_OUTPUTTEXT := trim(P_OUTPUTTEXT) || ', ' || trim(V_TEXT);

Where V_TEXT contains the next GHS text, so for instance Causes serious eye irritation.

What works

If the procedure COMMON.GetGHSInformation() is called directly, everything works as you would expect.

What doesn't work

The goal of this GetGHSInformation() method is to use it in other procedures, possibly even originating at a different schema.

I have another procedure, let's say

OtherSchema.DoSomethingGHSRelated()

that calls COMMON.GetGHSInformation().

While building the P_OUTPUTTEXT variable the logic throws a ORA-06502 PL/SQL error.

Declarations

The impacted variables are defined like this:

P_OUTPUTTEXT IN OUT VARCHAR2

V_TEXT     VARCHAR2(2000);

Question

The logic defined by COMMON.GetGHSInformation() works fine if called directly, how come it fails if called from another context?

Answer 1

If you have the minimal example:

DECLARE
  value VARCHAR2(10);
BEGIN
  value := '12345';
  value := value || '678901';
END;
/

Then the output is:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5

This is because we defined a string that could have at most 10 characters (2000 in your question) and we tried to set it to a value that has 11 characters.

---

If V_TEXT is defined as VARCHAR2(2000) then when you get to more than 2000 bytes of characters you will get an ORA-06502 error.

To solve it, either:

• Increase the size of V_TEXT (and make sure your strings will not exceed the new limit);

• Change V_TEXT to be a CLOB; or

• Limit the procedure to only put a maximum of 2000 characters into V_TEXT.

  IF LENGTH(P_OUTPUTTEXT) + 2 + LENGTH(trim(V_TEXT)) <= 2000 THEN
    P_OUTPUTTEXT := P_OUTPUTTEXT || ', ' || trim(V_TEXT);
  ELSE
    RETURN P_OUTPUTTEXT || ', ...';
  END IF;
  

fiddle