/// <summary>
/// Reorder the rows in an annotation table
/// </summary>
/// <param name="methodId"></param>
/// <returns></returns>
public static long ReorderRows(long methodId)
{
AnnotationDao dao = null;
DbCommandMx cmd = null;
string sql = null, ttName = null;
int insCnt = 0;
try
{
dao = new AnnotationDao();
dao.BeginTransaction();
cmd = dao.DbCmd;
ttName = "tt_" + methodId;
try // delete just in case
{
cmd.PrepareAndExecuteNonReader("truncate table " + ttName, logExceptions: false);
cmd.PrepareAndExecuteNonReader("drop table " + ttName, logExceptions: false);
}
catch (Exception ex) { ex = ex; }
// Create temp table & copy data for method
sql =
"create global temporary table " + ttName + @"
on commit preserve rows
as select* from mbs_owner.mbs_adw_rslt
where mthd_vrsn_id = " + methodId;
cmd.PrepareAndExecuteNonReader(sql);
// Delete existing data
sql =
@"delete from mbs_owner.mbs_adw_rslt
where mthd_vrsn_id = " + methodId;
int delCnt = cmd.PrepareAndExecuteNonReader(sql);
// Reinsert from temp table in desired order into new blocks (e.g. APPEND hint)
sql =
@"insert /*+ APPEND */ into mbs_owner.mbs_adw_rslt
select* from " + ttName + @"
order by ext_cmpnd_id_nbr, rslt_grp_id, rslt_id" ;
insCnt = cmd.PrepareAndExecuteNonReader(sql);
cmd.Commit(); // commit
try // remove temp table
{
cmd.PrepareAndExecuteNonReader("truncate table " + ttName, logExceptions: false);
cmd.PrepareAndExecuteNonReader("drop table " + ttName, logExceptions: false);
}
catch (Exception ex) { ex = ex; }
dao.Dispose();
}
catch (Exception ex)
{
string msg = "Exception for Annotation Table: " + ttName + " ";
DebugLog.Message(msg + DebugLog.FormatExceptionMessage(ex));
try
{
cmd.Rollback(); // rollback if failed
cmd.PrepareAndExecuteNonReader("truncate table " + ttName, logExceptions: false);
cmd.PrepareAndExecuteNonReader("drop table " + ttName, logExceptions: false);
}
catch (Exception ex2) { ex2 = ex2; }
try
{
dao.Dispose();
}
catch (Exception ex3) { ex3 = ex3; }
throw new Exception(msg, ex);
}
return(insCnt);
}