Calling an Oracle function from .NET

At some point in every man’s life, he asks himself “How do I call an Oracle function from C# code?” Well, time to answer that question.

I have an Oracle package, and in the package header, I have declared my function:

FUNCTION ValidateId(pId IN VARCHAR) RETURN VARCHAR;

In the package body, I have my function implementation:

    FUNCTION ValidateId(pIdIN VARCHAR) RETURN VARCHAR IS
    
        pMessage VARCHAR(100);
        mId VARCHAR(10);
    
    BEGIN
        pMessage := 'OK';
        
        SELECT COUNT(*)
        INTO mId
        FROM MyTable
        WHERE id = pId;
        
        IF mId = 0 THEN
            pMessage := 'The ID supplied is not valid';
        END IF;

        RETURN pMessage;
    END;

It’s a simple function that verifies that the ID in question exists in the database, and returns a status based on that.

In my C# code, I use some fun and exciting ADO.NET code to call said function:

     public static string ValidateId(string id) {

         string message = String.Empty;

         using (OracleConnection conn = new OracleConnection(connString)) {
             using (OracleCommand comm = new OracleCommand("pplant.cool_package.ValidateId", conn)) {
                 comm.CommandType = CommandType.StoredProcedure;
                 comm.Parameters.Add(new OracleParameter() {
                   Direction = ParameterDirection.ReturnValue, OracleDbType = OracleDbType.Varchar2,
                     Size = 150
                 });
                 comm.Parameters.Add(":pId", OracleDbType.Varchar2, 10).Value = id;

                 conn.Open();
                 comm.ExecuteNonQuery();
                 message = comm.Parameters[0].Value.ToString();
             }
         }

         return message;
     }

A couple of things to note about returning a value from the function. The return value is declared as the first parameter, and has a direction of “ReturnValue”. Also notice that the return parameter does not have a name.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: