10 Deploying an Oracle Database Application

About DDL Statements and Schema Object Dependencies

When you run your installation script files, their DDL statements create the schema objects of your application in the deployment environment. To create the installation script files correctly and run them in the correct order, you must understand the dependencies between the schema objects of your application.

If the definition of object A references object B, then A depends on B. Therefore, you must create B before you create A. Otherwise, the statement that creates B either fails or creates B in an invalid state, depending on the object type.

Typically, you install schema objects and data in the deployment environment in this order:

1. Package specifications

2. Tables (with constraints and indexes) in correct order

3. Sequences (often used by triggers)

4. Triggers

5. Synonyms

6. Views (which might reference functions, procedures, or synonyms)

7. Package bodies

8. Data

However, for a complex application, the order for creating the objects is rarely obvious. Usually, you must consult the database designer or a diagram of the design.

About INSERT Statements and Constraints

When you run your installation script files, their INSERT statements insert the data from the source tables into the corresponding new tables. For each source table in your application, you must determine whether any constraints could be violated when their data is inserted in the new table. If so, you must first disable those constraints, then insert the data, and then try to re-enable the constraints. If a data item violates a constraint, you cannot re-enable that constraint until you correct the data item.

If you are simply inserting lookup data in correct order, constraints are not violated. Therefore, you do not need to disable them first.

If you are inserting data from an outside source (such as a file, spreadsheet, or older application), or from many tables that have much dependent data, disable the constraints before inserting the data.

Some possible ways to disable and re-enable the constraints are:

• Using SQL Developer, disable and re-enable the constraints one at a time.

• Edit the installation script file, adding SQL statements that disable and re-enable each constraint.

• Create a SQL script with SQL statements that disable and enable each constraint.

• Find the constraints in the Oracle Database data dictionary, and create a SQL script with the SQL statements to disable and enable each constraint.

  To find and enable the constraints used in the EVALUATIONS, PERFORMANCE_PARTS, and SCORES tables, enter these statements into a SQL Worksheet window:

  SELECT 'ALTER TABLE '|| TABLE_NAME || ' DISABLE CONSTRAINT '|| 
    CONSTRAINT_NAME ||';'
    FROM user_constraints
   WHERE table_name IN ('EVALUATIONS','PERFORMANCE_PARTS','SCORES');
   
  SELECT 'ALTER TABLE '|| TABLE_NAME || ' ENABLE CONSTRAINT '|| 
    CONSTRAINT_NAME ||';'
    FROM user_constraints
   WHERE table_name IN ('EVALUATIONS','PERFORMANCE_PARTS','SCORES');
  

Creating an Installation Script File with SQL Developer

To create an installation script file with SQL Developer, use the Database Export tool. You specify the name of the installation script file and the objects and data to export, and SQL Developer generates DDL statements for creating the objects and INSERT statements for inserting the data into new tables, and writes these statements to the installation script file.

To create an installation script file with the Database Export tool:

1. If you have not done so, create a directory for the installation script file, separate from the Oracle Database installation directory (for example, C:\my_exports).

2. In the SQL Developer window, click the menu Tools.

   A drop-down menu appears.

3. From drop-down menu, select Database Export.

   The Source/Destination window opens.

4. In the Source/Destination window:

   1. In the File field, type the full path name of the installation script file (for example, C:\my_exports\hr_export.sql).

      The file name must end with .sql.

   2. From the Connections drop-down menu, select your connection (for example, hr_conn).

   3. Select the desired DDL Options (and deselect any selected undesired options).

      By default, Terminator and Pretty Print are selected and all other options are deselected. If you deselect Terminator, the installation script file fails.

      For descriptions of the DDL Options, see Oracle Database SQL Developer User's Guide.

   4. Click Next.

   The Types to Export window appears, listing the types of objects, and data, that you can export. To the left of each object is a check box. By default, every check box is selected.

5. In the Types to Export window:

   1. Deselect the check boxes for the types that you do not want to export.

      Selecting or deselecting Toggle All selects or deselects all check boxes.

   2. If you do not want to export data, deselect the check box Data.

   3. Click Next.

   The Specify Objects window appears.

6. In the Specify Objects window:

   1. In the drop-down menu with the value HR, accept that value.

   2. Click Go.

      A list appears, showing all objects in schema HR whose types you specified in step 5, and, if you specified Data in step 5, names of tables in schema HR.

   3. To export all items on the list, click >>; otherwise, select the objects to export and the tables with data to export, and click >.

      The items to be exported move from the original column to the other column. (To move all of them back to the original column, click <<; to move individual items back, select them and click <.)

   4. Click Next.

   The Export Summary window appears.

7. In the Export Summary window, click Finish.

   The Exporting window opens, showing that exporting is occurring. When exporting is complete, the Exporting window closes, and the SQL Worksheet shows the contents of the installation script file that you specified in step 4.

8. In the installation script file, check that:

   • Referenced objects are created before their dependent objects.

   • Tables are created before data is inserted into them.

   If necessary, edit the file in the SQL Worksheet or any text editor.

