Using Oracle.ManagedDataAccess.Client to return a value from a function to c#

1
votes

I have the following Function in Oracle:

FUNCTION return_test (p_id varchar2) RETURN VARCHAR2
is
   p_return_val VARCHAR2 (500 CHAR) := 'test';
begin  
   return p_return_val;
end;

I'm trying to get the value into my c# code as follows:

var dbCommand = new OracleCommand();
string retVal;
string Function_query = "PKG_TEST.return_test";
dbConnection = new OracleConnection(ConnString);
dbConnection.Open();


OracleParameterCollection dbParams = dbCommand.Parameters;
dbParams.Add("p_return_val", OracleDbType.Varchar2, System.Data.ParameterDirection.ReturnValue);
dbParams.Add("p_id", OracleDbType.Varchar2, "123", System.Data.ParameterDirection.Input); 

OracleCommand cmd;
OracleParameter dbAddParam;
cmd = new OracleCommand(Function_query , dbConnection); 
cmd.CommandType = CommandType.StoredProcedure;
foreach (OracleParameter dbParam in dbParams)
{
    dbAddParam = (OracleParameter)dbParam.Clone(); // clones all settings
    cmd.Parameters.Add(dbAddParam);
}
retVal = cmd.ExecuteScalar().tostring();

The Error while debugging is:

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

2
c#oracle
stackoverflow.com/questions/13662497/… – Mottor

2 Answers

1
votes

I never worked with OracleParameterCollection. Usually I add Parameters to OracleCommand one by one.

Anyway, try this one:

OracleParameterCollection dbParams = dbCommand.Parameters;

dbParams.Add("p_return_val", OracleDbType.Varchar2, 500, null, ParameterDirection.ReturnValue);
dbParams.Parameters["p_return_val"].DbType = DbType.String; // Maybe this line is not required at Oracle.ManagedDataAccess. However, you must use it for Oracle.DataAccess

dbParams.Add("p_id", OracleDbType.Varchar2, "123", ParameterDirection.Input); 

string Function_query = "BEGIN :p_return_val := PKG_TEST.return_test(:p_id); END;";
cmd = new OracleCommand(Function_query , dbConnection);     
cmd.CommandType = CommandType.Text
cmd.Parameters.Add(dbParams);

cmd.ExecuteNonQuery();
retval = cmd.Parameters["p_return_val"].Value;

You cannot use method ExecuteScalar() on a function call, it is used only for result sets, i.e. SELECT ... statements. See Data Provider for .NET Developer's Guide

0
votes

Thank you Wernfried for your contribution. I will try it out now. In the mean time we found this to work:

string query = "select PKG_TEST.return_test(:x) from dual";
dbParams.Add("x", OracleDbType.Varchar2, QuoteID, System.Data.ParameterDirection.Input);
var result = (string)dbManager.ExecuteScalar(query, dbParams, CommandType.Text);

To me, it seems like its inline SQL which I would always try and avoid, but it worked. Is there any reason I wouldn't use this?