Calling a database stored function or stored proc that returns a value

Question

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?

score 4 · Accepted Answer · 2019-04-01 14:03:36Z

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;
    }

Collectives™ on Stack Overflow

Calling a database stored function or stored proc that returns a value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related