You are currently browsing the monthly archive for January 2011.

Now and again we all come across situations where we need to save multiple records to a database, and the easy way is to set up a loop and insert the records one at a time. However there is a way to insert multiple records at once  using ODP.NET (also works for DDTek). It involves creating arrays for the parameters where you would normally have single values, and setting the ArrayBindCount of the command object to the number of elements in the array. Sample code:


public bool QueueEmail(EmailQueueItemCollection coll)
{
  bool retVal = false;
  OracleCommand cmd = new OracleCommand("CAEProc.CAE_FOF_EMAILQUEUE_PKG.QueueItem", conn);
  cmd.CommandType = CommandType.StoredProcedure;

  string[] emails = new string[coll.Count];
  string[] userids = new string[coll.Count];
  int[] emaildefs = new int[coll.Count];
  long[] secids = new long[coll.Count];

  for (int i = 0; i < coll.Count; i++ )
  {
    emails[i] = coll[i].EmailAddress;
    userids[i] = coll[i].UserId;
    emaildefs[i] = 1;
    secids[i] = coll[i].SecID;
  }

  //set the number of elements we're passing in
  cmd.ArrayBindCount = coll.Count;

  OracleParameter p_emailAddress = new OracleParameter("p_emailAddress_c", OracleDbType.VarChar);
  p_emailAddress.Direction = ParameterDirection.Input;
  p_emailAddress.Value = emails;
  cmd.Parameters.Add(p_emailAddress);

  OracleParameter p_userId = new OracleParameter("p_user_id_c", OracleDbType.VarChar);
  p_userId.Direction = ParameterDirection.Input;
  p_userId.Value = userids;
  cmd.Parameters.Add(p_userId);

  OracleParameter p_email_def_id = new OracleParameter("p_email_def_id", OracleDbType.Number);
  p_email_def_id.Direction = ParameterDirection.Input;
  p_email_def_id.Value = emaildefs;
  cmd.Parameters.Add(p_email_def_id);

  OracleParameter p_SECID = new OracleParameter("p_cae_sec_id", OracleDbType.Number);
  p_SECID.Direction = ParameterDirection.Input;
  p_SECID.Value = secids;
  cmd.Parameters.Add(p_SECID);

  OracleParameter p_result_n = new OracleParameter("p_result_n", OracleDbType.Int32);
  p_result_n.Direction = ParameterDirection.Output;
  cmd.Parameters.Add(p_result_n);

  conn.Open();

  // Make the Command object a SelectCommand for DataAdapter
  try
  {
    int recordsInserted = cmd.ExecuteNonQuery();
    int tmpInt = 0;
    retVal = (recordsInserted > 0);
  }
  catch (Exception ex)
  {
    LoggingHelper.Log(ex, "Exception when adding emails to queue");
  }
  finally
  {
    conn.Close();
  }

  return retVal;
}

Advertisements

I’ve been working on a multi tier WCF project for the past few months. One of the things we have to do as part of the saving and retrieving of data is translate a data contract to a business entity which can be used by the business tier and vice versa. To do this I created static translation classes for each entity to translate between the types, here’s a basic sample:


public static class SecurityTranslator
{
  public static Business.Security Translate(DataContract.Security from)
  {
    Business.Security to = new Business.Security();
    to.SecID = from.SecID;
    to.SecName = from.SecName;
    to.SecDesc = from.SecDesc;

    return to;
  }

  public static DataContract.Security Translate(Business.Security from)
  {
    DataContract.Security to = new DataContract.Security();
    to.SecID = from.SecID;
    to.SecName = from.SecName;
    to.SecDesc = from.SecDesc;

    return to;
  }
}

The Oracle tool of choice where I currently work is SQL Developer. While it’s good in a lot of ways it’s difficult to output the results of a proc that returns a sys_refcursor. I finally managed to do it after a couple of hours tweaking. It involves creating an output record structure that matches exactly the column structure of the records being returned. Here’s a cut down version of my proc:


open p_fof_sec_refcur for

  SELECT *
  FROM(
    SELECT securities.*, rownum rnum, v_total_count
    FROM
    (
      SELECT
      CFS.CAE_SEC_ID,
      CFS.FM_SEC_CODE,
      ...
      FROM
      CAEDBO.CAE_FOF_SECURITY CFS
      INNER JOIN caedbo.CAE_DATA_SET_ELEMENT CDSE_STAT
      ON (CDSE_STAT.DATA_SET_ELEMENT_ID = CFS.APPR_STATUS)
      ...
      WHERE APPR_STATUS = NVL(p_appr_status, APPR_STATUS)
      ...
    ) securities
  )
  WHERE rnum between v_pgStart and v_pgEnd;

In SQL Developer when viewing the proc, right click and choose ‘Run’ or select Ctrl+F11 to bring up the Run PL/SQL window. This creates a template with the input and output params which you need to modify. The proc returns a sys_refcursor and the tricky part for me was declaring a row type that is exactly equivalent to the select stmt / sys_refcursor being returned by the proc.


  DECLARE
  P_CAE_SEC_ID_N NUMBER;
  P_FM_SEC_CODE_C VARCHAR2(200);
  P_PAGE_INDEX NUMBER;
  P_PAGE_SIZE NUMBER;
  v_Return sys_refcursor;
  type t_row is record (CAE_SEC_ID NUMBER,FM_SEC_CODE VARCHAR2(7),
    rownum number, v_total_count number);
  v_rec t_row;

  BEGIN
  P_CAE_SEC_ID_N := NULL;
  P_FM_SEC_CODE_C := NULL;
  P_PAGE_INDEX := 0;
  P_PAGE_SIZE := 25;

  CAE_FOF_SECURITY_PKG.GET_LIST_FOF_SECURITY(
  P_CAE_SEC_ID_N => P_CAE_SEC_ID_N,
  P_FM_SEC_CODE_C => P_FM_SEC_CODE_C,
  P_PAGE_INDEX => P_PAGE_INDEX,
  P_PAGE_SIZE => P_PAGE_SIZE,
  P_FOF_SEC_REFCUR => v_Return
  );

  loop
    fetch v_Return into v_rec;
    exit when v_Return%notfound;
    DBMS_OUTPUT.PUT_LINE('sec_id = ' || v_rec.CAE_SEC_ID
      || 'sec code = ' ||v_rec.FM_SEC_CODE);
  end loop;

  END;

%d bloggers like this: