Migrating SQL Server to Oracle – .NET Perspective
April 18, 2010
Posted by on
Some time back I had written a post on adding parameters to Oracle Stored Procedure. Extending that in this post I will try to outline the challenges to be met for converting a data access layer code (.NET side) for SQL Server to one for Oracle. Hope you find it useful.
1) Multiple Result Sets – Oracle expects a REF CURSOR parameter to be passed for every result set that a Stored Procedure is returning which isn’t required by MSSQL Server. So you would have to create API’s that take number of REF CURSOR’s required and in turn add those. You would even a need a REFCURSOR for retriving scalar values or executing datareaders.
2) Order Of Parameters – As I had posted earlier Oracle expects that parameters should be added in the same order as they are specified in Stored Procedure. To deal with this issue you need to set the property BindByName of OracleCommand class to ‘true’.
3) Parameter Prefix – Oracle doesn’t allow the parameter names specified in Stored Procedure to start with ‘@’. So when you try migrating the Stored Procs of SQL Server to Oracle with help of tools like SwisSQL, Oracle Migration Workbench, etc. they convert that ‘@’ prefix to something else like ‘v_’. Now this would affect your calling code to stored procedures. You would have to replace that ‘@’ with ‘v_’.
4) Handling Error Numbers – Many a times we write database code specific to error numbers of underlying DB. E.g. You may want to re-execute the query in case of a deadlock. Now the issue you would run into is the Deadlock no. of SQL Server is 1205 while for Oracle it’s 60 (N.B. you can catch both of them via SQLException & OracleException – Number property). A way to tackle this is to introduce generic errors in form of Enum inside your Data Access Layer.
5) Date Formats – While this may vary, Oracle expects dd-MMM-yyyy and SQL Server is fine with dd-mm-yyyy. Most of us, pick the date value from a textbox and try to format it. But what is easy for us is to just convert the textbox’s text to a DateTime object and add it to the parameter collection of the underlying command (SqlCommand / OracleCommand). Underlying provider then takes care of format conversion for us.
6) Return Parameters – Although this is not completely from .NET perspective, in SQL Server you don’t have to specify the return parameter at Stored Procedure level. You can add parameter (ParameterDirection.ReturnValue) in your .NET code & retrieve the value on completion of stored procedure. But in Oracle the every parameter you add at .NET level there has to be an equivalent at Stored Procedure level.
I am not sure if this is a comprehensive list but hopefully it covers the most basic ones. Do drop a comment below helping me make this list complete. Just remember this are the changes from .NET side. I will outline changes at database level in another post.