Passing a list of numbers into an Oracle procedure

I wanted to pass in a list of numbers to an Oracle procedure for use in an “IN” statement, called from C# code. First, the C# code:

  string list = "1,2,3";
  using (OracleCommand comm= new OracleCommand("MyProcedure", connection)) {
     comm.CommandType = CommandType.StoredProcedure;
     comm.Parameters.Add(":pList", OracleDbType.Varchar2, 50).Value = list;
     comm.Parameters.Add(":p_cursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

My Oracle procedure was equally awesome:

    PROCEDURE MyProcedure(pList IN VARCHAR2, p_cursor OUT sys_refcursor) IS
        
    BEGIN
        OPEN p_cursor FOR
            SELECT name 
            FROM coasters
            WHERE id IN (SELECT TO_NUMBER(column_value) AS IDs FROM XMLTABLE(pList));
    END;

You can see the “FROM XMLTABLE” statement is doing all the good stuff of splitting the list into the appropriate format. Good times.

Empty string returned from long Oracle text field

I was returning data from an Oracle procedure using an OracleDataAdapter. I noticed that one text field that can hold large values was returning an empty string. So in the following, the “description” value was empty, when I knew the “ParkDescription” field in the database had a value:

string description = ds.Tables[0].Rows[0]["ParkDescription"].ToString();

Running the query in Toad, I could see the data was there. To resolve this, I needed to set the InitialLONGFetchSize  propety of the data adapter’s SelectCommand object to -1:

OracleDataAdapter adp = new OracleDataAdapter(comm);
adp.SelectCommand.InitialLONGFetchSize = -1;

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.