Oracle's PL/SQL is fairly new to me, so I need some help understanding if the way I'm trying to use parameters in a Merge's Using clause is even possible.
I'm working with Oracle 11g using ODP.NET for communication with an existing C# .NET 4.0 code-base that uses the SQL connection to retrieve/modify data. The existing SQL statement looks like so:
MERGE INTO Worker Target
USING
(
SELECT
:Id0 Id
,:Options0 Options
FROM dual
UNION ALL
SELECT
:Id1 Id
,:Options1 Options
FROM dual
) Source
ON (Target.Id = Source.Id)
WHEN MATCHED THEN
UPDATE SET
Target.StateId = :StateId
,Target.Options = Source.Options
The Using clause is generated in a C# StringBuilder to accomodate a varying number of worker Id/Option pairs, while at the same time the matching parameters are created.
StringBuilder usingClause = new StringBuilder();
List<OracleParameter> parameters = new List<OracleParameter>();
for (int i = 0; i < workers.Count; ++i)
{
if (i > 0)
usingClause.Append("UNION ALL\n");
usingClause.AppendFormat("SELECT\n :Id{0} Id\n ,:Options{0} Options\n FROM dual\n", i);
parameters.Add(new OracleParameter("Id" + i, workers[i].Id));
parameters.Add(new OracleParameter("Options" + i, workers[i].Options))
}
parameters.Add(new OracleParameter("StateId", pendingStateId));
The usingClause StringBuilder is combined with the rest of the Merge command into a string called 'sql', which is then used in an OracleCommand object. The C# to execute the SQL Merge statement looks like so:
OracleConnection cn = new OracleConnection(
ConfigurationManager.ConnectionStrings["OracleSystemConnection"].ConnectionString
);
using (OracleCommand cmd = new OracleCommand(sql, cn))
{
cmd.BindByName = true;
cn.Open();
foreach (OracleParameter prm in parameters)
cmd.Parameters.Add(prm);
cmd.ExecuteNonQuery();
cn.Close();
}
I've tried it both with and without binding the parameters by name, and ensuring the order is correct when binding without the parameters by name. What I keep getting is an "ORA-01008: not all variables bound" error.
I've also tried running the Merge command in SQL Developer, and get a response of "Bind Variable 'Id0' is NOT DECLARED." Usually when I run a command in SQL Developer with undeclared bind variables it opens a dialog to enter the values, but not with this SQL command, so it's understandable that it would be undeclared in SQL Developer, but I don't understand why this is the case with the ODP.NET/C# implementation, since I'm adding the parameters to the OracleCommand object.
If anyone could point out what I'm doing wrong, or tell me how to achieve the same effect, it would be greatly appreciated. Also, if anyone knows of a better way to pass a list of values into a Merge's Using clause, than doing a bunch of SELECTs FROM dual with UNION ALLs between them, it would be appreciated as well.
Answer Using Long Raw for the Options Column
After a bit of work, this was the final solution. Thanks to tomi44g for pointing me in the right direction.
DECLARE
TYPE id_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE option_array IS TABLE OF LONG RAW INDEX BY PLS_INTEGER;
t_ids id_array := :ids;
t_options option_array := :options;
BEGIN
FORALL i IN 1..t.ids.count
EXECUTE IMMEDIATE '
MERGE INTO Worker Target
USING (SELECT :1 Id, :2 Options FROM dual) Source
ON (Source.Id = Target.Id)
WHEN MATCHED THEN
UPDATE SET
Target.StateId = :3
,Target.Options = Source.Options' USING t_ids(i), t_options(i), :state_id;
END;
And this is what the C# changed to, to accomodate the solution.
// Gather the values into arrays for binding.
int[] workerIds = new int[workers.Count];
byte[][] workerOptions = new byte[workers.Count][];
BinaryFormatter binaryFormatter = new BinaryFormatter();
for (int i = 0; i < workers.Count; ++i)
{
workerIds[i] = workers[i].Id;
// There's an assumed limit of 4096 bytes here; this is just for testing
MemoryStream memoryStream = new MemoryStream(4096);
binaryFormatter.Serialize(memoryStream, workers[i].Options);
workerOptions[i] = memoryStream.ToArray();
}
// Excute the command.
OracleConnection cn = new OracleConnection(
ConfigurationManager.ConnectionStrings["OracleSystemConnection"].ConnectionString
);
using (OracleCommand cmd = new OracleCommand(sql, cn))
{
cmd.BindByName = true;
cn.Open();
OracleParameter ids = new OracleParameter();
ids.OracleDbType = OracleDbType.Int32;
ids.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
ids.Value = workerIds;
ids.ParameterName = "ids";
OracleParameter options = new OracleParameter();
options.OracleDbType = OracleDbType.LongRaw;
options.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
options.Value = workerOptions;
options.ParameterName = "options";
cmd.Parameters.Add(ids);
cmd.Parameters.Add(options);
cmd.Parameters.Add(new OracleParameter("state_id", pendingStateId));
try
{
cmd.ExecuteNonQuery();
}
catch (OracleException e)
{
foreach (OracleError err in e.Errors)
{
Console.WriteLine("Message:\n{0}\nSource:\n{1}\n", err.Message, err.Source);
System.Diagnostics.Debug.WriteLine("Message:\n{0}\nSource:\n{1}\n", err.Message, err.Source);
}
}
cn.Close();
}
