执行Oracle函数并获取返回值 [英] Executing Oracle function and getting back a return value

问题描述

我正在使用NPoco ORM，并尝试从Oracle函数获取结果.我尝试过:

I am using NPoco ORM and trying to get a result from Oracle function. I have tried:

    public string GetData(int param1, int param2)
    {
        var command = "PKG_SOMEPACKAGE.SET_DATA( @p_param1, @p_param2);";
        var sql = Sql.Builder.Append(command, new { p_param1 = 10, p_param2 = 20 });
        string result = this.Db.ExecuteScalar<string>(sql);

        return result;
    }

但是它不起作用并返回ORA-00900: invalid SQL statement

But it doesn't work and returns ORA-00900: invalid SQL statement

Oracle函数类似于:

The Oracle function looks something like:

function SET_DATA
( 
    p_param1 IN NUMBER,
    p_param2 IN NUMBER
) return varchar2 IS

BEGIN

    IF some condition is true THEN
      RETURN 'Y';         
    END IF;

    -- some logic

    RETURN 'N';
END;

推荐答案

我之前曾尝试过这种方法，但是，当我编写SQL时，我包含了一个包装版本，这就是导致我的异常的原因，包装sql.如果SQL的编写方式如下所示，则可以正常运行. 在此调用中，我将JSON数据发送到该函数并返回结果.

I had tried it before kind of like this, however, when I wrote the SQL, I included a wrapped version and that is what caused my exception, the wrapped sql. If the SQL is written like below, it works, perfectly. In this call, I'm sending JSON data to the function and getting the result back.

public string UpdateParticipant(ParticipantUpdate Participant)
{
    string ret = "";
    IsoDateTimeConverter dt = new IsoDateTimeConverter();
    dt.DateTimeFormat = "MM-dd-yyyy"; // we must have this format for our dates
    string json = JsonConvert.SerializeObject(Participant, dt);
    // Creating this output parameter is the key to getting the info back.
    var result = new OracleParameter
    {
        ParameterName = "RESULT",
        Direction = System.Data.ParameterDirection.InputOutput,
        Size = 100,
        OracleDbType = OracleDbType.Varchar2
    };
    // Now, setting the SQL like this using the result as the output parameter is what does the job.
    string sql = $@"DECLARE result varchar2(1000); BEGIN  @0 := WEBUSER.F_UpdateParticipant(@1); END;";
    var res = _db.db.Execute(sql, result, json);

    // Now return the value of the Output parameter!!

    ret = result.Value.ToString();
    return ret;
}

这篇关于执行Oracle函数并获取返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！