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.