Using Schema Compare in Visual Studio 2013

Purpose

This tutorial describes how to use the Schema Compare tool in Visual Studio 2013.

Time to Complete

Approximately 45 minutes.

Introduction

Schema Compare is an integrated tool that is included with the Oracle Developer Tools for Visual Studio (ODT). It is used to visually inspect the differences between a source and a target schema and to generate a diff script that modifies the target schema to make it identical to the source. The source and target schemas can reside on the same database or on two different databases.

Developers will typically evolve their development schema as they work on their .NET application, for example, creating or modifying tables or stored procedures that are used by their .NET application. During development, they can use the Schema Compare tool to visually inspect what changes have been made thus far, and when development has completed, they can use the tool to generate the deployment scripts for synchronizing the development and production schemas.

Scenario

This OBE walks through a general use case scenario of a .NET developer. It consists of the following steps:

1. Start with a development schema identical to the production schema. We will create two schemas named DEVELOPMENT and PRODUCTION and they will both contain a portion of the HR schema.

2. With the progress of the .NET application, the development schema evolves to meet the needs of the application.

3. During development, use the Schema Compare tool to compare the development versus production schemas and view the differences.

4. On the completion of the development, use the Schema Compare tool to compare the development versus production schemas and generate the diff scripts and rollback scripts.

5. Deploy the diff script to bring the production database up to the required version of development schema.

6. Apply the rollback script to roll back the changes made to the production database.

We will use the Schema Compare tool to identify, synchronize and rollback the differences in the created instances.

Prerequisites

Before starting this tutorial, you should:

1. Install Microsoft Visual Studio 2013

2. Install or obtain SYSDBA access to Oracle Database 10g Release 2 or higher

3. Install Oracle Developer Tools for Visual Studio version 12.1 or higher

4. Install the HR sample schema if you do not already have access to it

Note: The database connection information are unique for every database client. In this OBE, the database connection alias used (ORCL) is merely an example and likely will not match your client connection alias. To complete the OBE, replace the current values with the values for your specific database instance.

In this section, we will be creating two new schemas namely, DEVELOPMENT and PRODUCTION. These two schemas will act as development schema and production schema respectively. Initially these two schemas are identical and are derived from part of the HR schema.

1. Open Microsoft Visual Studio 2013.

   

2. To create the HR connection, select View > Server Explorer.

   

3. Right click Data Connections and select Add Connection...

   

4. The Add Connection dialog box opens up. Make sure that Datasource field is showing "ODP.NET". If it is not, click the Change button to change it. Enter HR for the User name and the password in the Password field. Ensure the Data source name is ORCL and click Test Connection.

   

   The Test connection is successful. Click OK.

5. Click OK to confirm creating the new connection.

   

6. Next, create a connection with the database administrator privileges. We will use this connection to create the DEVELOPMENT and PRODUCTION schemas. In the server explorer, right-click Data Connections and select Add Connection...

   

7. In the Add Connection dialog box, enter SYS for the User name and provide the SYS password in the password field. Select the Connect as SYSDBA role check box. Ensure the Data source name is ORCL and click OK.

   

8. Once the connection is successful, right-click SYS.ORCL data connection and select Query Window.

   

9. Copy the following code into the query window. Select the complete code and click the Execute Query button. (Depending on your environment you may need to modify the tablespace name and the profile name)

   
   CREATE USER DEVELOPMENT
    IDENTIFIED BY development
    DEFAULT TABLESPACE "USERS"
    PROFILE "DEFAULT";
   
   grant connect, resource, unlimited tablespace to DEVELOPMENT;
   
   CREATE USER PRODUCTION
    IDENTIFIED BY production
    DEFAULT TABLESPACE "USERS"
    PROFILE "DEFAULT";
   
   grant connect, resource, unlimited tablespace to PRODUCTION;
   

   

   The code in the query window creates and grants resources to the DEVELOPMENT and PRODUCTION schemas.

10. The result is displayed in the query output window.

    

11. Repeat steps 2 to 5 to create connections in Server Explorer using the following information:

    To create the DEVELOPMENT connection:

    Username: DEVELOPMENT
    Password: development
    Data source name: ORCL

    To create the PRODUCTION connection:

    Username: PRODUCTION
    Password: production
    Data source name: ORCL

    After completing this step, the Server Explorer should have the following connections established successfully:

    

In this section, we will create a Solution that contains a Console Application project as well as three database projects. The first project will contain scripts for initially creating tables and procedures in the DEVELOPMENT and PRODUCTION schemas. The second database project will contain deployment scripts to update the PRODUCTION schema to the level of the DEVELOPMENT schema and the third database project will contain rollback scripts to rollback the changes performed by the deployment script.

