Styjun

Using linq2db with an oracle 12 database, how can I call a stored function or a stored procedure that returns a value? From the info I could find, the solution seemed to be to use:

IEnumerable<T> QueryProc<T>(
 this DataConnection connection, 
 string sql, 
 params DataParameter[] parameters);

But it doesn't work with functions and with a stored proc, I do not receive the output value from the proc.

create or replace PROCEDURE TESTPROC(inputParm VARCHAR2, outputParm OUT VARCHAR2)
IS
BEGIN 
 outputParm := 'TEST OUTPUT STRING'; 
END TESTPROC;

I call it with linq2db the following way:

DataParameter[] para = new DataParameter("inputParm", "some_string", DataType.VarChar), new DataParameter("outputParm", "", DataType.VarChar) ;

var res = myDataContext.QueryProc<string>("TESTPROC", para).FirstOrDefault();

What am I doing wrong?

Update: updated answer to be correct

IEnumerable<T> QueryProc<T>(...) return value is a data set, returned by procedure/function by select statement.

If your procedure doesn't return table, you need to use non-generic version of ExecuteProc, which just returns number of affected records.

To get output parameter value you need to access parameter in command:
((IDbDataParameter)dataConnection.Command.Parameters["parameter_name"]).Value

below is an example of procedure call helper from linq2db tests, generatedd by T4 template:

public static int OUTREFTEST(this DataConnection dataConnection, decimal? PID, out decimal? POUTPUTID, ref decimal? PINPUTOUTPUTID, string PSTR, out string POUTPUTSTR, ref string PINPUTOUTPUTSTR)
 
 var ret = dataConnection.ExecuteProc("TESTUSER.OUTREFTEST",
 new DataParameter("PID", PID, DataType.Decimal),
 new DataParameter("POUTPUTID", null, DataType.Decimal) Direction = ParameterDirection.Output, Size = 22 ,
 new DataParameter("PINPUTOUTPUTID", PINPUTOUTPUTID, DataType.Decimal) Direction = ParameterDirection.InputOutput, Size = 22 ,
 new DataParameter("PSTR", PSTR, DataType.NVarChar),
 new DataParameter("POUTPUTSTR", null, DataType.NVarChar) Direction = ParameterDirection.Output ,
 new DataParameter("PINPUTOUTPUTSTR", PINPUTOUTPUTSTR, DataType.NVarChar) Direction = ParameterDirection.InputOutput );

 POUTPUTID = Converter.ChangeTypeTo<decimal?>(((IDbDataParameter)dataConnection.Command.Parameters["POUTPUTID"]). Value);
 PINPUTOUTPUTID = Converter.ChangeTypeTo<decimal?>(((IDbDataParameter)dataConnection.Command.Parameters["PINPUTOUTPUTID"]). Value);
 POUTPUTSTR = Converter.ChangeTypeTo<string> (((IDbDataParameter)dataConnection.Command.Parameters["POUTPUTSTR"]). Value);
 PINPUTOUTPUTSTR = Converter.ChangeTypeTo<string> (((IDbDataParameter)dataConnection.Command.Parameters["PINPUTOUTPUTSTR"]).Value);

 return ret;