There are a lot of differences between Oracle and MS SQL Server and you will face many of them trying to move your database from one platform to another. If you use SSMA, which is a good thing to do, you can avoid huge amount of manual work, but you will have to solve some specific problems by yourself after SSMA job will be done. One of them can be raising errors from UDF, which you can easily do in Oracle and can’t in SQL Server. Let’s discover the workarounds to raise an exception in SQL Server User Defined Functions because of T-SQL limitations for UDF.
So let’s create some pretty simple UDF in Oracle and take a look on how we can keep its full functionality in MS SQL.
Oracle:
RETURN NUMBER IS
BEGIN
IF (b=0) THEN
RAISE_APPLICATION_ERROR(-20002, 'You cannot divide by zero!');
END IF;
RETURN a/b;
END DIVIDE;
If you try to rewrite it in T-SQL just with obvious syntax and data types changes like that:
RETURNS float(53)
BEGIN
IF (@b = 0)
RAISERROR(59998, 16, 1, 'You cannot divide by zero!')
RETURN CAST(@a AS float(53)) / @b
END
you will get an error:
Msg 443, Level 16, State 14, Procedure DIVIDE, Line 5
Invalid use of a side-effecting operator ‘RAISERROR’ within a function.
Let’s see what SSMA does to avoid this (and many other) T-SQL restrictions for UDF. If you are new to SSMA you can start with this presentation by our CEO Dmitry Balin, which describes the main idea of migration process. It also should be enough to make first steps with SSMA.
So, first of all SSMA creates “sysdb” database with collection of auxiliary objects (like tables, SPs, UDFs etc). Part of them is just emulating Oracle build-in functionality and the other helps to deal with more complicated situations.
You can’t raise errors from UDF, but you can do that from a stored procedure, so SSMA creates one:
@a int,
@b int,
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@return_value_argument float(53) OUTPUT
AS
/*Generated by SQL Server Migration Assistant for Oracle version 5.2.1259.*/
BEGIN
IF (@b = 0)
BEGIN
DECLARE
@db_raise_application_error_message nvarchar(4000)
SET @db_raise_application_error_message = N'ORA' + CAST(-20002 AS nvarchar) + N': ' + N'You cannot divide by zero!'
RAISERROR(59998, 16, 1, @db_raise_application_error_message)
END
SET @return_value_argument = CAST(@a AS float(53)) / @b
RETURN /*EXCEPTION*/
END
It contains all the logic from the original UDF and next problem here is that you can’t invoke stored procedure from UDF. But you can execute an extended stored procedure and call regular stored procedure from it. “Sysdb” includes “xp_ora2ms_exec2_ex” extended stored procedure which is just a wrapper for calling regular stored procedures from UDF (you can read about this and other migration troubleshooting in SSMA White Paper).
So, the whole thing here is that we create stored procedure where the logic is implemented, wrap it with extended stored procedure and call the last from UDF. It looks like obvious overhead, causes performance issues and… doesn’t work. You can’t receive exception from extended SP and if you run the following query
it will return null.
This trick with extended procedure is good when you need to emulate other Oracle functionality which is forbidden in T-SQL, but it doesn’t work if you just need to raise error from UDF. So what is the solution?
The most known trick here is to force UDF fail with system exception:
RETURNS float(53)
BEGIN
IF (@b = 0)
return cast('You cannot divide by zero!' as int)
RETURN CAST(@a AS float(53)) / @b
END
It looks much less complicated than previous solution and it really works! Now if you run
again you will get result message like bellow:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘You cannot divide by zero!’ to data type int.
Well, it doesn’t look perfect, but it’s definitely better than nothing. But what if for some reasons you have to specify error severity and state? There is another much more refined way to accomplish that. It’s similar to the first solution but now we will use OPENQUERY instead of extended procedure.
First of all, let’s create loopback linked server:
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'Your server name',
@catalog = N'master'
After that we need a stored procedure (in difference from the first solution you have to create only one SP for raising errors from all your UDFs):
@errorstr varchar(max),
@errorsvr int = 11,
@errorstate int = 1
as
set nocount on
raiserror(@errorstr,@errorsvr,@errorstate);
select null as result;
And finally here is our “divide” function:
RETURNS float(53)
BEGIN
declare @fake_for_error int
IF (@b = 0)
select @fake_for_error = 0 from openquery(loopback, 'EXEC [Your Database Name].dbo.raise_error @errorstr = ''You cannot divide by zero'', @errorsvr = 18, @errorstate = 3')
RETURN CAST(@a AS float(53)) / @b
END
And if you run select again, you will get clear error message with custom severity and state values:
Msg 50000, Level 11, State 3, Line 1
You cannot divide by zero
But what about performance? Well, calling stored procedure through the linked server is a little overhead and if performance is critical you should use “cast message to int” trick instead. But in most cases query time will increase almost insensibly, so if you want to get a clean error message with custom state and severity levels you can use the loopback linked server version.
