Oracle Dynamic SQL – Drop Table, Copy Table, Purge Tables
CREATEORREPLACEPROCEDURE COMPLIANCE11.table_drop (table_name VARCHAR2) IS dml_str VARCHAR2 (500);BEGIN dml_str := 'DROP TABLE ' || table_name ; EXECUTEIMMEDIATE dml_str ;END;
void the_Table_Housekeeping()
{
try
{
string JobDateTime_string = _jobDateTime.ToString("yyyy-MM-dd_HH-mm-ss");
JobDateTime_string = JobDateTime_string.Replace("-", "");
JobDateTime_string = JobDateTime_string.Replace("_", "");
Microsoft_Enterprise_Library.Oracle.OracleAccessLayer objData = new Microsoft_Enterprise_Library.Oracle.OracleAccessLayer();
//-----------------------------------
// look for dupe table before drop table
//-----------------------------------
string ssql = "select nvl((select 1 from user_tables where table_name='THE_" + JobDateTime_string + "' and rownum=1),0) from dual";
DataTable dt = objData.GetDataTableFromOracleSQL(ssql, this._Oracle_Compliance11connectionString);
if (dt.Rows.Count < 1)
{
throw new Exception("dt.Rows.Count < 1");
}
int found = int.Parse(dt.Rows[0][0].ToString());
OracleCommand oracleCommand = new OracleCommand();
if (found == 1)
{
//-----------------------------------
// drop table
//-----------------------------------
oracleCommand.CommandText = "table_Drop";
oracleCommand.Parameters.Clear();
oracleCommand.Parameters.Add("table_name", OracleType.VarChar).Value = "THE_" + JobDateTime_string;
int rowsAffected = objData.GetRowsAffected_From_StoredProc(this._Oracle_Compliance11connectionString,
oracleCommand);
}
//-----------------------------------
// does not work, "insufficient privileges"
//-----------------------------------
//oracleCommand.CommandText = "table_copy";
//oracleCommand.Parameters.Clear();
//oracleCommand.CommandType = CommandType.StoredProcedure;
//oracleCommand.Parameters.Add("table_name_orig", OracleType.VarChar).Value = "THE_Table";
//oracleCommand.Parameters.Add("table_name_new", OracleType.VarChar).Value = "THE_" + JobDateTime_string;
//int rowsAffected2 = objOracle.GetRowsAffected_From_StoredProc_Transaction();
//-----------------------------------
// create table
//-----------------------------------
StringBuilder sb = new StringBuilder();
sb.AppendLine("CREATE TABLE THE_" + JobDateTime_string + " AS (SELECT * FROM THE_Table)");
string mySQL = sb.ToString();
oracleCommand.CommandType = CommandType.Text;
int rowsAffected2 = objData.ExecuteNonQuery_SQL(
mySQL,
this._Oracle_Compliance11connectionString,
false);
//-----------------------------------
// purge to 4 previous copies
//-----------------------------------
sb = new StringBuilder();
ssql = "select table_name from user_tables where table_name LIKE 'THE_2%' order by table_name asc";
dt = objData.GetDataTableFromOracleSQL(ssql, this._Oracle_Compliance11connectionString);
for (int i = 0; i < dt.Rows.Count - 4; i++)
{
string table_name = dt.Rows[i][0].ToString();
//-----------------------------------
// drop table
//-----------------------------------
oracleCommand.CommandText = "table_Drop";
oracleCommand.Parameters.Clear();
oracleCommand.Parameters.Add("table_name", OracleType.VarChar).Value = table_name;
int rowsAffected = objData.GetRowsAffected_From_StoredProc(this._Oracle_Compliance11connectionString,
oracleCommand); ;
}
}
catch (Exception ex)
{
throw ex;
}
}