String Array UDT for Oracle, and Something Better

For work I was creating a simple website that allowed a user to batch multiple items together.  I was planning on taking the id’s of the items that they batched, and passing them into an Oracle SP, so they could be used in a “Where in” statement.  In MS-SQL you can hack together a solution doing some fancy string parsing, and the Execute query function, but you have lots of issues if your csv string is longer than 8000 characters.  I wanted to do it the “right” way in Oracle using a UDT (User Defined Type).

Basically to be able to pass an Oracle UDT as a parameter to a SP from C#, you have to create a C# class for your UDT which implements IOracleCustomType, INullable, and a factory to create the objects. First, here is the Oracle Code for the UDT and the SP:

create or replace

TYPE STRINGARRAY_UDT AS varray(3000) OF VARCHAR2(30);

create or replace

PROCEDURE TEST_ARRAY

(

  IN_MODELS IN STRINGARRAY_UDT,

  OUT_MODELS OUT SYS_REFCURSOR

) AS


BEGIN

  OPEN OUT_MODELS FOR

    SELECT * from Items WHERE id IN (SELECT * FROM table(IN_MODELS));

END TEST_ARRAY;

The C# code is rather simple as well, but you have to create a class for each UDT that you want to use.  This is a real pain (which leads me onto something better).  If you want to create a UDT with types that are not nullable, you have to maintain your own OracleStatus array to determine if the element is null or not, and pass it into the OractleUdt.SetValue and OracleUdt.GetValue.  The StringArrayUDTFactory get’s called by Oracle to create the object, it then uses reflection to call the FromCustomeObject or ToCustomObject methods, depending on whether you’re passing the UDT to oracle, or retrieving it from Oracle.

public class StringArrayUDT : IOracleCustomType, INullable
{
    [OracleArrayMapping()]
    public string[] Array { get; set; }

    public StringArrayUDT()
    {
        Array = null;
    }

    public StringArrayUDT(string[] array)
    {
        Array = array;
    }

    #region INullable Members

    public bool IsNull { get { return Array == null; } }

    #endregion

    #region IOracleCustomType Members

    public void FromCustomObject(OracleConnection con, System.IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, 0, Array);
    }

    public void ToCustomObject(OracleConnection con, System.IntPtr pUdt)
    {
        Array = (string[])OracleUdt.GetValue(con, pUdt, 0);
    }

    public override string ToString()
    {
        if (IsNull)
            return "NULL";
        else
        {
            return "StringArrayUDT('" + String.Join("', '", Array) + "')";
        }
    }

    #endregion
}

/* StringArrayUDTFactory Class
**   An instance of the StringArrayUDTFactory class is used to create 
**   StringArrayUDT objects
*/
OracleCustomTypeMapping("MySchema.STRINGARRAY_UDT")]
public class StringArrayUDTFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
    #region IOracleCustomTypeFactory Members

    public IOracleCustomType CreateObject()
    {
        return new StringArrayUDT();
    }

    #endregion

    #region IOracleArrayTypeFactory Members

    public System.Array CreateArray(int numElems)
    {
        return new String[numElems];
    }

    public System.Array CreateStatusArray(int numElems)
    {
        return new OracleUdtStatus[numElems];
    }

    #endregion
}

Once all of this gets created, you can add your class as a parameter directly to an OracleCommand object for an SP.  But after doing all that work, with all of it’s custom code, my co-worker showed me a better way for this example, done entirely in Oracle.  The bulk of the work is done in this SplitByChar function:

FUNCTION SPLITBYCHAR

(

    p_list varchar2,

    p_del varchar2 := ','

) return split_tbl pipelined

is

    l_idx    pls_integer;

    l_list    varchar2(32767) := p_list;

    l_value    varchar2(32767);

begin

    loop

        l_idx := instr(l_list,p_del);

        if l_idx > 0 then

            pipe row(substr(l_list,1,l_idx-1));

            l_list := substr(l_list,l_idx+length(p_del));

        else

            pipe row(l_list);

            exit;

        end if;

    end loop;

    return;

END SPLITBYCHAR;

And then our Test Function becomes this:

create or replace


PROCEDURE TEST_ARRAY


(


  IN_MODELS IN varchar2,


  OUT_MODELS OUT SYS_REFCURSOR


) AS


BEGIN


  OPEN OUT_MODELS FOR


    SELECT * from Items WHERE id IN (SELECT * FROM table(SPLITBYCHAR(IN_MODELS)));


END TEST_ARRAY;

Where IN_MODELS is a csv string.  This puts a little more work on the Oracle database, but is much better on the application side of things.