Lesson Learnt from Oracle - SQL Server

Moving to a new place could bring hope of learning something new. It did happen to me. I have to heavily work with both Oracle and SQL Server. The very first code I have to write was writing a stored procedure. Not my favorite at first but now I’m just OK with it. There are some notes I'd like to write here as a reference just in case I need them again in future.

Oracle Connect By

The first procedure was who-are-your-managers. There was a need to find out who are an employee’s managers, from direct manager all the way up to the top-level manager. To do that there are several steps need to be done.

Suppose we have a table named PERSON which contains information about employees and who is his/her direct manager. The following query is used to get non-top-level employee:

SELECT PK_PERSON,
            FK_PERSON_MGR
FROM (
    SELECT PK_PERSON,
                FK_PERSON_MGR
    FROM PERSON
    WHERE FK_PERSON_MGR IS NOT NULL AND IS_ACTIVE = 1
) NON_TOP_LEVEL_PERSONS
ORDER BY PK_PERSON

After knowing all non-top-level employees we can now use the following query to get all his/her managers:

SELECT ABOVE_QUERY.PK_PERSON,
            CONNECT_BY_ROOT ABOVE_QUERY.PK_PERSON,
            SYS_CONNECT_BY_PATH(ABOVE_QUERY.PK_PERSON,' > ')
FROM ABOVE_QUERY
START   WITH ABOVE_QUERY.PK_PERSON = ‘THE MANAGER IN QUESTION’
CONNECT BY PRIOR ABOVE_QUERY.PK_PERSON = ABOVE_QUERY.FK_PERSON_MGR
ORDER BY ABOVE_QUERY.PK_PERSON

The result is shown below:

There are four things to note from this query:

1. CONNECT BY PRIOR is used to connect the person primary key and person manager foreign key.
2. START WITH is used to define who is the root manager. The above result substituted ‘THE MANAGER IN QUESTION’ with PK_PERSON which means ‘anybody can be the manager so just find his/her employees’. This is equal to removing the START WITH clause.
3. CONNECT_BY_ROOT is used to get the root in the hierarchy. To be clear, see the result of the SYS_CONNECT_BY_PATH.
4. Last is the SYS_CONNECT_BY_PATH. It is used to display the hierarchy and very helpful if we need to debug.

SQL Server Linked Server

For some reason, the app I’ve been developing needs to pull data out of Oracle and then store it to SQL Server. We decided to use a SQL Server feature called Linked Server. The following picture shows it on SQL Server Management Studio 2008 R2:

Before we can create a Linked Server to Oracle, we need to make sure that the Oracle provider has been installed.

If it is not there yet, we can download the provider from Oracle website or, in my case; it is installed when I was installing Oracle XE.

After installing the provider, we need to make some changes of the provider’s properties, see the following images on how to do that:

Now we can move on to the steps required to create a new Linked Server:

1. Right click on the “Linked Servers” and choose “New Linked Server”




2. On the General tab, fill the following data on the form





The complete “Data source” is:
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = USER-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

3. On the Security tab, fill the following information on the form




4. After we click OK and there is no error thrown then we can test the connection by right clicking the previously created Linked Server.

SQL Server Linked Server OpenQuery

I had problems when writing a query to pull the data out of Oracle to SQL Server. Things like incompatible numeric decimal points and unrecognized Oracle objects (like synonym) are quite annoying. But fortunately there is this guy named OpenQuery that is able to send native Oracle query from SQL Server.

The following is an example of how to use the OpenQuery:


