Reputation: 742
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
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
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