Using Toad Data Modeler with Oracle DB on AWS RDS-II

    Nov 8, 2018 12:44:54 PM by Deepak Vohra

    Toad Data Modeler is a design and modeling tool for data with support for forward- and reverse-engineering to most of the commonly used relational database management systems (RDBMS). In the first of two articles we started by creating an empty model in Toad Data Modeler. Subsequently we reverse-engineered an Oracle Database instance from AWS RDS to the model. In this continuation article we shall discuss some of the other Toad Data Modeler features, as used with the model we reverse engineered. This article has the following sections.

    Verifying a Model

    Exploring Database Objects

    Generating DDL for Model

    Browsing Generated DDL in Toad Oracle

    Saving a Model

    Searching for Database Objects

    Creating a Report

    Converting a Model

    Verifying a Model

    A new model, whether created in Toad Data Modeler or reverse engineered, should be verified before use. To verify the model we reverse-engineered, select the model tab and select Model>Verify Model as shown in Figure 1.

    Figure 1. Selecting Verify Model

    Figure 1. Model>Verify Model

    In Model Verification the default Verification Settings are displayed, as shown in Figure 2. Click on Save & Verify.

    Figure 2. Default verification settings are displayed

    Figure 2. Model Verification

    An Information dialog indicates that “Verification has finished without errors”, as shown in Figure 3. Click on OK.

    Figure 3. Verification Finished message

    Figure 3. Verification Information

    The verification settings may be modified by selecting Options as shown in Figure 4.

    Figure 4. Selecting options

    Figure 4. Options

    In Options select Model>Oracle>Oracle 11g Release 2 as shown in Figure 5. The default Verificator settings are listed and may be modified.

    Figure 5. Default Verificator Settings

    Figure 5. Default Verificator Settings

    Exploring Database Objects

    The database objects in the model we reverse-engineered are listed in the Object Viewer tab in Figure 6.

    Figure 6. Object viewer

    Figure 6. Object Viewer

    To display detail about an object double-click on the object in Physical Model Explorer, the DBA object as an example, as shown in Figure 7.

    Figure 7.  Selecting the DBA object to display detail

    Figure 7. Selecting DBA Object

    The User Group Settings for the DBA group get displayed, as shown in Figure 8.

    Figure 8. User Group Settings for the DBA group

    Figure 8. User Group Settings for DBA

    An object may also be selected from the Physical Model Explorer, as shown for DVOHRA  user in Figure 9.

    Figure 9. Selecting an object from the Physical Model Explorer

    Figure 9. Selecting User DVOHRA

    The User Properties for DVOHRA get displayed, as shown in Figure 10.

    Figure 10. User properties for the selected user are displayed

    Figure 10. User Properties for DVOHRA

    The Object Viewer may also be selected from the toolbar. A specific type of model items may be selected from Model>Model Items, as shown in Figure 11. Select User Groups as an example.

    Figure 11. Selecting Model Items and the User Groups from the toolbar

    Figure 11. Model>Model Items>User Groups

    The User Groups get listed, as shown in Figure 12.

    Figure 12. User Groups are listed

    Figure 12. User Groups

    Generating DDL for Model

    To generate DDL for a model select the model in Physical Model Explorer and select Model>Generate DDL Script>Run, as shown in Figure 13.

    Figure 13. Generating DDL Script

    Figure 13. Model>Generate DDL Script>Run

    The DDL Script Generation wizard gets launched, as shown in Figure 14. The Location of SQL File field specifies the default file, including directory path  generated. Select a User/Schema from the drop-down.

    Figure 14. DDL Script Generation Wizard

    Figure 14. DDL Script Generation Wizard

    Select DDL Script Generation strategy as Create or Replace as shown in Figure 15.

    Figure 15. Selecting the Create or Replace DDL Script Generation Strategy

    Figure 15. Create or Replace DDL Script Generation Strategy

    Click on Verify as shown in Figure 16.

    Figure 16. Clicking on the Verify button

    Figure 16. Verify

    An Information dialog indicates that “Verification has been finished without errors”, as shown in Figure 17.

    Figure 17. Verification Completed Without Errors message

    Figure 17. Verification Completed without any errors found

    Click on Generate as shown in Figure 18 to generate an SQL script.

    Figure 18. Clicking on the Generate button

    Figure 18. Generate

    If the script generated contains multiple objects, a Confirm dialog prompts whether to split the file, as shown in Figure 19. Click on Yes if the generated DLL file may be split. Click on No if the file should not be split.

    Figure 19. Confirm Dialog to Split File

    Figure 19. Confirm Dialog to Split File

    A second Confirm dialog may get displayed if the number of files generated could be so large that it could affect the system performance. The Generated file is shown in Figure 20.

    Figure 20. Generated DDL Script

    Figure 20. Generated DDL Script

    To display the log for the generated DDL, click on Show Log as shown in Figure 21.

    Figure 21. Clicking on Show Log

    Figure 21. Show Log

    As indicated by a log message in Figure 22, SQL Generation has been finished successfully.

    Figure 22. Log Messages, including message that SQL Generation has finished successfully

    Figure 22. Log Messages

    The DDL Script Generation Wizard may also be launched by selecting Generate DDL Script from the toolbar, as shown in Figure 23.

    Figure 23. Selecting Generate DDL Script from the toolbar

    Figure 23. Generate DDL Script

    Browsing Generated DDL in Toad for Oracle

    To browse the generated DDL in Toad for Oracle, click on Show Code in the DDL Generation wizard, as shown in Figure 24.

    Figure 24. Clicking on Show Code

    Figure 24. Show Code

    The Toad for Oracle tool gets launched. The generated SQL gets displayed in Toad for Oracle, as shown in Figure 25.

    Figure 25. Generated SQL in Toad for Oracle

    Figure 25. Generated SQL in Toad for Oracle

    Saving a Model

    To save the model we reverse-engineered, select the model tab in Physical Model Explorer and select Save Model from the toolbar as shown in Figure 26.

    Figure 26. Clicking on Save Model

    Figure 26. Save Model

    In the Save Model as window specify a file name and click on Save as shown in Figure 27.

    Figure 27. The Save Model as window

    Figure 27. Save Model as

    Searching for Database Objects

    To search for database objects click on Quick Search in the toolbar as shown in Figure 28.

    Figure 28. Clicking on the Quick Search icon in the toolbar

    Figure 28. Quick Search

    In Quick Search wizard specify a search term or object to search, and the objects get listed as shown in Figure 29.

    Figure 29. Objects are listed in the Quick Search wizard

    Figure 29. Quick Search wizard

    Creating a Report

    In this section we shall discuss creating a report from the model we reverse-engineered. To generate a report, select the Model tab and select Model>Generate Report>Run as shown in Figure 30.

    Figure 30. Selecting Generate Report and then Run

    Figure 30. Model>Generate Report>Run

    The Report Wizard gets launched, as shown in Figure 31. Select a report format from the choices of HTML, RTF, and PDF and click on Next as shown in Figure 31.

    Figure 31. Selecting a report format

    Figure 31. Report Wizard>Selecting a report format

    Next, select report type, as shown in Figure 32, in which Basic HTML Report for PER Model is selected. Click on Next.

    Figure 32. Selecting Report Type

    Figure 32. Selecting Report Type

    Next, select the file and folder to save report to as shown in Figure 33. Click on Next.

    Figure 33. Indicating what folder to save the report file to

    Figure 33. Select File

    Next, select layout as Frameless as shown in Figure 34. Select CSS Style as Default. Click on Next.

    Figure 34. Selecting the report Layout

    Figure 34. Selecting Layout

    Next, select What to Report as shown in Figure 35.

    Figure 35. Selecting What to Report by tickinng checkboxes for properties

    Figure 35. What to Report

    In Select Workspaces select the default settings as shown in Figure 36.

    Click on Next.

    Figure 36. Selecting Workspaces

    Figure 36. Select Workspaces

    In Options, the various options including Display Mode are listed for selection, as shown in Figure 37. Click on Next. Click on Execute.

    Figure 37. Display Mode Options selected, clicking Execute button

    Figure 37. Options

    An Information dialog indicates that “Report was generated”, as shown in Figure 38.

    Figure 38. The Report was Generated message

    Figure 38. Information dialog

    The log messages should also indicate that a report was generated, as shown in Figure 39.

    Figure 39. Log Messages

    Figure 39. Log Messages

    Display the report in a browser, as shown in Figure 40. In addition to the basic Model Info, tabs are generated for ER (entity relation) Diagram, Tablespaces, Directories, Editions, Users, and User Groups.

    Figure 40. HTML Report shows model info

    Figure 40. HTML Report

    Scroll to list the number of different database objects in the model as shown in Figure 41.

    Figure 41. Model Info shows the Number of various types of Database Objects

    Figure 41. Number of Database Objects

    The Tablespaces tab displays the tablespaces in the Oracle Database on RDS, as shown in Figure 42.

    Figure 42. The Tablespaces tab

    Figure 42. Tablespaces

    The Directories tab lists the directories, as shown in Figure 43.

    Figure 43. the Directories tab

    Figure 43. Directories

    The Users tab lists the users. A user may be selected, as shown in Figure 44.

    Figure 44. Detailed User information

    Figure 44. Users>DVOHRA

    The User Groups tab lists the user groups, from which a user group may be selected to get detailed information about the user group, as shown for DBA in Figure 45.

    Figure 45. Detailed information about the DBA user group

    Figure 45. User Groups>DBA

    The Report Wizard may also be launched from the toolbar, as shown in Figure 46.

    Figure 46. Launching the Report Wizard from the Toolbar

    Figure 46. Selecting Report from Toolbar

    Converting a Model

    Toad Data Modeler provides a wizard to convert a data model created for one database to a model for another database.  To launch the model conversion wizard, select Model>Convert Model>Run as shown in Figure 47.

    Figure 47. Launching the Model Conversion Wizard

    Figure 47. Model>Convert Model>Run

    The Model Conversion wizard gets launched, as shown in Figure 48. First select a Target (Database) Platform.

    Figure 48. Selecting a target database platform in the Model Conversion Wizard

    Figure 48. Model Conversion Wizard

    Select Oracle 18c as the target database platform as shown in Figure 49.

    Figure 49. Selecting Oracle 18c as Target Database Platform

    Figure 49. Selecting Oracle 18c as Target Database Platform

    Next, select Conversion Settings as shown in Figure 50. Click on Next.

    Figure 50. Selecting the conversion settings

    Figure 50. Model Conversion>Conversion Settings

    Next, select object types to convert as shown in Figure 51. Select Convert All to convert all objects.

    Click on Next.

    Figure 51. Selecting Object Types to convert

    Figure 51. Model Conversion>Select Object Types

    Next, select the items to convert, as shown in Figure 52. Click on Next.

    Figure 52. Selecting the items to convert

    Figure 52. Model Conversion> Items Selection

    In Review the summary of items to be converted are listed including number of Commits for each as shown in Figure 53. Click on Finish.

    Figure 53. Review shows the summary of items to be converted. Click on Finish.

    Figure 53. Model Conversion>Review

    The model conversion gets started as shown in Figure 54.

    Figure 54. Model conversion progress bar

    Figure 54. Converter converting

    The Oracle 18c model gets generated and added to Physical Model Explorer as shown in Figure 55.

    Figure 55. The Oracle 18c Model now shows in Physical Model Explorer

    Figure 55. Oracle 18c Model

    To verify the converted model select Model>Verify Model as shown in Figure 56.

    Figure 56. Selecting Verify Model

    Figure 56. Model>Verify Model

    In Model Verification dialog click on Save & Verify as shown in Figure 57.

    Figure 57. Clicking on Save and Verify

    Figure 57. Model Verification>Save & Verify

    An Information dialog indicates that the verification finished without errors as shown in Figure 58. Click on OK.

    Figure 58. Information Dialog. Verification complete without errors. Click OK

    Figure 58. Information Dialog

    Conclusion

    In two articles we have discussed using Toad Data Modeler to reverse engineer ] Oracle Database on AWS RDS. The benefits of using Toad Data Modeler for data design and modeling include an integrated support for modeling all the common relational databases, and support to forward- and reverse-engineer the databases.

     

    Tags: Toad Data Modeler Oracle Cloud

    Deepak Vohra

    Written by Deepak Vohra

    Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.