Oct 29, 2018 1:28:07 PM by Clarisa Maman Orfali
We can easily create a data model of an existing database schema using the Toad Data Modeler Reverse Engineering wizard and then using the Convert Model Wizard to convert that model to another database platform.
For this demonstration I have a schema called quest in an Oracle 11g R2 database; below we can see its data model.
Figure 1. Data model for our example schema
Before using the wizard that will allow us to create the data model of the existing database, it’s very important to know which database systems are supported by the Reverse Engineering tool.
Figure 2. Reverse Engineering supported database systems
In this chart from the Toad Data Modeler User Guide, you can see all the databases that are supported by the tool.
Open Toad Data Modeler and select File Menu > Reverse Engineering >Reverse Engineering Wizard. (You can download a trial of Toad Data Modeler here.)
Figure 3. Selecting Reverse Engineering Wizard
The Reverse Engineering Wizard window opens.
Figure 4. Reverse Engineering Wizard - Connections
We click on the Next button.
Now, we need to select the Data Source. From the root node of the Database RE tree, we select the desired type of database RE; in this case, we select Oracle 11g Release 2.
Figure 5. Selecting the Data Source
We click on the Next button.
Select the Data Provider:
Figure 6. Selecting the Data Provider
Click on the Next button.
Select the Home and the Net Service Name. In addition, enter the user and password of the schema.
Figure 7. Specifying the Service Identification and Login Information
We click on the Next button.
In this window, we select what we want the Reverse Engineering to perform. We can use the Auto Check button to automatically select parent objects when any of their dependent objects are selected.
Figure 8. Selecting what to reverse
We click on the Next button.
In this window we configure settings used for Reverse Engineering.
Figure 9. Configuring the settings for reverse engineering
We click on the Next button.
In Save Connection, we can save the connection or move on with the wizard.
In this case, we are going to save the connection. To do that, we click on the Save button and then click on the OK button.
Figure 10. Saving the connection
We click on the Next button.
We select all objects. It’s important to mention that depending on what the database schema has, the tabs: Tables, Views, Procedures, Functions, etc., will be either displayed or not.
Once all the objects that we want to show in the data model have been selected, we click on the Execute button.
Figure 11. Selecting Tables
When the process of creating the model is finished, we can close the informative popup and then close the wizard window.
Figure 12. Notification message: Reverse Engineering is finished. Click OK.
If we want to see the data model entities larger, we can use the zoom tool in the icon bar or simply place the mouse over each entity so we can see the maximized object in the Collapsible Pane on the bottom area of the window.
Figure 13. Zooming in on data model entities
In this very simple way we have generated a data model with the Toad Data Modeler of an existing schema in an Oracle 11g R2 database.
The Model Conversion wizard allows us to convert:
In this section we are going to convert the previously generated basic model to MS SQL Server 2017 database platform.
We Select Model Menu > Convert Model > Run…
Figure 14. Choosing to run Convert Model Wizard
The Model Conversion wizard opens.
First, we need to define the Target of the database. Select Microsoft SQL Server 2017
Figure 15. The Model Conversion Wizard. Selecting the target database platform
We click on the Next button.
In Conversion Settings window, we accept the default values.
Figure 16. Conversion Settings window
Note: Converting between different platforms means that some items that contain SQL code may not be converted correctly (e.g. After Scripts, Before Scripts, Procedures... ). In this case, it is recommended to check the Comment Out Database Specific Items checkbox. This way, SQL code that cannot be successfully converted will be commented out. We can review these parts of code later and fix them on our own.
We click on the Next button.
In this step of the wizard, we choose what object types will be converted. We can access full list of Objects and Properties by clicking on Detailed Settings.
Figure 17. Choosing object types to be converted
We click on the Next button.
In the Items Selection window, we select all the objects that we want to convert to another model.
Figure 18. Selecting objects to convert
Finally, we come to the Review window, which shows us some statistics about how many and what kinds of objects will be converted.
We change the name for the new data model to "Convert Oracle 11g R2 to SQL Server 2017" and once we're done reviewing, we click on the Finish button.
Figure 19. Review Window. Specify the new model name and click Finish.
The conversion process occurs and our model is now converted to MS SQL Server 2017 database platform.
Figure 20. Showing the new data model generated for MS SQL Server 2017 database
Generate Script
To generate the Data Model Script, we can click on the "Generate DDL Script" icon or click from the Model menu > Generate DDL Script > Run…
Figure 21. Opening the DDL Script Generation Wizard
The "DDL Script Generation of ..." wizard opens. Then we can see four configuration tabs:
What to Generate Tab
Location of SQL File: in this box we select the location where the generated script will be saved.
Figure 22. The What to Generate tab of the DDL Script Generation Wizard
Detail Settings
Figure 23. The Detail Settings tab of the DDL Script Generation Wizard
Referential Integrity
Figure 24. The Referential Integrity tab of the DDL Script Generation Wizard
Select List
Figure 25. The Select List tab of the DDL Script Generation Wizard
Before generating the Script, we can perform a verification. To do this, we click on the Verify button.
The tool helps us find errors (red icon), warnings (yellow icon) and also show us hints (blue icon) in the lower pane called Verification Log.
Figure 26. Verifying the model before generating the script
To learn more about the errors, warnings, or hints, we place the mouse over the row and it shows us tips to solve the problem. In this case, it shows us that the Authors entity has not been assigned a Filegroup.
Figure 27. Verification log provides error information
For this demonstration, we have created a database called quest and it only has the PRIMARY filegroup.
We double click on first row, Entity - AUTHORS, and in the popup window we assign the Filegroup to the entity. If it is not shown in the select list, we can create a filegroup called PRIMARY by clicking on the button with the ellipsis.
Figure 28. Learning how to implement the hints suggested by Toad Data Modeler - Assigning a Filegroup to an entity.
Now we assign the newly created Filegroup to the AUTHORS entity and click on the Apply button. We can see that the Hints disappear from the list of the Verification log.
Figure 29. Applying the hint suggested by Toad Data Modeler to the AUTHORS entity
We do the same for each of the remaining entities.
Once the errors and warnings have been solved and the hints given by the tool have been applied, we can generate the Script by clicking on the Generate button.
Note: We can ignore the warnings or hints but I advise you to resolve the errors before generating the script.
Figure 30. Clicking on the Generate button
At the end of the Script generation process, we can see the code by clicking on the Show Code button.
Figure 31. Clicking on the Show Code button brings up SQL File Viewer window
Now we use Toad Data Point to execute the SQL Server database script.
We connect and open the newly generated script.
When the quest database is selected, we can see that the database does not have tables created yet. We click on the Execute Scripts icon.
Figure 32. Clicking on the Execute Scripts icon.
Once the script is executed, we can see the tables created in the quest database.
Figure 33. Tables have been created.
SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and thus is free for use for any purpose, commercial or private.
Now let's convert the data model from Oracle 11g R2 to SQLite.
We follow the same steps described above for the Model Conversion Wizard; click on Model menu > Convert Model > Run…
We select the Target of the database: SQLite 3.7. In the name of the model enter: "Convert Oracle 11gR2 to SQLite".
Generate Script
We open the wizard to generate the script and before generating we verify and solve the problems shown in the verification log.
In this case, we need to order the entities because, for example, the Foreign key in the child entity of the Books entity must be created before the entity Books_Categories.
Figure 34. Learning how to solve warning messages shown in the Verification Log Tab - Error message indicates we must reorder entities.
We reorder the entity Books_Categories up and then click on the Apply button. We check again and we no longer have errors.
Now, we generate the Script for SQLite.
Figure 35. Generating the script for SQLite database
We execute the script inside our DB Browser for SQLite tool.
Figure 36. Executing the script
We can see that all the tables have been created in the database quest.db.
Figure 37. Tables have been created
The Toad Data Modeler can help us greatly reduce the amount of work hours to migrate a data model from one database platform to another platform.
The generation of the script is not totally automatic. When we work with basic models we usually have no problems, but when our data model is more complex we need to work manually with the Toad Data Modeler generated script to avoid errors when we execute the script on the target platform.
Tags: Toad Data Modeler
Written by Clarisa Maman Orfali
Clarisa is a System Engineer with more than 24 years of experience as a developer, teacher, and consultant in Information Technology. She was a Founder and CEO between 2013 to 2020 at ClarTech Solutions, Inc., a consulting firm specializing in Oracle APEX and Open Source technologies. Clarisa entered the world of Oracle technologies in 2009. After a few years, she discovered the power of application development with Oracle Application Express (APEX) and specialized in that area.
Clarisa is from Argentina, and she is living and working in Irvine, California, in the United States. She's also a Co-Founder of the Argentina Oracle User Group (AROUG). In addition, she had actively participated in large and popular events such as the OTN Tour Argentina and Oracle APEX Tour Latin America after called Oracle Developer Tour. She's also authored the first three Spanish books about Oracle Application Express and recorded and published several Online Video Courses.
She has a great passion for Oracle technologies and wants to expand that passion and influence, transferring her knowledge and experience to the entire Latin American and worldwide Oracle communities. You can always contact her through her Twitter account (@Clari707).
Clarisa loves spending time with her family by enjoying outdoor activities with her two adored children, Melanie and Nicolas, as well as hanging out with her friends. Also, she loves traveling with her husband Julio worldwide and to takes new challenges at every moment of her life.
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.