Problems with Oracle Migration Workbench

Database vendors like to bash each other, sponsoring dubious “objective industry studies” to “prove” they are better than others. All of them do it. In my opinion, Oracle is particularly bad in this regard, compared against IBM DB2 or Microsoft Sql Server. Talking about “unbreakable Oracle” and software full of bugs, and in many cases you need to pay the Metalink membership to simply get to know that the issue you are dealing with is a bug. That is not to say Sql Server and DB2 don’t have bugs in their software, though.

Each of them wants you to convert your database to their platform. To that end, they provide some program to help you along. Oracle has a thing called Oracle Migration Workbench. Sql Server has something similar called Sql Server Migration Assistant.

More often than not, marketing people from those companies will tell you how great their migration program is. That it is easy to migrate, you will see performance improvement, etc. Don’t believe them.

A couple of months ago, I worked on a project to migrate a database from Sql Server to Oracle. This database has a lot of stored procedures, user defined functions, and linked server stuff. I talked to Oracle people, and they recommended their own Migration Workbench, with Sql Server plug-in. I downloaded them and started working.

Basically, the Migration Workbench tries to go through database code in T-Sql. For code that uses built-in T-Sql functions, the Migration Workbench creates functions with the same name in Oracle, and try to writes something similar in PL/Sql that makes an attempt to do what T-Sql function does. I didn’t look too deep into this, but I am suspicious at all of them. The reason I didn’t look too deep into them was that the whole effort was derailed by a bigger problem. Let me explain.

In T-Sql, all variable names start with @ symbol. In fact, in many places, variable names are simply column names prefixed with the @ symbol. This can be pretty easy to read. And it actually works very well.

However, PL/Sql variable names don’t follow that convention. So, as Migration Workbench goes through T-Sql stored procedures, it simply strips off the @ symbol from the variable names. This basically renders all the code useless.

This is just very, very dumb. Oracle is a multi-billion dollar company, and you would think they should know this. Instead of stripping off the @ symbol, it could replace it with some kind of prefix. But it does not do that.

Another problem is with the identity field. Once again, one would expect that the Migration Workbench converts it to Oracle sequence, but it doesn’t. It changes that to NUMBER.

Eventually, the project was canceled. The moral of the story: migration from one RDBMS to another is not as easy as it sounds. It is doable. Sometimes it is probably easier to just write everything from scratch.

Monitoring error logs in Oracle and Sql Server

In Oracle, there are 3 places that I know of that are important for monitoring: the bdump, where background process error is stored; udump, where user trace error is dumped; and cdump, the core dump, where Oracle internal error is dumped. cdump is in the binary format, you can use “strings -a” to look at things inside. All other trace and log files are text files that you can open up and read for yourself.

On Unix/Linux/Solaris based systems, these folders are located under:

$ORACLE_BASE/admin/$ORACLE_SID/bdump, udump, and cdump

To find out your $ORACLE_BASE and $ORACLE_SID, you can do:

echo $ORACLE_BASE and echo $ORACLE_SID

There is also the alert log, usually in the format of alert_$ORACLE_SID.log format, under the bdump folder.

There can be numerous trace and log files in these folders. To troubleshoot and correlate events with some problems, you can do:

ls -ltr

to sort files in ascending order according to date and time, and open up the file that is closest to the time when problem occurred. Those trace and log files may hold telltale signs of what you need to examine further.

For Sql Server, by default, the error log file is at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG. And that is the only place you need to worry about. If you turn on certain trace, the trace info will also be logged into error log.

Don’t confuse Oracle’s .trc file with Sql Server’s .trc file. Oracle’s trace file is in text format, generated when certain trace flags are turned on. Sql Server’s trace file are in binary format, and can be read using Sql Server Profiler.

Assign SELECT results into variables

I mentioned here that SELECT INTO in Sql Server is functionally similar to CREATE TABLE AS in Oracle. Oracle also has SELECT INTO, but it is used for assigning query results to a variable.

Here is a PL/SQL code snippet:

declare MyVariable varchar2(20);

Begin

select ColumnName into MyVariable from MyTable where MyID = SomeInteger;
dbms_output.put_line('Hello ' || MyVariable);

End

In the above example, a column value for a particular record is assigned to MyVariable and printed out.

How do you assign select results into T-Sql variables in Sql Server then? Here is a code sample that does the same thing above:

declare @MyVariable varchar(20)

select @MyVariable = ColumnName from MyTable where MyId = SomeInteger

print 'Hello ' + @MyVariable

Delete permission implementation differences

I mentioned when grant statements take into effect in Sql Server, MySql, and Oracle here.

I found out recently that there are some implementation differences when you grant only delete permission on a table to a user. MySql and Sql Server do this the same way, whereas Oracle is different.

Suppose you have:

1. Table t1: create table t1 (c1 int);
2. User TestLogin. The only permission of this TestLogin is delete on t1.

In all 3 database platforms, TestLogin can find out what columns t1 has by default, using either

desc t1

or

sp_columns t1

In both Sql Server and MySql, the only thing you can do is:

delete from t1;

which essentially wipes out the whole table. You can do the same thing in Oracle.

However, if you do:

delete from t1 where c1 = 1;

you will get a select permission denied in both Sql Server and MySql, but Oracle will allow you to do it.

Personally, I think Oracle’s implementation is wrong on this one, because this gives TestLogin select permissions on this table. For example, suppose the table is a salary table, TestLogin can find out columns using desc, then it can do something like:

delete from SalaryTable where FirstName = ‘John’ and LastName = ‘Doe’ and SalaryAmount >= 50000 and SalaryAmount <= 50050; If the statement returns "one row affected", then the person would know John Doe's salary. This person can then issue rollback If "no row affected" is returned, the person can continue until s/he can find it out. I did a quick search on ANSI SQL 92 standard, but didn't find anything, so I am not sure which way is consistent with ANSI 92. My check was brief, though, so I may have overlooked it.