SQLServerCentral Article

SQL Server Adventures for the Oracle DBA



I became a COBOL programmer after I graduated from college. In 1995 I joined a software company selling software to college. The software had two versions, one was written in COBOL using VSAM files or DB2, the other one was using Oracle and was written using Oracle forms, PL/SQL and Pro C. In the beginning I was writing COBOL programs, but later on I started to learn Oracle and started to write PL/SQL and Pro C. I did not go to any training class. I learnt it by reading books and looking at existing programs. That was how I became a database programmer.

After I worked for the company for a few years, it had an organizational change. The new vice president got rid of my department and moved each of us to different departments. Most of us were not happy, and all of us except two left the company within six months, including me. At that time I was using Oracle 8i.


I found a job in a market research company using SQL Server 7.0. At that time SQL Server was just starting to get popular so not too many people knew SQL Server and T-SQL. When I interviewed the job, I told the manager I did not know SQL Server. But both of us agreed SQL was SQL, there would be no difference between Oracle and SQL Server. I would have no problem learning SQL Server and writing T-SQL.

Both the manager and I were naive or we both did not understand how big the difference was between both databases. She knew SQL Server but not Oracle, and I knew Oracle but not SQL Server. Yes, SQL is SQL but Oracle SQL is not SQL Server SQL.

SQL Server has Query Analyzer and Enterprise Manager. Oracle at that time did not have any GUI interface itself. Most of the people had to write PL/SQL using Notepad and compile it using SQL*Plus. At least until someone was sick of this; he wrote a freeware program called "TOAD" (Tool for Oracle Application Developer). That saved a lot of time for Oracle developers (God Bless him!). Now many companies offer an Oracle GUI interface, but none can match up to TOAD. Unfortunately Quest bought TOAD, so it is not free anymore. When I went to work the first day in the new company, the first thing I had to learn was how to use Query Analyzer and Enterprise Manager.

Also I found out there are a lot of commands and that the syntax was different, or one database has a command but the other one does not have the same command. For example to check if the field is null, SQL Server command is "ISNULL" and Oracle command is "NVL"; to extract a substring from a string, in SQL Server command is "SUBSTRING" and in Oracle is "SUBSTR". At that time SQL Server had the "CASE" statement, which was wonderful, but Oracle did not have that command. Instead it had a command "DECODE". Converting data is different as well; to convert data in SQL Server I used "CAST" or "CONVERT", but in Oracle it is "TO_DATE" and "TO_CHAR".

PL/SQL is totally different from T-SQL in many other ways. It is different when declaring variables. In PL/SQL using a cursor is common, but not in T-SQL. One SQL Server DBA yelled at me like I was an idiot for using a cursor. To do dynamic SQL in PL/SQL was painful, but it was so easy in T-SQL. It is much easier to do dynamic SQL in Oracle 10g.

Also the error handling is totally different. In PL/SQL a warning or an error condition is called "exception". Oracle has some internal exceptions, and the users can define user exceptions. There is no particular error handling in SQL Server. In PL/SQL, there are packages and in the package, there are public variables, stored procedures, subprograms. The advantage of package is kind of like modulalization, making it easier to design and understand the applications. But there is no package in SQL Server. In Oracle 8i, there is a way to define single dimension array, but in SQL Server 7.0 there is no array processing, though in SQL 2000 there is array processing.

The people in market research company where I worked were not helpful at all, as a matter of fact, they were rude and cruel. The first day I asked the DBA a question, he told me to look that up in Books Online and basically told me it was not his job to teach me programming. I did not even know what Books Online was at the time.

The Oracle DBA did not allow developers to create database, also it was the DBA's job to create, alter and delete tables, constraints, indexes, primary and foreign keys. The DBA also takes care of creating synonyms, sequence numbers, grant and revoke permissions. When I told the DBA in the new company that I wanted to create a table, he told me to do it myself. Then I found out I had to do everything what my old Oracle DBA did before. Before when I used Oracle, the DBA would take care of moving things from development to production. In SQL Server I had to script out everything to give to the DBA and he ran the script.

Then there were DTS packages. There was nothing liked this in Oracle. Learning how to use DTS package was another challenge, as was learning how to write VB script. I also had to learn how to create jobs. In Oracle, it is DBA's job to schedule to create and run the job.

I worked in that company for two years; not a single day went by that I was not yelled by the DBA or the manager. They thought my skills were terrible. I even thought I was terrible and my self-esteem sank to the bottom. One day I just could not take it anymore and quit the job.


It was a very painful two years. After that I took a contracting job to write SQL Server stored procedures and DTS Packages in a bank. Then I realized how much I had learnt over the last two years. My new boss and the DBA thought my SQL skills were excellent. The DBA was even amazed that I had so many DBA skills. I found out I really was doing a lot of DBA work in the other company.

The reason why I write this article is management always think there is no learning curve moving from Oracle to SQL Server or vice versa, but there is a learning curve for the developers. In case you hire a former Oracle developer to write SQL Server T-SQL and that person uses a lot of cursors, do not yell at that person; explains to them the difference. The problem is most of the time the company will not send an Oracle developer to a SQL Server training class, thinking that person already knows everything. I agree most of the SQL Server class is for beginners, and maybe someone should create a SQL Server class for people already have database background. On the other hand, the Oracle developer has to by motivated to learn SQL Server. The two platforms are different and it is a challenge to learn.


3 (2)




3 (2)