CODEWITHSUNDEEP
c#sqloracle-database
lilfighterr
lilfighterr

Reputation: 15

Retrieving the return value from Oracle with C#

I'd like to be able to retrieve the return value from a function in Oracle with c#. Currently, this is what I have:

For SQL:

 FUNCTION add_resource (
     project_view BOOLEAN,
     project_id NUMBER,
     worker_id  NUMBER,
     role_id    NUMBER) RETURN NUMBER IS
     resource_id NUMBER;
 BEGIN
   INSERT INTO RESOURCE_T (WORKERID, ROLEID, PROJECTID)
   VALUES (worker_id, role_id, project_id)
   RETURNING RESOURCEID INTO resource_id;
   RETURN resource_id;
 END add_resource;

For C#

cmdText = "DECLARE resource_id NUMBER; BEGIN resource_id := PKG_RAP.add_resource(" + projectView + "," + id + "," + record["WORKERID"] + "," + record["ROLEID"] + "); END;"; 

using (OracleCommand cmd = new OracleCommand(cmdText, conn))
    {
         OracleTransaction trans = conn.BeginTransaction();
         try
         {
          OracleParameter prm = new OracleParameter("resource_id", OracleDbType.Int32, ParameterDirection.ReturnValue);
          cmd.Parameters.Add(prm);                      
          cmd.ExecuteNonQuery();
          trans.Commit();
         }catch 
         {
          trans.Rollback();
          throw;
         }
    }

The query works individually when I test it out in the database, however, the c# is not able to grab the value for resource_id and only gets "null". Any tips would be appreciated!

Upvotes: 1

Views: 1604

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

The command text must be like this one if you like to call a function (and if you want or have to avoid select ... from dual:

cmdText = "BEGIN :resource_id := PKG_RAP.add_resource(" + projectView + "," + id + "," + record["WORKERID"] + "," + record["ROLEID"] + "); END;";

As John Pederson already answered, all parameters should be defined as bind values, e.i.

cmdText = "BEGIN :resource_id := PKG_RAP.add_resource(:projectView, :projectId, :workerId, :roleId); END;";

However, I don't think you can use boolean variables when calling from C#. You have to cast it, e.g. to "0" and "1"

Upvotes: 1

John Pederson
John Pederson

Reputation: 189

Does something like this not work?

cmdText = @"select PKG_RAP.add_resource(:projectView, :projectId, :workerId, :roleId) from dual";
using (var cmd = new OracleCommand(cmdText, conn))
using (cmd.Parameters.Add(":projectView", projectView))
using (cmd.Parameters.Add(":projectId", id))
using (cmd.Parameters.Add(":workerId", record["WORKERID"]))
using (cmd.Parameters.Add(":roleId", record["ROLEID"]))
using (var tx = conn.BeginTransaction()) {
    try {
        // resource ID
        var resourceId = Convert.ToInt32(cmd.ExecuteScalar());
        tx.Commit();
        return resourceId;
    } catch {
        tx.Rollback();
        throw;
    }
}

This seems (to me) like the simplest solution to calling a PL-SQL function to return a single value.

ETA: I don't have sufficient rep to comment, yet, but Marc, above, means that you don't return anything in the PL-SQL block you are executing.

Upvotes: 2

Related Questions