Second insert fails because of returning into output parameter (OracleParameter)

Question

I am trying to insert two rows into a table (sequentiel). The first insert command is executed without any trouble. The second insert command gets stuck in the ExecuteNonQuery() part. When I remove the returning part (OracleParameter with Direction = Output) both inserts are successful.

Doesn't work (executed twice in a sequence with different data each):

cmd.CommandText = "INSERT INTO table (Col1, Col2, Col3,...) VALUES (:ParamCol1, :ParamCol2, :ParamCol3,...) RETURNING ROWIDTOCHAR(ROWID) INTO :OutputROWID";

Works:

cmd.CommandText = "INSERT INTO table (Col1, Col2, Col3,...) VALUES (:ParamCol1, :ParamCol2, :ParamCol3,...)";

The output-parameter is declared and initialized like the following:

OracleParameter outputRowId = new OracleParameter(":OutputROWID", OracleDbType.Varchar2, 30) { Direction = ParameterDirection.Output })

Reference: OracleParameter

The funny thing is, it also works when I use the first method (with the returning ROWID), but only when I rename the parameter in the second insert to i.e. :OutputROWID2.

Any ideas on that why the execution gets stuck and how I can solve this?

EDIT:

Here the longer Code-Snippet:

using (OracleTransaction transaction = Globals.Db.Connection.BeginTransaction())
            {

                using (OracleCommand cmd = Globals.Db.Connection.CreateCommand())
                {
                    cmd.BindByName = true;
                    cmd.CommandText = "INSERT INTO table (Col1, Col2, Col3,...) VALUES (:ParamCol1, :ParamCol2, :ParamCol3,...) RETURNING ROWIDTOCHAR(ROWID) INTO :OutputROWID";

                    OracleParameter outputRowId = new OracleParameter(":OutputROWID", OracleDbType.Varchar2, 30) { Direction = ParameterDirection.Output };


                    cmd.Parameters.Add(paramCol1, data["Col1"]);
                    cmd.Parameters.Add(paramCol2, data["Col2"]);
                    cmd.Parameters.Add(paramCol3, data["Col3"]);
                    ...
                    cmd.Parameters.Add(outputRowId);

                    try
                    {
                        cmd.ExecuteNonQuery();
                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        return;
                    }
                }
            }

I've put the snippet into the edit-part – hy13 Jan 30 '14 at 07:55 — hy13, Jan 30 '14 at 07:55
I'd still like to see how the posted code is called twice – b_levitt Jan 31 '14 at 15:51 — b_levitt, Jan 31 '14 at 15:51

score 0 · Answer 1 · answered Jan 30 '14 at 08:03

0

Try to define outputRowId as ParameterDirection.InputOutput

answered Jan 30 '14 at 08:03

bdn02

1,500
9
15

haven't solved the problem. it still gets stuck in the `ExecuteNonQuery()` part – hy13 Jan 30 '14 at 09:20
I have successfully tested your code on oracle database 10.2.0.4, .NET Framework 2.0 and Oracle.DataAccess 2.11.7.20. What is the exact error message that you obtain? – bdn02 Jan 30 '14 at 13:05
I don't get an error. That's the funny thing. He jumps into the `ExecuteNonQuery`and then it never jumps out. If I don't use the [OracleTransaction](http://docs.oracle.com/html/E10927_01/OracleTransactionClass.htm#sthref2008) the Row is added into the table but it also doesn't jump out of the `ExecuteNonQuery`. I am also using a later Version of the Oracle.ManagedDataAccess (it's 4.112.3.50) and .NET 4.0. – hy13 Jan 30 '14 at 13:23
I don't know, i've founded that exist System.Data.ParameterDirection.ReturnValue, i've tested on my application and i have no errors. Give you try? (sorry for mi english) – bdn02 Jan 30 '14 at 13:52
I've test it now with ReturnValue but it's the same result. It gets stuck. – hy13 Jan 30 '14 at 14:16

score 0 · Answer 2 · answered Jan 30 '14 at 16:57

The fix by renaming the output variable somewhat points to an issue with statement caching:

Try one of the following:

In your connection string set: Statement Cache Size=0 (not entirely sure 0 means don't cache)
In your connection string set: Statement Cache Purge=true
...and create a new connection for the second operation (statement cache purge applies when closing a connection) http://docs.oracle.com/cd/B19306_01/win.102/b14307/featOraCommand.htm#CIHCAFIG

Can you show how you are calling the statement twice?

Note: that catch statement makes my skin crawl - my #1 deadliest sin of programming is swallowing errors like that. Maybe you're were trying to be brief but if so swap that return with a throw ex;

I've tested your suggestions but the same problem appears again and again. I've set the CacheSize=0 and the CachePurge=true and build the connectionstring with the OracleConnectionStringBuilder (otherwise I couldn't set both of them). But it doesn't worked. To create a new connection hasn't solved it too. The second call is exactly the same. There is no difference. only the content of parameters is different. Regarding to the note: to calm you. It only looks so in this code snippet. in the original code the exception is handled. — hy13, Jan 31 '14 at 07:55

Second insert fails because of returning into output parameter (OracleParameter)

2 Answers2