Editing Installation Script Files that Create Sequences

For a sequence, SQL Developer generates a CREATE SEQUENCE statement whose START WITH value is relative to the current value of the sequence in the development environment.

If your application uses the sequence to generate unique keys, and you will not insert the data from the source tables into the corresponding new tables, then you might want to edit the START WITH value in the installation script file. You can edit the installation script file in the SQL Worksheet or any text editor.

Editing Installation Script Files that Create Triggers

If your application has a BEFORE INSERT trigger on a source table, and you will insert the data from that source table into the corresponding new table, then you must decide if you want the trigger to fire before each INSERT statement in the installation script file inserts data into the new table.

For example, in the sample application, NEW_EVALUATION_TRIGGER fires before a row is inserted into the EVALUATIONS table, and generates the unique number for the primary key of that row, using EVALUATIONS_SEQ. The trigger fires once for each row affected by the triggering INSERT statement. (NEW_EVALUATION_TRIGGER is created in "Tutorial: Creating a Trigger that Generates a Primary Key for a Row Before It Is Inserted".)

The source EVALUATIONS table is populated with primary keys. If you do not want the installation script to put new primary key values in the new EVALUATIONS table, then you must edit the CREATE TRIGGER statement in the installation script file as shown in bold:

CREATE OR REPLACE
TRIGGER NEW_EVALUATION_TRIGGER
BEFORE INSERT ON EVALUATIONS
FOR EACH ROW
BEGIN
  IF :NEW.evaluation_id IS NULL THEN
    :NEW.evaluation_id := evaluations_seq.NEXTVAL
  END IF;
END;

Also, check the current value of the sequence. If it not is greater than the maximum value in the primary key column, make it greater.

You can edit the installation script file in the SQL Worksheet or any text editor.

Two alternatives to editing the installation script file are:

• Change the trigger definition in the source file and then re-create the installation script file.

  For information about changing triggers, see "Changing Triggers".

• Disable the trigger before running the data installation script file, and then re-enable it afterward.

  For information about disabling and enabling triggers, see "Disabling and Enabling Triggers".

Tutorial: Creating an Installation Script File for the Sequence and Tables

This tutorial shows how to use the SQL Developer tool Database Export to create an installation script file for the tables and sequence of the sample application, including the constraints, indexes, and triggers associated with the tables.

To create an installation script file for the tables and sequence:

1. If you have not done so, create the directory C:\my_exports.

2. In the SQL Developer window, click the menu Tools.

   A drop-down menu appears.

3. From drop-down menu, select Database Export.

   The Source/Destination window opens.

4. In the Source/Destination window:

   1. In the File field, type: C:\my_exports\2day_tables.sql.

   2. From the Connections drop-down menu, select hr_conn.

   3. Accept the default DDL Options, Terminator and Pretty Print.

   4. Click Next.

   The Types to Export window appears.

5. In the Types to Export window:

   1. Deselect all check boxes except Tables, Sequences, Indexes, Constraints, and Triggers.

   2. Click Next.

   The Specify Objects window appears.

6. In the Specify Objects window:

   1. In the drop-down menu with the value HR, accept that value.

   2. In the drop-down menu with the value All, select the value TABLE.

   3. Click Go.

      A list of the tables in schema HR appears.

   4. Select HR.PERFORMANCE_PARTS, HR.EVALUATIONS, HR.SCORES, and HR.EVALUATIONS_LOG.

   5. Click >.

      The selected tables move from the original column to the other column.

   6. In the drop-down menu with the value TABLE, select the value SEQUENCE.

   7. Click Go.

      A list of the sequences in schema HR appears.

   8. Select HR.EVALUATIONS_SEQ.

   9. Click >.

      HR.EVALUATIONS_SEQ moves from the original column to the other column.

   10. (Optional) In the drop-down menu with the value SEQUENCE, select the value All.

       HR.PERFORMANCE_PARTS, HR.EVALUATIONS, HR.SCORES, HR.EVALUATIONS_LOG and HR.EVALUATIONS_SEQ show.

   11. Click Next.

   The Export Summary window appears.

7. In the Export Summary window, click Finish.

   The Exporting window opens, showing that exporting is occurring. When exporting is complete, the Exporting window closes, and the SQL Worksheet shows the contents of the file C:\my_exports\2day_tables.sql.

8. In the installation script file, C:\my_exports\2day_tables.sql, check that referenced objects are created before their dependent objects:

   • The sequence EVALUATIONS_SEQ must be created before the table EVALUATIONS, because EVALUATIONS has a trigger, NEW_EVALUATION_TRIGGER, that uses EVALUATIONS_SEQ.

   • The table EVALUATIONS_LOG must be created before the table EVALUATIONS, because EVALUATIONS has a trigger, EVAL_CHANGE_TRIGGER, that uses EVALUATIONS_LOG.

   • The tables EVALUATIONS and PERFORMANCE_PARTS must be created before the table SCORES, because SCORES has foreign keys to both EVALUATIONS and PERFORMANCE_PARTS.

   If necessary, edit the file in the SQL Worksheet or any text editor.

