执行Oracle函数并获取返回值

本文介绍了执行Oracle函数并获取返回值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用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; }