Calling an oracle function from C#

Question

I have an Oracle function GetEmployeeDetails which saves all the employee details into a temporary table TempEmployeeDetails table.

I have to call the function followed by a select query on the temporary table. The function call succeeds but the select query throws the following error.

"BEGIN :Output := MyPackage.GetEmployeeDetails(" + ":EmployeeId,"); SELECT * FROM TempEmployeeDetails; END;"

The above query is giving me this error:

ORA-06550: line 1, column 98:

PLS-00428: an INTO clause is expected in this SELECT statement

Answer 1

I think you should separate the query from the function call, if you are using OracleClient so the code probably by:

OracleCommand cmd = new OracleCommand("GetEmployeeDetails", conn);
cmd.CommandType = CommandType.StoredProcedure;


par = new OracleParameter("EmployeeId", OracleType.int32);
par.Value = EmployeeId;
par.Direction = ParameterDirection.Input;
cmd.Parameters.Add(par);

cmd.ExecuteNonQuery();

now to get the data from tempEmployeeDetails make another query like the following:

OracleCommand cmd = new OracleCommand("SELECT * FROM TempEmployeeDetails", conn);
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
   //do what you want...
}