public static List<HWL_Variance> GetCycle(string yearMonth)
        {
            List<HWL_Variance> cycleList = new List<HWL_Variance>();

            OracleConnection connection = HWLOracleDB.GetConnection();
            string sql = "SELECT DISTINCT Cycle FROM HWL_Variance Where YearMonth = " + ":YearMonth" + " ORDER BY Cycle";
            OracleCommand selectCommand = new OracleCommand(sql, connection);
            selectCommand.CommandType = CommandType.Text;

            OracleParameter p_YearMonth = new OracleParameter();
            p_YearMonth.OracleDbType = OracleDbType.Varchar2;
            p_YearMonth.Value = yearMonth;
            selectCommand.Parameters.Add(p_YearMonth);

            OracleDataAdapter da = new OracleDataAdapter(selectCommand);
            OracleCommandBuilder cb = new OracleCommandBuilder(da);
            DataSet ds = new DataSet();

            try
            {
                connection.Open();
                da.Fill(ds);

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    HWL_Variance cycle = new HWL_Variance();
                    cycle.Cycle = Convert.ToInt32(dr["Cycle"]);
                    cycleList.Add(cycle);
                }
            }
            catch (Exception ex)
            {
                frmSQLError errorForm = new frmSQLError();
                errorForm.error = ex.ToString();
                DialogResult selectedButton = errorForm.ShowDialog();
            }
            finally
            {
                connection.Close();
            }

            return cycleList;
        }

public static List<HWL_Variance> GetCycle(string yearMonth)
 {
 List<HWL_Variance> cycleList = new List<HWL_Variance>();

 OracleConnection connection = HWLOracleDB.GetConnection();
 string sql = "SELECT DISTINCT Cycle FROM HWL_Variance Where YearMonth = " + ":YearMonth" + " ORDER BY Cycle";
 OracleCommand selectCommand = new OracleCommand(sql, connection);
 selectCommand.CommandType = CommandType.Text;

 OracleParameter p_YearMonth = new OracleParameter();
 p_YearMonth.OracleDbType = OracleDbType.Varchar2;
 p_YearMonth.Value = yearMonth;
 selectCommand.Parameters.Add(p_YearMonth);

 OracleDataAdapter da = new OracleDataAdapter(selectCommand);
 OracleCommandBuilder cb = new OracleCommandBuilder(da);
 DataSet ds = new DataSet();

 try
 {
 connection.Open();
 da.Fill(ds);

 foreach (DataRow dr in ds.Tables[0].Rows)
 {
 HWL_Variance cycle = new HWL_Variance();
 cycle.Cycle = Convert.ToInt32(dr["Cycle"]);
 cycleList.Add(cycle);
 }
 }
 catch (Exception ex)
 {
 frmSQLError errorForm = new frmSQLError();
 errorForm.error = ex.ToString();
 DialogResult selectedButton = errorForm.ShowDialog();
 }
 finally
 {
 connection.Close();
 }

 return cycleList;
 } 

create or replace PROCEDURE SPVARIANCE_GETROUTE
(
  p_YearMonth VARCHAR2,
  p_Cycle int,
  p_RefCursor OUT SYS_REFCURSOR
)
AS 
BEGIN
  OPEN p_RefCursor FOR
    SELECT DISTINCT Route
    FROM HWL_Variance
    WHERE YearMonth = p_YearMonth AND Cycle = p_Cycle
    ORDER BY Route;
  
END SPVARIANCE_GETROUTE;

public static List<HWL_Variance> GetRoute(string yearMonth, int cycle)
        {
            List<HWL_Variance> routeList = new List<HWL_Variance>();

            OracleConnection connection = HWLOracleDB.GetConnection();
            OracleCommand selectCommand = new OracleCommand("SPVARIANCE_GETROUTE", connection);
            selectCommand.CommandType = CommandType.StoredProcedure;

            selectCommand.Parameters.Add(new OracleParameter("p_YearMonth", yearMonth));
            selectCommand.Parameters.Add(new OracleParameter("p_Cycle", cycle));

            OracleParameter p_RefCursor = new OracleParameter();
            p_RefCursor.OracleDbType = OracleDbType.RefCursor;
            p_RefCursor.Direction = System.Data.ParameterDirection.Output;
            selectCommand.Parameters.Add(p_RefCursor);

            try
            {
                connection.Open();

                OracleDataReader reader = selectCommand.ExecuteReader();

                while (reader.Read())
                {
                    HWL_Variance route = new HWL_Variance();
                    route.Route = Convert.ToInt32(reader["Route"]);
                    routeList.Add(route);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                connection.Close();
            }

            return routeList;
        }