Are you planning to migrate your business data from SQL server database to Oracle database? Are you worried about the challenges that will be faced during the database migration?
Let's see some of the key challenges faced during database objects migration from SQL server to Oracle database using SQL developer 4.0 version.
The total length allowed for a database object name in Oracle is 30 characters. Those that are more than 30 characters are truncated and appended with _numbers.Example: prcinsertemployeetableandpopulate is converted to prcinsertemployeetableandpop_1 in Oracle during database migration.Based on the requirements, it has to be decided either to retain the name generated by the tool or to convert it to a meaningful name and change it in all the referencing objects.
The global temporary table created inside a stored procedure in SQL server is moved outside the stored procedure in Oracle during conversion. This absence creates error and has to be recreated every time as it exists outside the scope of the procedure. The possible solution in Oracle would be to store the select result into a cursor for processing instead of temporary tables as in SQL server.
SQL developer tool converts SQL server data type into compatible Oracle data type as in below table.
A varchar(max) data type which gets converted into varchar2 in Oracle will be an incorrect data. The possible solution would be to understand the maximum length of data and define a rule for varchar(max) data type during the migration so that it gets converted automatically to a defined value like varchar2(200) instead of varchar2 which creates compilation error.
Oracle does not allow stored procedure to return expression but SQL server allows returning expression from stored procedure. So the migration tool will convert stored procedure into a function. It is necessary to find out such incorrect object conversion, manually convert it to store procedure and change the logic to use OUT instead of return statement.
When a table is migrated from SQL server to Oracle, the index created on constraints is deleted. While we create a primary key in a table, index is automatically created on the primary key column, but when the tool migrate such tables it retains only the Primary key and drops the index on the primary key.
SQL server allow to refer more than two tables in UPDATE statement but Oracle allows only one. Hence when converting from SQL Server to Oracle, the code needs to be manually rewritten using join condition in UPDATE statement as the tool fails in converting such update queries.
Common table expression from SQL server does not have equivalent syntax in Oracle and tool fails to convert such queries as well. So we need to manually rewrite the query using sub queries or using Merge in Oracle.
We have discussed some of the challenges faced during database migration from SQL to Oracle above. Hope you find this article helpful to perform a successful database object migration through SQL developer 4.0 version.