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

start with passing prod_date as a date from .NET DateTime, then check parameter binding order and varchar2 sizes. — Cee McSharpface
– Cee McSharpface, Commented Jul 24, 2019 at 9:29

Mohan · Accepted Answer · 2019-07-24 12:09:49Z

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

answered Jul 24, 2019 at 12:09

Mohan

2485 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A.K.M. Arifur Rahman Over a year ago

using data length "200" in dot net side at "OracleDbType.Varchar2,200" fixed my issue

Littlefoot · Accepted Answer · 2019-07-24 09:29:15Z

0

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.

answered Jul 24, 2019 at 9:29

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

8 Comments

Littlefoot Over a year ago

ORA-06502 is an error raised on Oracle side, so ... think & test it over.

Mohan Over a year ago

i changed the VAR_VALUE datatype to clob but now getting error {"ORA-22275: invalid LOB locator specified\nORA-06512: at line 1"}

Littlefoot Over a year ago

Why CLOB? Do you really expect something that large? Try with VARCHAR2(32767).

Mohan Over a year ago

still getting same error {"ORA-06502: PL/SQL: numeric or value error: character string buffer too small\nORA-06512: at line 1"}

Littlefoot Over a year ago

OK. Just to make sure that VAR_VALUE's size is a culprit: instead of var_value := var_value || X||'~';, put var_value := substr(var_value || X||'~', 1, 1000); which will shorten it to 1000 characters. Disregard the fact that it maybe/probably won't contain the full string length.

|

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related