1. First, let us create a Console Application. Select File > New > Project...

   

2. Expand Installed > Templates > Visual C# and select Windows. From the list of applications, select Console Application and click OK.

   

3. You have successfully created a .NET Windows Console Application. In the Solution Explorer, right-click Solution 'Console Application#' and select Add > New Project...

   

4. In the Add New Project window, expand Installed and select Oracle. From the list of projects, select Oracle Database Project. Enter the name of the project as CREATESCRIPTS and click OK.

   

5. Add a reference to the HR.ORCL connection and click OK.

   

6. Follow steps 3 and 4 to create the other two Oracle database projects namely DEPLOYSCRIPTS and ROLLBACKSCRIPTS. Select the PRODUCTION.ORCL connection in the Add Database Reference dialog box for both these projects. After completing this step, the solution explorer should contain the highlighted projects:

   

In this section, we will be generating the create scripts to create the following tables and procedures in the DEVELOPMENT and PRODUCTION schemas:

Tables: DEPARTMENTS, EMPLOYEES, JOB_HISTORY, JOBS
Procedures: ADD_JOB_HISTORY, SECURE_DML

1. In the Server Explorer, expand the tables and procedures under the HR.ORCL connection. The default HR schema consists of the following tables and procedures:

   

2. Select the DEPARTMENTS table. Hold the Ctrl key and select the EMPLOYEES, JOB_HISTORY and JOBS tables. Further, select the ADD_JOB_HISTORY and SECURE_DML procedures under the Procedures node.

   

3. Right-click and select Generate Create Script to Project...

   

4. Select the CREATESCRIPTS project in the Choose Oracle Database Project dialog box and click OK.

   

5. Wait for a few seconds. Once the script is available, you see that the script has a reference to many other scripts to create tables, dependencies, indexes, procedures and triggers.

   

6. Let us run the above script against the DEVELOPMENT and PRODUCTION schemas to create the tables and procedures. In the Solution Explorer, Expand CREATESCRIPTS > Scripts. Right-click on the SQL script located there that starts with "DeployScript" and select Run On...

   

7. In the Run On dialog box, select PRODUCTION.ORCL as the connection to run the scripts. Click OK.

   

8. Check the output window for errors. You should have no errors in the output.

   

9. Repeat step 6 to run the deployment script on the development schema. Right-click on the script that starts with "DeployScript" and select Run On...

   Select DEVELOPMENT.ORCL in the Run On dialog box and click OK.

   

10. Refresh the DEVELOPMENT.ORCL and PRODUCTION.ORCL connections in Server Explorer and then verify that the tables and procedures were created under both the connections.

    

In this section, we will modify tables and procedures in the DEVELOPMENT schema. This creates a difference in the DEVELOPMENT schema compared to the PRODUCTION schema.

1. Let us update the JOB_HISTORY table in the DEVELOPMENT.ORCL connection by adding a new column called "BIRTHDAY". Right-click JOB_HISTORY and select Design...

   

2. Click the Add button. Perform the following tasks under column properties to create the new column:

   • Enter BIRTHDAY as the name of the column
   • Select the data type of the column as DATE
   • Uncheck the Not Null check box
   • Click the Save button

   

3. Next, let us edit the ADD_JOB_HISTORY procedure. Right-click the procedure name under DEVELOPMENT.ORCL and select Edit...

   

4. Modify the procedure to accept the new column "BIRTHDAY" that we added in the previous steps. Add the following to the header of the procedure:

   , p_birthday        job_history.birthday%type

   

   Modify the insert statement to add "birthday" to the column list, and "p_birthday" to the VALUES list.

   

5. Right-click anywhere in the code window and select Compile.

   

6. The code is compiled successfully.

   

In this section, we will use the Schema Compare Tool to view the differences in the schemas, and to create the diff and rollback scripts.

Viewing Schema Differences and Generating Diff Scripts

1. Right-click DEVELOPMENT.ORCL in the Server Explorer and select Oracle Schema Compare.

   

2. The Schema Compare dialog box opens up. Verify that DEVELOPMENT.ORCL is selected under Select Source. Select PRODUCTION.ORCL under Select Target and click OK.

   

3. Wait for a few seconds. The Schema compare tool displays the comparison between the DEVELOPMENT and PRODUCTION schemas. The Compare Status column indicates that the schemas are not identical.

   

