How to call a function in Oracle in .NET, not a stored procedure

Ever think it would be oh so simple to call a function on an Oracle Database? So did I. I am normally writing SQL code for Microsoft SQL Server, but we also integrate with other systems who run Oracle.

I needed to call a function on the Oracle database to retrieve certain data. So I plugged it in thinking I could call it with a simple select. Wrong.

It is still simple, just had a little extra to the select to make it work.

OracleCommand command = provider.Connection.CreateCommand();
command.CommandType = System.Data.CommandType.Text;

//set sp name
command.CommandText = string.Format("Select PKG_NAME.VALIDATE({0}) from dual", id) ;

int returnValue = (int)(decimal)command.ExecuteScalar();
return returnValue ==1;

Hopefully this helps you out. Pretty simple to do, just a matter of knowing how to do it.

Happy Coding!

Leave a Reply

Your email address will not be published. Required fields are marked *