Archive for category ADO.NET

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.

Advertisements

Leave a comment

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;

Leave a comment

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.

Leave a comment