Tuesday, January 19, 2010

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.

13 comments:

moodboom said...

Well done, I came to the same conclusions. Why is this so hard, and not documented well anywhere? You'd think Oracle and Microsoft would be able to work out a better solution. Oh well, thanks for breaking down the working solution so well!

Michael said...

Thanks for the post, proved very useful, however for anyone else reading be aware of a couple of things
1) You need to add the following to the package spec
TYPE RefCursorType IS REF CURSOR;
If you don't you'll get an error from Oracle while compiling the package.
2) In the package body, before you call Extend, add the following
iMyIDList := IDTableType();
If you don't you'll get an ORA-06531 when you try to execute the function.

elLoco said...

Thanks! Just updated the article

Anonymous said...

How many elements is considered large for the Table(delimited) usage that it starts to slow down the performance? Over 100? 1,000? 10,000?

elLoco said...

I guess you refer to

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

100 are ok
1000 can be acceptable
10000 sucks

performance degrades exponentially

George Joseph said...

There is an alternative to binding the "IN" clause. However it doesn't use bind variables, which in my opinion is a really bad idea especially in concurrent situations.

How it works is that the in clause be stored as string variable with comma seperated values. Take special note of the leading and trailing comma here
var_in_string=',1,2,3,4,'

Then construct the query block in this manner.

SELECT * FROM TABLE WHERE var_in_string LIKE '%,'||ID||',%'

This basically does a pattern matching between the passed in variable and the column value of id in the database table after concatenating a comma

so if the table has values of id
(1,9,0,10)

the comparison would be(i am showing one row at a time)
',1,2,3,4,' like '%,1,%' which will return true

JazzHarmonicat said...

I'm just now coming back to Oracle after working with MS SQL Server for a couple of years. I don't know if Oracle can do this, but in SQL Server, one way is to:
1. In C#, format the array or list as an XML "table".
2. Pass the entire XML string as VARCHAR2 to the stored proc.
3. Inside the stored proc, parse the XML into a local table variable (or a TEMP table could be used, which if shared could have a GUID callerid field to differentiate).
4. Format the SQL as
WHERE a. IN (SELECT * FROM )

Might run faster if using a global temp table.

JazzHarmonicat said...

Also in SQL Server, there is a Split( ) function that can be used to load a temp table or local table variable from a comma-delimited string (which works only if no commas in the values included in the list). Not sure if anything like this exists in Oracle, but a Split( ) function could easily be written.

Anonymous said...

This is easy in Sql. Place your array in a datatable. Go to sql and create a user defined data table type. Now pass your data table to the stored procedure and you can select from it as you would a normal table. Why on earth does oracle not have this functionality yet?????

Anonymous said...

I am new to all this and I am having trouble implementing this code example. I don't know what to put in the line:

da.Fill(myDataSet);


What is myDataSet?

I can get into the database and call my procedure which needs 4 inputs, but it does not get called with any parameters.

Martin Mihalovic said...

That all works fine when the array has items, but I get
System.InvalidOperationException: OracleParameter.Value is invalid
when the array is empty. And empty array is valid input for my procedure. Do you know some way to handle this?

Robert Cline said...

@Martin,
In your case you are either expecting everything back from from the call or no data returned back from the call. In either case your solution is fairly straight forward if handled on the .Net side.

In the first case, call a different procedure in Oracle that does NOT require the variable (e.g. get_all_**** instead of get_****).

In the second case don't call Oracle at all, just return an empty initialized set.

Kamran said...

What if I want to pass array of files (byte[]) to stored procedure?

Please see my post and please tell me if there exists any solution for doing it.

http://stackoverflow.com/questions/24360736/inserting-multiple-files-in-pl-sql-by-calling-stored-procedure-from-c-sharp