Generating Database Change Script

In order to patch existing database to newer version, database administrator need to prepare DDL (Data Definition Language) scripts with commands of updates. Visual Paradigm is capable in updating database, or just to generate the DDL required, by comparing differences between the design, which is the ERD(s), and the database, and to generate the DDL scripts accordingly. In addition to generating update scripts, Visual Paradigm can also generate scripts for creating and dropping database. In this tutorial, we will focus on generating the update scripts.
Topics
1. Update database design - the ERD
2. Generate database scripts for "Update Database"
3. Execute scripts in Oracle

September 1, 2009
Views: 51,297
PDF Download
Compatible: Enterprise, Professional, Standard

The original database design for the Computer Sales project:

Computer Sales project

  1. Let's update the design. Rename entity Addresses to Customer_Addresses.
    Rename entity in ERD
  2. Add a new column to entity Computer_Part by right clicking on it and selecting New Column from the popup menu.
    Create new column in entity
  3. Enter discount : numeric(8, 2) as the column definition, press the Enter key and then the Esc key to confirm editing.
    Enter new columns in ERD
  4. Move the mouse cursor over Computer_Part, press on the One-to-Many Relationship -> Entity icon in the resource-centric interface, and drag to the empty region to create a new entity. Enter Part_Usage as entity name.
  5. Rename the automatically created FK (foreign key) column to part_id.
  6. Repeat the previous steps to add the following columns to Part_Usage.
    • +usage_id : number(10)
    • description : varchar2(1024)

    Create new columns in entities
  7. The design is now updated. To generate the scripts of changes, select Tools > Database > Generate Database... from the main menu.
  8. In the Database Code Generation dialog box, specify the output path of the DDL script file.
    Specify output path of generated script
  9. Select Update Database as the type of generation.
    Generate script to update Oracle database schema
  10. Un-check Export to database to make it generate scripts file only.
    Uncheck on export to database
  11. Click OK to start generation.
  12. The script is generated to the given path.
    Open generated update database script
  13. Let's take a look at the generated scripts.
    Take a look to generated script
  14. To execute the scripts, open the Oracle Web client, select SQL > SQL Scripts, and click Upload to upload the generated DDL file.
    Upload Oracle database update script
  15. Once the file is uploaded, click the script icon.
    Select available database script
  16. Click Run to execute the scripts to database.
    Run Oracle database script
  17. Click Run again to confirm.
    Confirm to run select database script
  18. Click the file icon to check the result.
    View database script execution results
  19. The scripts are executed successfully. Database is updated.
    Detail view of execution result
  20. We can see the changes in the Object Browser.
    Confirm database is updated with Oracle object browser
Related Links
Trademark Disclaimer

Oracle is U.S. registered trademark of Oracle Corporation