Migration from SQL to Oracle

Migration from SQL to Oracle


Hello all,

I have a question about migrating an application from an environment that works on SQL database to an environment that works on Oracle DB. If we start building the application on SQL based environment, can we easily backup the app and restore to an environment that work on Oracle?



What do you exactly mean with "backup the app". The source code is stored in outsystems tables, just as the apps business data. If you migrate the complete outsystems database from SQL Server to Oracle, you won't loose anything.

After the migration, you might want to query your code and find for specific sql server statements in your advanced queries if you have any. We don't have very often specific db related commands in queries but it happens.

Hi Mehmit

I believe that your question can refer to two things (following Kurt's line), so I'll pick on both and comment on both.

The two things are:

  • Using an app developed for a SQL Server database in a Oracle database
  • Moving data from a SQL Server database to an Oracle database.

== Using an app developed for a SQL Server database in a Oracle database ==

To move an application from an environment to the other, it suffices to download the app from the source and publish it in the new environment. 

When you move an app between different stacks (be it the code - Java or .NET - or the database - SQL Server, Oracle or MySQL), most of the application behavior will remain the same.
The things to worry about are (top of mind):

  • Advanced queries / SQL nodes: the SQL syntax between the 3 database engines differs, so your queries may need to be altered. A classic example is the difference between SELECT TOP N .... [SQL Server] and SELECT * FROM (SELECT ....) WHERE ROWNUM < N [Oracle];

  • Transactional behavior may not be the same: some engines use READ UNCOMMITTED, others use READ COMMITTED. This is probably where you want to pay more attention in such migration.

  • Integrations with Extensions: if moving between Java and .NET or vice-versa, extension code will need to be re-written / added. Extensions provided by OutSystems have code in both stacks, but it was explicitly written by us - if you coded your own (or used Extensions from the Forge), this is something you will need to worry about.

== Moving data from a SQL Server database to an Oracle database ==

This is the part that is not trivial. The models OutSystems creates in SQL Server, Oracle and MySQL are very similar, but not equal. A blind migration between one and another will not work correctly. Simple examples are the way an empty string is stored in the database and the maximum length of object names in Oracle.

As such, OutSystems does not document the process and advises customers against performing the said operation on their own - the operation is not supported, we will not be able to assist and customer may run into issues in which OutSystems may not be able to help. 

If data move is needed, using the platform, it is possible to implement logic to move the data (e.g. via web services) from one to the other; but it should include doing all the data writing using platform primitives. Any other approach is not feasible.