Guess you have the following query
and you want to pass a number of IDs in the binding variable 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):
In PL/SQL now create a package
What is going on here?
First thing you notice is that we have 2 very similar array (table) types.
Globally we defined the
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:
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);
It's not working!
I can't find neither
cmd.BindByName = true;
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
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
And if you want to develop in SQL and simply convert it with our macro you can add the following function to your package:
and rewrite your regular SQL to:
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:
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.....
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.