CODEWITHSUNDEEP
c#oracle-databaseodac
CocLn
CocLn

Reputation: 742

How can i execute a Oracle Function in C#

I want to execute oracle function from c#. I use oracle data access OracleConnection and OracleCommand. My function is;

create or replace 
FUNCTION Fnc_Sistem(Tbl VarChar2) RETURN Number IS
Sistem    Number     := -1;
C         Integer;
S         Integer;

BEGIN
C := Dbms_sql.open_cursor;
Dbms_sql.parse(C, 'Select Sistem_Kodu From '||Tbl ||' Where Referans = 1', 0);
Dbms_sql.define_column(C, 1, Sistem);
S := dbms_sql.execute(C);
Loop
  If Dbms_sql.fetch_rows(C) > 0 then
       Dbms_sql.Column_value(C, 1, Sistem);
  End If;
  Exit;
End Loop;
Dbms_sql.close_cursor(C);
Return (Sistem);
Exception
      when others then
     If dbms_sql.is_open(C) then
        dbms_sql.close_cursor(C);
        Return (-1);
     end if;
     Return (-1);
     Raise;
End;

Upvotes: 1

Views: 4426

Answers (2)

Duomi8
Duomi8

Reputation: 160

Oracle functions can be called in a similar manner to Stored Procedures:

cmd = new OracleCommand("Fnc_Sistem", connection);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("prm_Result", OracleDbType.Varchar2, ParameterDirection.ReturnValue);
cmd.Parameters.Add("prm_Argument", OracleDbType.Varchar2).Value = value;

command.ExecuteNonQuery();

The return parameter should be created first, its name is not important regardless of whether BindByName is true or false.

Upvotes: 2

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186843

You can use Oracle anonymous block to call your function:

public static int FunctionCall(String value, String myConnectionString) {
  using (var conn = new OracleConnection(myConnectionString)) {
    conn.Open();

    using (var command = conn.CreateCommand()) {
      command.CommandText =
        @"begin
            :prm_Result := Fnc_Sistem(:prm_Argument);
          end;";

      command.Parameters.Add(":prm_Result", OracleDbType.Varchar2, ParameterDirection.Output);
      command.Parameters.Add(":prm_Argument", OracleDbType.Varchar2).Value = value;

      command.ExecuteNonQuery();

      return int.Parse(command.Parameters[0].Value.ToString());
    }
  } 
}

....

int myValue = FunctionCall("myValue", myConnectionString);

Upvotes: 1

Related Questions