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 ?
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.