SELECT * FROM OPENQUERY(XE, 
         'SELECT BASE_DT,
                      COUNT,
                      DATA_CTG
          FROM SYS.MARPRO_PROMO_LOG')

The Open Query takes two arguments. First is the Linked Server and second is the query string. The query string is Oracle native SQL which means we can use Oracle clauses or functions.

However, as I said before, I had problem with numeric conversion. The TO_NUMBER comes to rescue the conversion as shown below:

SELECT * FROM OPENQUERY(XE, 

          'SELECT BASE_DT,
                       PROMO_CD,
                       CIF_NO,
                       GCIF_NO,
                       GCIF_CIF_NM,
                       TO_NUMBER(BAL_SA) AS BASE_BAL_SA,
                       TO_NUMBER(BAL_CA) AS BASE_BAL_CA,
                       TO_NUMBER(BAL_TD) AS BASE_BAL_TD
           FROM SYS.MARPRO_PROMO_ACT')

Insert/Update Many Rows

Most likely there is always be a need to insert or update many rows and we can achieve this by iterating cursor. However, it is not recommended because of performance degradation issue. Instead, we can use insert-into-table-select.

INSERT INTO table1
SELECT * FROM table2

The following query can be used to update many rows at once:

UPDATE GIFT_ORDER SET status_order = 'ME'
WHERE gift_order.pk_order IN (
           SELECT gift_order.pk_order
           FROM gift_order_old)

Oracle Over Partition By

I have a table named DELINQ_CASE to store cases data, including the case owner. The next procedure I had to write was to make prioritization of the cases. To do that, the cases should be queried and ordered by WORK_ON_DATE, STATUS_GROUP, and CURRENT_BAL_DESC columns. Then we can select ROW_NUMBER() to be the priority number since the rows are already ordered. However, because the prioritization should be done for each case owner, the query must be run many times. Oracle has a better way to achieve the same result.


SELECT ROW_NUMBER() OVER (PARTITION BY DELINQ_CASE.FK_PERSON_F_COORD_CLR
                          ORDER BY TRUNC (WORK_ON_DATE) ASC,
                                   STATUS_GROUP ASC,
                                   CURRENT_BAL DESC) AS PRIORITY,
       DELINQ_CASE.PK_DELINQ_CASE,
       DELINQ_CASE.FK_PERSON_F_COORD_CLR,
       DELINQ_CASE.WORK_ON_DATE,
       ACTION_CODE.STATUS_GROUP,
       DUE_AMOUNT_CURRENT.CURRENT_BAL
FROM DELINQ_CASE
INNER JOIN ACTION_CODE
        ON ACTION_CODE.PK_ACTION_CODE = DELINQ_CASE.FK_ACTION_CODE_LATEST
INNER JOIN DUE_AMOUNT_CURRENT
        ON DUE_AMOUNT_CURRENT.FK_CONTRACT = DELINQ_CASE.FK_CONTRACT

• The query above is used to select data from tables DELINQ_CASE, ACTION_CODE, and DUE_AMOUNT_CURRENT. Data from those tables are used to display information about cases.
• The ROW_NUMBER() is used as the priority number.
• Instead of ordering the query by WORK_ON_DATE, STATUS_GROUP, and CURRENT_BAL_DESC columns; and then repeat the query for each case owner, we can use Oracle’s OVER PARTITION ORDER. Here we moved the ordering inside OVER clause and then the result is partitioned by the case owner.

Oracle Update from Select

Oracle offers another way to update data from a sub-query. In UPDATE clause, we can replace the table with a sub-query, with some note though: the sub query must contain a primary key; and if it is in a JOIN statement, the first table must contain primary key and second table must contain foreign key of first table.

UPDATE (
SELECT DELINQ_CASE.PRIORITY AS DC_PRIORITY,
             PRIORITIZATION_SCHEDULING.PRIORITY AS PS_PRIORITY,
             DELINQ_CASE.SUGGEST_WORK_ON_DATE AS DC_SWOD,
             PRIORITIZATION_SCHEDULING.SUGGEST_WORK_ON_DATE AS PS_SWOD
      FROM DELINQ_CASE
           INNER JOIN PRIORITIZATION_SCHEDULING
              ON DELINQ_CASE.PK_DELINQ_CASE = PRIORITIZATION_SCHEDULING.FK_DC
      )
SET DC_PRIORITY = PS_PRIORITY,
        DC_SWOD = PS_SWOD;

In the query above I tried to update two columns from a sub-query. The DELINQ_CASE table was joined to PRIORITIZATION_SCHEDULING table on their PK and FK. Since the sub-query contains a primary key, in this case primary key from DELINQ_CASE.PK_DELINQ_CASE, we can update any column on it. In my case I need to update two columns of DELINQ_CASE with two columns of table PRIORITIZATION_SCHEDULING.

The objectlabkit Comes to Calculate Working Days

There was this requirement for my project to calculate delivery date estimation. The delivery date is estimated to be 40 working days, in this case I have to exclude Saturday-Sunday and any holidays.

Fortunately there is a library from sourceforge named objectlabkit which can be found here. The latest version was 1.2.0 and I would like to share here how it helped me out:

Create a set of holidays. This is a list of holidays to be excluded from working days.

Calendar firstHoliday = Calendar.getInstance();
firstHoliday.set(Calendar.DAY_OF_MONTH, 18);
firstHoliday.set(Calendar.MONTH, Calendar.JANUARY);
...
Set<calendar> holidays = new HashSet<calendar>();
holidays.add(firstHoliday);
holidays.add(secondHoliday);

Create start and end calendar as a boundary.

In some condition, the holidays are supplied by a service which we can not control and the list could contain holidays from 1990 to 2020 while we are only interested in holidays in 2012. That is why we need the start and end calendar as a boundary.

Calendar earlyBoundary = Calendar.getInstance();
earlyBoundary.set(Calendar.DAY_OF_MONTH, 1);
earlyBoundary.set(Calendar.MONTH, Calendar.JANUARY);

Calendar lateBoundary = Calendar.getInstance();
lateBoundary.set(Calendar.DAY_OF_MONTH, 31);
lateBoundary.set(Calendar.MONTH, Calendar.DECEMBER);

Create an object named HolidayCalendar and register it to CalendarKitCalculatorsFactory.

After creating the boundaries, we have to register the holiday list and the boundaries to an object of type CalendarKitCalculatorsFactory. We need to supply an id when registering to the object so that we can request a DateCalculator object from it later.

HolidayCalendar<calendar> holidayCalendar = new DefaultHolidayCalendar<calendar>(holidays, earlyBoundary, lateBoundary);
CalendarKitCalculatorsFactory.getDefaultInstance().registerHolidays("ID", holidayCalendar);

Get DateCalculator object from CalendarKitCalculatorsFactory by supplying the previously registered id.

The only thing to note here is the HolidayHandlerType constant. In this example I use FORWARD because when the estimated delivery date falls on either Saturday-Sunday or holidays I want the estimated delivery date to be the next available working day.

DateCalculator<calendar> dateCalculator = CalendarKitCalculatorsFactory.getDefaultInstance().getDateCalculator("ID", HolidayHandlerType.FORWARD);

Set the start date.

We need to set start date when the calculation must begin.

Calendar startDate = Calendar.getInstance();
dateCalculator.setStartDate(startDate);

Set the span of how many working days the estimation is supposed to be calculated which in my case is 40 working days.

dateCalculator.moveByBusinessDays(40);

 To get the result use getCurrentBusinessDate() method.

dateCalculator.getCurrentBusinessDate();