4. Expand Schemas > DEVELOPMENT -> PRODUCTION > Tables > Relational to see the difference in the tables between both the schemas. Note the status of the JOB_HISTORY table.

   

5. Drill down further to see the difference in columns with in the JOB_HISTORY table. Notice that the BIRTHDAY column is present only in the DEVELOPMENT schema.

   

6. Select the JOB_HISTORY table and click on the Object Definition tab to view the difference in the object definition for the table.

   

7. Click on the UPDATE SQL tab. It gives a list of ALTER TABLE statements that can be executed to update the JOB_HISTORY table in the PRODUCTION instance to the level of the DEVELOPMENT instance.

   

8. Expand Procedures > ADD_JOB_HISTORY. Notice that the P_BIRTHDAY parameter is present only in the source schema.

   

9. Select the ADD_JOB_HISTORY procedure and click on the Object Definition tab to view the difference in the object definition for the procedure.

   

   Click on the UPDATE SQL tab to view the CREATE statement that can be executed to replace the ADD_JOB_HISTORY procedure in the PRODUCTION instance with that of the DEVELOPMENT instance.

   

10. Navigate to the top of the window. Click on the down arrow after the Export to Editor tab and select Add to Database Project.

    

11. Select DEPLOYSCRIPTS and click OK.

    

12. Navigate to DEPLOYSCRIPTS > Scripts folder under the Solution Explorer window and double click the script that we added in the previous step. The script opens up in the code window. Examine the script. You see that the script acts as a master script which in turn calls two SQL scripts to update the JOB_HISTORY table and the ADD_JOB_HISTORY procedure. You can navigate to each of these script to look at the SQL code inside it.

    

    

13. Examine the DEVELOPMENT_JOB_HISTORY.sql script. Navigate to the Tables folder under DEPLOYSCRIPT and double click the DEVELOPMENT_JOB_HISTORY.sql script. You see that the script contains SQL queries to add the new BIRTHDAY column.

    

    

    Note: The deployment scripts for the ADD_JOB_HISTORY procedure are available under the DEPLOYSCRIPTS > Procedures folder.

Generating Rollback Scripts

In this section, we will be creating the rollback script which can be executed to roll back the changes performed to the PRODUCTION schema using the deployment script.

1. In the code page, navigate to the Schema Compare window and click on the Source to Target button on the top-left corner. This button swaps the source connection to PRODUCTION.ORCL and the target connection to DEVELOPMENT.ORCL.

   

2. Expand the tree structure and select the JOB_HISTORY table. Click on the UPDATE SQL tab. The tab displays SQL statements to update the JOB_HISTORY table in the DEVELOPMENT schema to the level of the PRODUCTION schema.

   

3. Click on Add to Database Project button

   

4. Choose the ROLLBACKSCRIPTS project and click OK. The summary information is displayed in the code window.

   

   

5. Navigate to the ROLLBACKSCRIPTS project under the Solution Explorer.

   

6. In the Scripts folder, double click the SQL script that starts with "DeployScript". The script opens up in the code window.

   

   

In this section, we will execute the deployment scripts that we created in the previous section of this OBE.

1. Close all the tabs in the code window.

2. In the Solution Explorer, navigate to DEPLOYSCRIPTS > Scripts folder. Select the deploy script that we created in the previous section. Right-click and select Run On...

   

3. Choose the PRODUCTION.ORCL connection and click OK. This runs the deployment script against the PRODUCTION instance.

   

4. The script executes successfully with no errors.

   

5. Run the Schema compare tool again. Right-click DEVELOPMENT.ORCL connection in the Server Explorer and select Oracle Schema Compare. Ensure DEVELOPMENT.ORCL is selected under Select Source and select PRODUCTION.ORCL under Select Target and click OK.

   

6. The Schema Compare tool indicates that both the schemas are identical.

   

   Note: You can rollback the changes made to the production version by using the rollback script under the ROLLBACKSCRIPTS > Scripts folder.

In this tutorial, you have learned how to:

• Create database connections in Server Explorer
• Add a Oracle Database Project to a solution
• Generate SQL scripts to create schema objects
• Run a SQL script in an Oracle Database Project against a database
• Use the Schema Compare tool to compare schemas
• Use the Schema Compare tool to generate deployment scripts
• Use the Schema Compare tool to generate rollback scripts

Resources

• Oracle .NET Developer Center
• Additional .NET OBEs in the Oracle Learning Library

Credits

• Lead Curriculum Developer: Ashwin Agarwal
• Other Contributors: Alex Keh, Christian Shay