0

I work in a project where the UI has direct access to the database through SQL code. The company has a framework where we create UI pages in xml and after that it is parsed and creates FLEX pages. If we want some data from the DB (Oracle) we add a sql query in the xml (instead of databinding with a datacontext object like we could do with WPF). If we want to add some logic, there is no code behind, we call store procedures. After we have the data we need the parser does the job.

The new requirements are to use the framework and create a new product that will be compatible with SQL Server and the thoughts are to start transforming the (Oracle)SQL queries to ANSI SQL.

Can somebody tell me the benefits and mainly the problems that we are going to face doing that? Do you think there is a better way? Note: The framework is really big and there are a lot of products built on that so managers are not keen to just throw it away(I tried but.. :))

Alexandros Alexandrakis
  • 13
  • 2

1 Answers1

0

Each dialect of SQL is different. You could use ANSI SQL but a) not all of ANSI SQL is implemented by most DBMS and b) most DBMS's have implementation-specific optimisations which will perform better for some cases.

So I'd say, don't go for ANSI SQL. It won't always work and sometimes it will work slower than taking advantage of a vendor's non-standard implementations.

Specifically, Oracle requires a StoredProcedure to return a REF_CURSOR from a stored procedure to fill a DataSet. SQL Server doesnt; the SP returns what the sp SELECTed. You're going to have to change your SP's to get rid of the returned REF_CURSOR.

Date handling is quite different: Oracle needs a to_date to turn a string into a date in where clauses etc; SQL Server just takes the string and converts it for you. And so on and so on. (I'm not at all sure what the ANSI Standard is, or even if it covers this!) To avoid changing your SQL you could add create SQL Server function called to_date, but this is now going to slow up your SQL.

If you have much PL/SQL in stored procedures, you have a big job converting it to T-SQL. They are quite different.

Good luck!

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
simon at rcl
  • 7,326
  • 1
  • 17
  • 24
  • 1
    Agree with all of that - it's a very difficult task. However on the ref_cursor thing I believe that Oracle 12c has a new syntax that avoids the issue and is similar to SQL Server's treatment. – David Aldridge Jan 24 '14 at 13:19
  • Actually, I forgot to mention that SQL Server has a tool called SQL Server Migration Assistant (SSMA) which does a reasonable job of converting an Oracle db (PLSQL and all) to SQL Server. But you'll still need to do quite a lot of work by hand to get maintainable and performant code. – simon at rcl Jan 24 '14 at 15:36