Executing Oracle function and getting back a return value

Question

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

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

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;

Answer 1

Make sure that you have working SQL statement by running function execution in oracle client. Than potentionally modify your code. If even that isnt working than just run some databaze profiler (e.g. mssql have database profiler) to see what sql statement is your application actually sending to your oracle server.

Answer 2

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