Tutorial: Creating an Installation Script File for the Package

This tutorial shows how to use the SQL Developer tool Database Export to create an installation script file for the package (specification and body) of the sample application.

To create an installation script file for the package:

1. If you have not done so, create the directory C:\my_exports.

2. In the SQL Developer window, click the menu Tools.

   A drop-down menu appears.

3. From drop-down menu, select Database Export.

   The Source/Destination window opens.

4. In the Source/Destination window:

   1. In the File field, type: C:\my_exports\2day_package.sql.

   2. From the Connections drop-down menu, select hr_conn.

   3. Accept the default DDL Options, Terminator and Pretty Print.

   4. Click Next.

   The Types to Export window appears.

5. In the Types to Export window:

   1. Deselect all check boxes except Package Spec and Package Body.

   2. Click Next.

   The Specify Objects window appears.

6. In the Specify Objects window:

   1. In the drop-down menu with the value HR, accept that value.

   2. In the drop-down menu with the value All, accept that value.

   3. Click Go.

      A list of the packages and package bodies in schema HR appears.

   4. If the only items listed are the package HR.EMP_EVAL and the package body HR.EMP_EVAL, click >>; otherwise, select those two items from the list and click >.

      The package HR.EMP_EVAL and the package body HR.EMP_EVAL move from the original column to the other column.

   5. Click Next.

   The Export Summary window appears.

7. In the Export Summary window, click Finish.

   The Exporting window opens, showing that exporting is occurring. When exporting is complete, the Exporting window closes, and the SQL Worksheet shows the contents of the installation script file that you specified in step 4.

Tutorial: Creating an Installation Script File for the Synonym and View

This tutorial shows how to use the SQL Developer tool Database Export to create an installation script file for the synonym and view of the sample application.

To create an installation script file for the synonym and view:

1. If you have not done so, create the directory C:\my_exports.

2. In the SQL Developer window, click the menu Tools.

   A drop-down menu appears.

3. From drop-down menu, select Database Export.

   The Source/Destination window opens.

4. In the Source/Destination window:

   1. In the File field, type: C:\my_exports\2day_other.sql.

   2. From the Connections drop-down menu, select hr_conn.

   3. Accept the default DDL Options, Terminator and Pretty Print.

   4. Click Next.

   The Types to Export window appears.

5. In the Types to Export window:

   1. Deselect all check boxes except Views and Synonyms.

   2. Click Next.

   The Specify Objects window appears.

6. In the Specify Objects window:

   1. In the drop-down menu with the value HR, accept that value.

   2. In the drop-down menu with the value All, select the value VIEW.

   3. Click Go.

      A list of the views in schema HR appears.

   4. Select HR.EMP_LOCATIONS.

   5. Click >.

      HR.EMP_LOCATIONS moves from the original column to the other column.

   6. In the drop-down menu with the value VIEW, select the value SYNONYM.

   7. Click Go.

      A list of the synonyms in schema HR appears.

   8. Select HR.EMP.

   9. Click >.

      HR.EMP moves from the original column to the other column.

   10. (Optional) In the drop-down menu with the value SYNONYM, select the value All.

       HR.EMP_LOCATIONS and HR.EMP show.

   11. Click Next.

   The Export Summary window appears.

7. In the Export Summary window, click Finish.

   The Exporting window opens, showing that exporting is occurring. When exporting is complete, the Exporting window closes, and the SQL Worksheet shows the contents of the file C:\my_exports\2day_other.sql.

Tutorial: Creating an Installation Script File for the Data

This tutorial shows how to use the SQL Developer tool Database Export to create an installation script file for the data of the sample application. If you followed the instructions in this document, you added data only to the table PERFORMANCE_PARTS.

To create an installation script file for the data:

1. If you have not done so, create the directory C:\my_exports.

2. In the SQL Developer window, click the menu Tools.

   A drop-down menu appears.

3. From drop-down menu, select Database Export.

   The Source/Destination window opens.

4. In the Source/Destination window:

   1. In the File field, type: C:\my_exports\2day_data.sql.

   2. From the Connections drop-down menu, select hr_conn.

   3. Accept the default DDL Options, Terminator and Pretty Print.

   4. Click Next.

   The Types to Export window appears.

5. In the Types to Export window:

   1. Deselect all check boxes except Data.

   2. Click Next.

   The Specify Data window appears.

6. In the Specify Data window:

   1. In the drop-down menu with the value HR, accept that value.

   2. Click Go.

      A list of the tables in schema HR appears.

   3. Select HR.PERFORMANCE_PARTS.

   4. Click >.

      HR.PERFORMANCE_PARTS moves from the original column to the other column.

   5. Click Next.

   The Export Summary window appears.

7. In the Export Summary window, click Finish.

   The Exporting window opens, showing that exporting is occurring. When exporting is complete, the Exporting window closes, and the SQL Worksheet shows the contents of the file C:\my_exports\2day_data.sql.