C# call oracle stored function

Question

create or replace function ar_knyga_egzistuoja(
id number
)
return number
is
kiekis number;
begin
select count(*) into kiekis from knygos where kn_id  = id;
return kiekis;
end;

C# code:

conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "ar_knyga_egzistuoja";

cmd.CommandType = CommandType.StoredProcedure;
OdbcParameter param = new OdbcParameter();

cmd.Parameters.Add("id", OracleType.Number).Value = id;

cmd.ExecuteNonQuery();
var kiekis = Convert.ToString(cmd.Parameters["kiekis"].Value);

MessageBox.Show(kiekis);

cmd.Parameters.RemoveAt(0);

conn.Close();

I getting error:

PLS-00221: 'AR_KNYGA_EGZISTUOJA' is not a procedure or is undefined
ORA-06550: line 1, column 7:

This is not procedure but function, but I know that function I can call like procedures, what is wrong ?

Answer 1

The ORA-06550 code you get means that the function was compiled with invalid statements and needs to be re-written. I'm not seeing anything obviously wrong with the code so you might have issues like permissions or incorrect table names and should check if you can run the function in a PL/SQL editor first and foremost. Then, after you get that running, try...

var cmd = new OracleCommand();

cmd.Connection = conn;
cmd.CommandText = "ar_knyga_egzistuoja";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("id", id);
cmd.Parameters.Add("kiekis", OracleType.Number);
cmd.Parmeters["kiekis"].Direction = ParameterDirection.ReturnValue;

cmd.Connection.Open();
cmd.ExecuteNonQuery();

var kiekis = Convert.ToString(cmd.Parameters["kiekis"].Value);

MessageBox.Show(kiekis);
cmd.Connection.Close();

This should be able to run the function like a stored procedure while expecting a return value named kiekis of type number to be available for work.