Conversion of Oracle schemas with hierarchical queries to SQL Server
One of our clients, prominent information technology company, needed to migrate its Oracle database to SQL Server so they called ABCloudz. They knew they wanted to automate the major part of the migration with SQL Server Migration Assistant (SSMA) but there were some challenges associated with this process. First, the company’s source database code contained over 400 hierarchical queries that could not be converted with SSMA. And second, the company’s technical staff was also unable to convert such queries.
By reaching out to ABCloudz specialists to help them convert Oracle database schemas, we were able to get them on the right track with the right level of expertise. We also managed to solve the problem within a short period of time. You can find more information on this project and our approach to converting hierarchical queries from Oracle to SQL Server in the video below.
The state of the customer’s original system
From the start, there were some obstacles to address with the state of their current system. Before engaging ABCloudz and partnering with us to achieve the migration, the customer had been using an Oracle database containing a significant number of schemas. This meant the client had already started migrating to SQL Server before contacting ABCloudz. To add to the urgency, there was a pressing deadline for migration since the client’s Oracle license was about to expire.
The customer started their database migration by successfully using the SQL Server Migration Assistant. This helped them automate database schema conversion but they faced some issues at this stage. Namely, some of the original schemas contained hierarchical queries with the NOCYCLE clause. You can see the example of such a clause in the image below.
1 2 3 4 5 | SELECT rt.id, parent_id FROM TEST_ORACLE_MSSQL.T_HIERARCHICAL_QUERIES rt CONNECT BY NOCYCLE PRIOR id = parent_id; |
The issue with SQL Server conversion
When you build a hierarchical tree, there may be situations when a row links to one of its parents or to itself as a child. This can be frustrating because it creates an endless loop in your code. That was exactly the case with rows in the client’s data. And because this issue was not identifiable on the query code level, we had to review the client’s data level to detect the problem.
Oracle’s NOCYCLE clause enables you to avoid the issue with an endless loop occurring during the execution of the CONNECT BY query condition.
Meanwhile, SQL Server introduces recursive queries as an alternative to Oracle’s hierarchical queries. The image below illustrates them.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | WITH h$cte AS ( SELECT rt.ID, rt.PARENT_ID, 1 AS LEVEL, CAST(ROW_NUMBER() OVER( ORDER BY @@spid) AS VARCHAR(MAX)) AS path FROM TEST_ORACLE_MSSQL.T_HIERARCHICAL_QUERIES AS rt UNION ALL SELECT rt.ID, rt.PARENT_ID, h$cte.LEVEL + 1 AS LEVEL, path + ',' + CAST(ROW_NUMBER() OVER( ORDER BY @@spid) AS VARCHAR(MAX)) AS path FROM TEST_ORACLE_MSSQL.T_HIERARCHICAL_QUERIES AS rt, h$cte WHERE h$cte.ID = rt.PARENT_ID ) SELECT h$cte.ID, h$cte.PARENT_ID FROM h$cte ORDER BY h$cte.path GO |
Oracle is a bit quirky in this aspect, as these recursive queries do not detect the above-mentioned loops automatically. As a result, SSMA failed to convert the queries and returned the error message ‘The following SQL clause cannot be converted.’
Already deep into the migration when these problems occurred, the client faced issues with converting the queries manually. That was the point where they asked ABCloudz database migration experts for help.
We examined the client’s schemas and began developing the right strategy for converting the aforementioned 400 hierarchical queries to get them on track and meet their deadline.
Conversion of hierarchical queries
We started by developing an algorithm for real-time loop detection. This algorithm was placed into a recursive procedure allowing for emulation of the NOCYCLE clause peculiar to Oracle. Each time we executed the converted recursive query in SQL Server, we executed this procedure as well.
This innovative approach allowed us to manually convert Oracle’s hierarchical queries to recursive queries of SQL Server. After that, we updated the query code so it supported the results returning from the procedure detecting the loops.
These steps preserved the functionality of the client’s original solution in the new database environment. And the database migration was completed before the expiration of the client’s Oracle license. This allowed ABCloudz to fulfill all the client’s needs in this project in the time it needed them done.
Value delivered
To provide them with ongoing value, we converted the database schema and set up a tool that automates and verifies data migration. Overall, the client received the following benefits:
- The finished conversion of 400 hierarchical queries that could not be converted automatically.
- Complex migration project finished within the pressing deadline.
- High-performance indicators of the client’s software working in the new environment.
This project allowed us to optimize and customize our ready-to-use methodology of converting Oracle hierarchical queries to SQL Server recursive queries. We are ready to share our expertise with you or facilitate your database migration project. Contact us for more information or for help with your database migration.
