Geeks With Blogs
Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done
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;
            }
        }
 
 
 
Posted on Monday, October 10, 2011 2:43 PM Oracle | Back to top


Comments on this post: Oracle Dynamic SQL – Drop Table, Copy Table, Purge Tables

# re: Oracle Dynamic SQL – Drop Table, Copy Table, Purge Tables
Requesting Gravatar...

It's a good thing taht the people behind that cooperative was that generous for all. used inversion tables
invertalign inversion table
Left by rikyjake on Nov 02, 2011 5:45 AM

# re: Oracle Dynamic SQL – Drop Table, Copy Table, Purge Tables
Requesting Gravatar...
Great stuff, I don't know how you do it!
Left by canvas print on Nov 10, 2011 4:13 AM

# re: Oracle Dynamic SQL – Drop Table, Copy Table, Purge Tables
Requesting Gravatar...
Left by Craig on Nov 29, 2011 11:35 PM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net