First off, excuse anything simple I may have over looked. I do not have a lot of oracle experience. I've inherited an old code base of which a snip is below (cleaned a bit for privacy). We are getting sporadic
ORA-22922: nonexistent LOB value errors. It's creating a blob and inserting. By sporadic I mean a few out of hundreds of transactions. Can anyone insert any light into this error or point me in a starting direction?
>
public void InsertRecord(string p1, string p2, string fileName, string fileLocation, string filePath, string fileSize, bool successFailure, string orphanFlag, string logData)
{
string sSql = null;
DataSet dsData = new DataSet();
string serverName = string.Empty;
OracleParameter[] arrParam = new OracleParameter[10];
string sConn = "conn string here";
int iRowdel = 0;
string refMsg = string.Empty;
byte[] byLogmsg = System.Text.Encoding.ASCII.GetBytes(logData);
string statusMessage = string.Empty;
OracleTransaction oraTrans = default(OracleTransaction);
OracleConnection oConn = new OracleConnection();
try
{
serverName = "serv1";
statusMessage = "BLOBTEST";
oConn.ConnectionString = sConn;
oConn.Open();
oraTrans = oConn.BeginTransaction();
arrParam[0] = new OracleParameter("dnumber", p1);
arrParam[1] = new OracleParameter("store", p2);
arrParam[2] = new OracleParameter("servername", serverName);
arrParam[3] = new OracleParameter("filename", fileName);
arrParam[4] = new OracleParameter("filelocation", fileLocation);
arrParam[5] = new OracleParameter("filepath", filePath);
arrParam[6] = new OracleParameter("filesize", fileSize);
arrParam[7] = new OracleParameter("status", statusMessage);
arrParam[8] = new OracleParameter("orphanflag", orphanFlag);
arrParam[9] = new OracleParameter("log", System.Data.OracleClient.OracleType.Blob);
arrParam[9].Value = getOracleBlob(ref oraTrans, byLogmsg, false);
oConn.Close();
sSql = "Insert INTO job (dealernumber,storenumber,servername,filename,filelocation,filepath,filesize,status,orphan,data) values(:dnumber,:store,:servername,:filename,:filelocation,:filepath,:filesize,:status,:orphanflag,:log)";
Cmn.DBSqlExec(oConn, sSql, CommandType.Text, ref iRowdel, ref refMsg, arrParam);
}
catch (Exception ex)
{
oConn.Close();
}
finally
{
if (oConn.State == ConnectionState.Open)
{
oConn.Close();
}
oConn.Dispose();
oConn = null;
oraTrans = null;
arrParam = null;
}
}
private OracleLob getOracleBlob(ref OracleTransaction voDBTransaction, byte[] aOfBytesForBlob, bool bNeedNullBlob)
{
bNeedNullBlob = false;
OracleLob functionReturnValue = default(OracleLob);
OracleLob voOracleBlob = default(OracleLob);
OracleCommand voOracleCommand = new OracleCommand();
string sQuery = "DECLARE dpBlob BLOB; BEGIN DBMS_LOB.CREATETEMPORARY(dpBlob, False, DBMS_LOB.CALL); :tmpBlob := dpBlob; END;";
int iSQLReturnCode = -1;
try
{
voOracleCommand = voDBTransaction.Connection.CreateCommand();
voOracleCommand.Connection = voDBTransaction.Connection;
voOracleCommand.Transaction = voDBTransaction;
voOracleCommand.CommandText = sQuery;
voOracleBlob = OracleLob.Null;
voOracleCommand.Parameters.Add(new OracleParameter("tmpBlob", OracleType.Blob)).Direction = ParameterDirection.Output;
iSQLReturnCode = voOracleCommand.ExecuteNonQuery();
voOracleBlob = (OracleLob)voOracleCommand.Parameters[0].Value;
if (!(bNeedNullBlob))
{
if ((aOfBytesForBlob != null))
{
voOracleBlob.BeginBatch(OracleLobOpenMode.ReadWrite);
voOracleBlob.Write(aOfBytesForBlob, 0, aOfBytesForBlob.Length);
voOracleBlob.EndBatch();
}
}
return voOracleBlob;
}
catch (Exception ex)
{
functionReturnValue = OracleLob.Null;
return functionReturnValue;
}
}