Trying to call oracle function from dotnet getting error while returning value

Question

I'm trying to call a oracle function from asp.net mvc side and returning a value but getting error "{"ORA-06502: PL/SQL: numeric or value error: character string buffer too small\nORA-06512: at line 1"}.

In oracle side i have written a function which return concatenated value and then in dotnet side i am retrieving that value.

Oracle function

CREATE OR REPLACE FUNCTION func_multi_val(PROD_DATE in varchar2)
 RETURN varchar2  
 is 
  var_value varchar2(200);
  x varchar2     (200);
  cursor age_val is 
 SELECT AGE_CD,
 decode(AGE_CD,'04','CL LINE(R','05','CR ','A',NULL,STAGE_DESC) STAGE_DESC ,
 STAGE_SEQ      
 FROM DBPROD.PROD_AGE_MST
 WHERE STAGE_SEQ < 15
 AND STAGE_CD NOT IN ('ZA','05')
 ORDER BY 3;  
 BEGIN
    FOR i IN age_val LOOP
      SELECT To_Char(Round(NVL(SUM(NVL(WF_PROD_WT, 0)), 0), 0)) 
      INTO X
      FROM DBPROD.Prod_age_wp_gs_cur
     WHERE WF_DATE BETWEEN trunc(to_date(PROD_DATE,'dd-MM-YYYY')) AND  trunc(to_date(OD_DATE,'dd-MM-YYYY')+1)
       AND WF_STAGE_CD=i.AGE_CD;
    var_value := var_value || X||'~';
   END LOOP;
return var_value;
END;

Asp.net MVC

try
   {
    conn.Open();
    OracleCommand command = new OracleCommand();
    command.Connection = conn;
    command.CommandText = "func_multi_val";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("PROD_DATE", OracleDbType.Varchar2).Value = DateTime.Now.ToString("dd-MM-yyyy");
    command.Parameters.Add("var_value", OracleDbType.Varchar2).Direction = ParameterDirection.ReturnValue;
    command.ExecuteScalar();
    object o = command.ExecuteScalar();

     }  
     catch(Exception ex)
     {
     }

Expected:Concatenated value should be return from oracle side retrieve at dotnet. Actual:Getting error at dotnet side while binding "{"ORA-06502: PL/SQL: numeric or value error: character string buffer too small\nORA-06512: at line 1"}".Any idea would appreciated

Answer 1

VAR_VALUE contains concatenated result. If it (the result) is longer than 200 characters, it can't fit into VARCHAR2(200) variable, as VAR_VALUE is declared as such.

Therefore, enlarge it.

Answer 2

Alternate way for answer i just Remove parameter from oracle query and use sysdate function instead of using PROD_DATE variable

CREATE OR REPLACE FUNCTION func_multi_val
 RETURN varchar2  
 is 
  var_value varchar2(200);
  x varchar2     (200);
  cursor age_val is 
 SELECT AGE_CD,
 decode(AGE_CD,'04','CL LINE(R','05','CR ','A',NULL,STAGE_DESC) STAGE_DESC ,
 STAGE_SEQ      
 FROM DBPROD.PROD_AGE_MST
 WHERE STAGE_SEQ < 15
 AND STAGE_CD NOT IN ('ZA','05')
 ORDER BY 3;  
 BEGIN
    FOR i IN age_val LOOP
      SELECT To_Char(Round(NVL(SUM(NVL(WF_PROD_WT, 0)), 0), 0)) 
      INTO X
      FROM DBPROD.Prod_age_wp_gs_cur
     WHERE PSWF_DATE BETWEEN sysdate AND  sysdate+1
       AND WF_STAGE_CD=i.AGE_CD;
    var_value := var_value || X||'~';
   END LOOP;
return var_value;
END;

and in dotnet side i do the following modification

try
   {
    conn.Open();
    OracleCommand command = new OracleCommand();
    command.Connection = conn;
    command.CommandText = "func_multi_val";
    command.CommandType = CommandType.StoredProcedure;    
   command.Parameters.Add("var_value", OracleDbType.Varchar2,200).Direction = ParameterDirection.ReturnValue;
    command.ExecuteScalar();
    object o = command.Parameters["var_value"].Value;

     }  
     catch(Exception ex)
     {
     }