How to pass arrays from .Net C# to Oracle

Guess you have the following query

select * from table where table.id in (:MyIDList)

and you want to pass a number of IDs in the binding variable :MyIDList from C# to your Oracle SQL. If you now check the types of binding variables available for this job, you find out that this Oracle only supports scalar types as binding variables.

If you now dig further you find a couple of bright solution that are all based on splitting up some string (varchar2) into several elements. But due to the fact, that the length of varchar2 binding variables is limited to 4/32K SQL/PLSQL this is not a scalable solution to the problem.

Even if you think you are smart, and you try a string replace of :MyIDList with the elements like 'a','b','c' let me assure you that the limit for written elements in an in statement is 1000. Also your performance will degrade significantly, as the query cache won't recognize the SQL as being executed before and therefore Oracle has to recompile it with every execution.

Is it impossible to pass an variable length array to SQL?

Let me put it straight!

Yes and No.

In pure SQL it is impossible to pass a variable length array to SQL.

But in PL/SQL it is not.

"Yes great", you think, "but I need it in SQL!"

The trick is a PL/SQL wrapper to SQL!

Simply use the following ingredients:

Define 2 global types (and don't try to be smart here. We need IDType for couple of reasons):

CREATE OR REPLACE

type IDTYPE as object (

id varchar2(20)

);

CREATE OR REPLACE

type IDTABLETYPE as table of IDType;

In PL/SQL now create a package

CREATE OR REPLACE PACKAGE MYPACKAGE

as

type stringTableType is table of varchar2(20) index by binary_integer;

procedure GetMyTableByIDs

(

p_MyIDList IN stringTableType,

p_outRefCursor out RefCursorType

);

end;

CREATE OR REPLACE PACKAGE BODY MYPACKAGE

as

TYPE RefCursorType IS REF CURSOR;

procedure GetMyTableByIDs

(

p_MyIDList IN stringTableType,

p_outRefCursor out RefCursorType

)

as

iMyIDList IDTableType;

begin

iMyIDList := IDTableType();

iMyIDList.Extend(p_MyIDList.count);

for i in p_MyIDList.First .. p_MyIDList.Last

loop

iMyIDList(i) := IDType(p_MyIDList(i));

end loop;

open p_outRefCursor

for

select * from table where table.id in (select id from table(iMyIDList));

end GetMyTableByIDs;

end;

What is going on here?

First thing you notice is that we have 2 very similar array (table) types.

Globally we defined the

type IDTABLETYPE as table of IDType -- IDType is varchar2(20)

This is in Oracle terms a "Nested Table". This type is available in SQL and PL/SQL. IDType brings the property "ID" of type varchar2(20).

In PL/SQL we defined the very similar type:

type stringTableType is table of varchar2(20) index by binary_integer;

This is an "index by table" or "associative array" in oracle terms. Associative arrays are better understood as "HashTable" and are available in PL/SQL only. For a more detailed explanation of the differences please have a look at "Collection Types in PL/SQL".

But why do you copy the arrays one by one?

Because you now see that Oracle has obviously 2 different development units for SQL and PL/SQL. And they do not seem to talk very much together.

The result of 3 days in short:

• There is no way to pass a nested table as parameter to a stored procedure in C#
• There is no way to use a associative array in SQL

• There is no way to assign/initialize a nested table to/with an associative array

Great, but how do we use it in C#?

OracleConnection conn = new OracleConnection("MyConnectionString");

OracleCommand cmd = conn.CreateCommand();

cmd.CommandText = "MyPackage.GetMyTableByIDs";

cmd.CommandType = CommandType.StoredProcedure;

cmd.BindByName = true;

cmd.Parameters.Add(new OracleParameter("p_outRefCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output;

cmd.Parameters.Add(new OracleParameter("p_MyIDList", OracleDbType.Varchar2)

{

CollectionType = OracleCollectionType.PLSQLAssociativeArray,

Value = my_list_with_ids.ToArray()

}

);

da = new OracleDataAdapter(cmd);

da.Fill(myDataSet);

It's not working!

I can't find neither

cmd.BindByName = true;

nor

OracleCollectionType.PLSQLAssociativeArray

The constraint of the solution is that you have to use Oracle .NET driver (Oracle.DataAccess) instead of the the Microsoft (System.Data.OracleClient) driver. But with .Net 4 the Microsoft's Oracle driver is marked deprecated anyway. So get used to it.

This is it. The only way to pass an array to a SQL.

"Holy shivers!" you think. This is a lot of glue for a simple task like this!

Basically yes, BUT

You can do 3 things now:

1.) I use a macro that converts my SQL into PL/SQL by automatically replacing the binding variable :MyIDList with the PL/SQL Parameter p_myIDList.

2.) You can tune your performance significantly by rewriting your SQL

3.) You can clean up your code a lot by using default values

Improve overall performance

Our former SQL

select * from table where table.id in (select id from table(iMyIDList))

becomes unbearable slow with a large number of ID's and lines in table. What you can do now is to rewrite our SQL to

select * from table where table.id

join (select id from table(iMyIDList)) IdFilter on table.id = IdFilter.id

And if you want to develop in SQL and simply convert it with our macro you can add the following function to your package:

FUNCTION GetDefaultTable

(

param varchar2

)

RETURN IDTableType

is

begin

return IDTableType(IDType(param));

end;

and rewrite your regular SQL to:

select * from table where table.id

join (select id from table(MyPackage.GetDefaultTable(:MyIDList)) IdFilter

on table.id = IdFilter.id

Use PL/SQL defaults:

A feature of our PL/SQL is that you can define default values for all parameters.

In a simple case this is:

procedure GetSomeThing

(

p_param1 in varchar2 default 'SomeDefaultValue',

);

but what do we do with our associative array?

The fancy part about default parameters is that the value can be a call to function.....

So

procedure GetSomeThing

(

p_MyIDList IN stringTableType default GetDefaultTable('DefaultValueForElement'),

p_param1 in varchar2 default 'SomeDefaultValue',

p_outRefCursor out RefCursorType

);

works nice and easy.

in combination with the line in C#

cmd.BindByName = true;

what you can do now is only pass parameters that differ from their defaults what can be used to write a much nicer code. Instead of passing all the parameters defined a for each procedure defined (results in a clunky piece of code), you just just set the parameter for any stored procedure if the associated value is non-default.