out varchar2 in package.procedure is giving numeric or value error

Question

I have a procedure in a package. there is an out variable p_return_msg VARCHAR2.

right after the BEGIN I have p_return_msg := 'Validation successful.'

this causes ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "ZWR5KFR.PRICING_AUDIT_PKG", line 187 ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "ZWR5KFR.PRICING_AUDIT_PKG", line 134 ORA-06512: at line 1

line 134 is that line and 187 is in the exception section where I'm trying to assign something to it again. p_return_msg := 'Validation failed: '

if I change the line to p_return_msg := ''; that works until at any point I try to assign anything to it then I get the error again.

I've tried using a temp var and just assign that to it, but same error. Nothing I've tried or could find online seems to help.

EDIT: I think I figured it out. The person calling my procedure doesn't have the variable in C# set up properly to accept a value. They have not confirmed the code I sent them to get the variable properly.

var pReturnMsg = new OracleParameter("p_return_msg", OracleDbType.Varchar2, 4000) { Direction = ParameterDirection.Output };

Answer 1

It is not the package to blame, but variable that accepts procedure's OUT parameter's value.

Sample procedure:

SQL> create or replace procedure p_test (p_return_msg out varchar2) is
  2  begin
  3    p_return_msg := 'Validation successful.';
  4  end;
  5  /

Procedure created.

See l_msg's declaration; 5 characters is just too short!

SQL> set serveroutput on
SQL> declare
  2    l_msg varchar2(5);   --> too short!
  3  begin
  4    p_test(l_msg);
  5    dbms_output.put_line(l_msg);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.P_TEST", line 3
ORA-06512: at line 4

But, if we enlarge it, everything is OK:

SQL> declare
  2    l_msg varchar2(200);   --> long enough
  3  begin
  4    p_test(l_msg);
  5    dbms_output.put_line(l_msg);
  6  end;
  7  /
Validation successful.

PL/SQL procedure successfully completed.

SQL>