Joe Gayetty Joe Gayetty - 1 month ago 6
C# Question

Why does this RETURNING INTO clause return an error?

I am trying to perform an insert into an Oracle database table and return the newly created primary key (through a trigger and sequence).

I have one piece of code that works. I have a second piece of code that returns an error on ExecuteNonQuery().

I cannot for the life of me determine why the first code works and the second does not.

The first code that WORKS:

string sqlStr = @"INSERT INTO LEI_EROUTER_SESSIONS(empCID, JOBNUMBER, EMP_NAME, LOGGEDON, MACHINENAME, IP) values(:empCID, :JOBNUMBER, :EMP_NAME, sysdate, :MACHINENAME, :IP) RETURNING C_ID INTO :LASTCID";

int C_ID;

using (RetryClass RetryClassInstance = new RetryClass(CallingForm, JobSessionData, UserData))
{
do
{
RetryClassInstance.Retry = false;
C_ID = 0;
OracleConnection conn = new OracleConnection(Machine_Data.oracle_connstr);
OracleCommand cmd = new OracleCommand(sqlStr, conn);
try
{
conn.Open();
cmd.Parameters.Add("empCID", UserData.employee_cid);
cmd.Parameters.Add("JOBNUMBER", JobSessionData.jobnumber);
cmd.Parameters.Add("EMP_NAME", UserData.employee_name);
//cmd.Parameters.Add("LOGGEDON", DateTime.Now);
cmd.Parameters.Add("MACHINENAME", Environment.MachineName);
cmd.Parameters.Add("IP", GlobalFunctions.LocalIPAddress());
cmd.Parameters.Add("LASTCID", OracleDbType.Int32, ParameterDirection.Output);
cmd.ExecuteNonQuery();

C_ID = Convert.ToInt32(cmd.Parameters["LASTCID"].Value.ToString());
}
catch (Exception ex)
{
RetryClassInstance.HadException(ex);
}
finally
{
if (cmd != null) cmd.Dispose();
if (conn != null) conn.Dispose();
}
} while (RetryClassInstance.Retry == true);
}

return C_ID;


Here is the second piece of code that DOES NOT WORK:

string C_ID;
List<string> C_IDS = new List<string>();

string sqlStr = @"INSERT INTO LEI_CHECKIN_QUEUE(CHECKIN_DATE, JOB_ID, CELL, SN, STEP_NAME, STEP_TYPE, START_SEQ,
END_SEQ, CHECKEDINBY_EMP_CID, IN_QUEUE, PRIORITY_CODE, STEP_STARTED_WHEN, GROUP_CID)
VALUES(SYSDATE, :JOB_ID, :CELL, :SN, :STEP_NAME, :STEP_TYPE, :START_SEQ, :END_SEQ, :CHECKEDINBY_EMP_CID, 'Y', null,
null, null) RETURNING C_ID INTO :LASTCID";

using (RetryClass RetryClassInstance = new RetryClass(this, JobSessionData, UserData))
{
do
{
RetryClassInstance.Retry = false;
OracleConnection conn = new OracleConnection(Machine_Data.oracle_connstr);
OracleCommand cmd = new OracleCommand(sqlStr, conn);
try
{
conn.Open();

foreach (string SN in JobSessionData.serial_numbers)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("JOB_ID", JobSessionData.jobnumber);
cmd.Parameters.Add("CELL", JobSessionData.cell);
cmd.Parameters.Add("STEP_NAME", StepName);
cmd.Parameters.Add("STEP_TYPE", StepType);
cmd.Parameters.Add("START_SEQ", Start_Seq);
cmd.Parameters.Add("END_SEQ", End_Seq);
cmd.Parameters.Add("CHECKEDINBY_EMP_CID", UserData.employee_cid);
cmd.Parameters.Add("SN", SN);
cmd.Parameters.Add("LASTCID", OracleDbType.Int64, ParameterDirection.Output);

cmd.ExecuteNonQuery();

C_ID = cmd.Parameters["LASTCID"].Value.ToString();
C_IDS.Add(C_ID);
}

}
catch (Exception ex)
{
RetryClassInstance.HadException(ex);
}
finally
{
if (cmd != null) cmd.Dispose();
if (conn != null) conn.Dispose();
}
} while (RetryClassInstance.Retry == true);
}


The error returned from the second piece of code is as follows:

System.IndexOutOfRangeException: Index was outside the bounds of the array.
at OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse(Accessor[]& defineAccessors, Accessor[] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int32 initialLOBFetchSize, Int64[] scnFromExecution, Boolean& bAllPureInputBinds, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Boolean bDefineDone, Boolean& bMoreThanOneRowAffectedByDmlWithRetClause)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int32 lobPrefetchSize, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()


Both are run on the same Oracle database instance. And the second piece of code works fine if I comment out the lines pertaining to the OUTPUT PARAMETER and remove the returning clause from the SQL INSERT statement.

Also, the LEI_CHECKIN_QUEUE table DOES have a column named C_ID which is the primary key.

Does anyone have any clues about why the second piece of code is not working and/or what I can do to fix it?

Thanks!

Answer

You need to declare the variable as shown below. As a rule of thumb, always test your query on the Oracle server before you embed it into your code. Most, importantly use parametrized Store Procedures to avoid sql injection attacks. So Dot embed queries into your code.

@"declare   LASTCID number;
INSERT INTO LEI_CHECKIN_QUEUE(CHECKIN_DATE, JOB_ID, CELL, SN, STEP_NAME, STEP_TYPE, START_SEQ,
                          END_SEQ, CHECKEDINBY_EMP_CID, IN_QUEUE, PRIORITY_CODE, STEP_STARTED_WHEN, GROUP_CID) 
                          VALUES(SYSDATE, :JOB_ID, :CELL, :SN, :STEP_NAME, :STEP_TYPE, :START_SEQ, :END_SEQ, :CHECKEDINBY_EMP_CID, 'Y', null, 
                          null, null) RETURNING C_ID INTO :LASTCID";