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?