Skip Headers
Oracle® Database Express Edition 2 Day Developer's Guide
11g Release 2 (11.2)
Part Number E18147-07
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us
Go to previous page
Previous		 Go to next page
Next
PDF · Mobi · ePub

10 Deploying an Oracle Database Express Edition Application

This chapter contains the following topics:

About Deployment Environments

Deployment is installing your application in one or more environments where other users can run it.

The schema in which you develop your application is called the development environment. (The development environment for the sample application is the sample schema HR.) The other environments in which you deploy your application are called deployment environments. These environments might exist in your organization; if not, you can create them.

The first deployment environment is the test environment. In the test environment, you can thoroughly test the functionality of the application, determine whether it is packaged correctly, and fix any problems before deploying it in the production environment.

You might also deploy your application to an education environment, either before or after deploying it to the production environment. An education environment provides a place for users to practice running the application without affecting other environments.

About Installation Script Files

To deploy an application, you run one or more installation script files. If these files do not exist, you can create them, with SQL Developer or any text editor.

An installation script file is an executable file (.sql file) that contains an installation script. An installation script is composed ot DDL statements and (optionally) INSERT statements. When you run an installation script file, the DDL statements create the schema objects of your application in the deployment environment, and the INSERT statements insert the data from the tables in your development environment (the source tables) into the corresponding tables in the deployment environment (the new tables).

Topics:

About DDL Statements and Schema Object Dependencies

When you run an installation script file, its DDL statements create the schema objects of your application in the deployment environment. To create installation script files correctly, and to run multiple installation script files 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.

See Also:

About INSERT Statements and Constraints

When you run an installation script file that contains INSERT statements, the 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 Express Edition (Oracle Database XE) 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');

See Also:

Creating Installation Script Files

This topic explains how to use SQL Developer to create installation script files, when and how to edit installation script files that create sequences and triggers, and how to create installation script files for the schema objects and data of the sample application.

The tutorials in this topic assume that you created the objects in the sample schema HR, using the instructions in this document, and are deploying the sample application in another standard HR schema.

Note:

To do the tutorials in this document, you must be connected to Oracle Database XE as the user HR from SQL Developer.

Topics:

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.

Note:

In the following procedure, you might have to enlarge the SQL Developer windows to see all fields and options.

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 XE installation directory (for example, C:\my_exports).

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

    A menu appears.

  3. From menu, select Database Export.

    The Source/Destination window opens.

  4. In the Source/Destination window:

    1. In the Connection field, select from the menu your connection to the development environment.

    2. Select the desired Export DDL options (and deselect any selected undesired options).

      Note:

      Do not deselect Terminator, or the installation script file will fail.

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

    3. If you do not want the installation script to export data, deselect Export Data.

    4. In the Save As field, accept the default Single File and 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.

    5. Click Next.

      The Types to Export window appears, listing the types of objects that you can export. To the left of each object type 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. Click Next.

      The Specify Objects window appears.

  6. In the Specify Objects window:

    1. Click More.

      More fields appear, including the Schema and Type fields.

    2. In the Schema field, select your schema from the menu.

    3. In the Type field, select from the menu either ALL OBJECTS or a specific object type (for example, TABLE).

    4. Click Lookup.

      A list of objects appears in the left frame. If the value of the Type field is ALL OBJECTS, then the list contains all objects in the selected schema. If the value of the Type field is a specific object type, then the list contains all objects of that type in the selected schema.

    5. Move the objects that you want to export from the left frame to the right frame:

      To move all objects, click >>. (To move all objects back, click <<.)

      To move selected objects, select them and then click >. (To move selected objects back, select them and click <.)

    6. (Optional) Repeat steps 3 through 5 for other object types.

    7. Click Next.

      If you deselected Export Data in the Source/Destination window, then the Export Summary window appears—go to step 3.

      If you did not deselect Export Data in the Source/Destination window, then the Specify Data window appears. The lower frame lists the objects that you specified in the Specify Objects window.

  7. In the Specify Data window:

    1. Move the objects whose data you do not want to export from the lower frame to the upper frame:

      To move all objects, click the double upward arrow icon. (To move all objects back, click the double downward arrow icon.)

      To move selected objects, select them and then click the single upward arrow icon.

    2. Click Next.

      The Export Summary window appears.

  8. 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 the Source/Destination window.

  9. 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.

See Also:

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.

See Also:

"Tutorial: Creating a Sequence"

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.

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".

See Also:

"Creating Triggers"

Tutorial: Creating an Installation Script File for the Sample Application

This tutorial shows how to use the SQL Developer tool Database Export to create a single installation script file for the sample application.

Note:

In the following procedure, you might have to enlarge the SQL Developer windows to see all fields and options.

To create an installation script file for the sample application:

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

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

    A menu appears.

  3. From menu, select Database Export.

    The Source/Destination window opens.

  4. In the Source/Destination window:

    1. In the Connection field, select hr_conn from the menu.

    2. Accept the default Export DDL options.

    3. Accept the default Export Data option (selected).

    4. In the Save As field, accept the default Single File and type the full path name of the installation script file: C:\my_exports\hr_export.sql.

    5. Click Next.

      The Types to Export window appears. All types are selected.

  5. In the Types to Export window:

    1. Deselect the check boxes for all types except those that the sample application uses, which are: Tables, Views, Indexes, Triggers, Constraints, Sequences, Synonyms, Package Spec, and Package Body.

    2. Click Next.

      The Specify Objects window appears.

  6. In the Specify Objects window:

    1. Click More.

      More fields appear, including the Schema and Type fields.

    2. In the Schema field, select HR from the menu.

    3. In the Type field, select TABLE from the menu.

    4. Click Lookup.

      The names of the tables in the HR schema appear in the left frame.

    5. In the left frame, select the names of the tables that you created for the sample application—EVALUATIONS, EVALUATIONS_LOG, PERFORMANCE_PARTS, and SCORES.

    6. Click >>.

      The selected tables move to the right frame.

    7. In the Type field, select SEQUENCE from the menu.

    8. Click Lookup.

      The names of the sequences in the HR schema appear in the left frame.

    9. In the left frame, select the name of the sequence that you created for the sample application—EVALUATIONS_SEQ.

    10. Click >>.

      The selected sequence moves to the right frame.

    11. In the Type field, select TRIGGER from the menu.

    12. Click Lookup.

      The names of the triggers in the HR schema appear in the left frame.

    13. In the left frame, select the names of the triggers that you created for the sample application—EVAL_CHANGE_TRIGGER and NEW_EVALUATION_TRIGGER.

    14. Click >>.

      The selected triggers move to the right frame.

    15. In the Type field, select PACKAGE from the menu.

    16. Click Lookup.

      The names of the packages in the HR schema appear in the left frame.

    17. In the left frame, select the name of the package that you created for the sample application—EMP_EVAL.

    18. Click >>.

      The selected package moves to the right frame, where it appears as HR.EMP_EVAL.

    19. In the Type field, select PACKAGE BODY from the menu.

    20. Click Lookup.

      The names of the package bodies in the HR schema appear in the left frame.

    21. In the left frame, select EMP_EVAL Body.

    22. Click >>.

      The selected package body moves to the right frame, where it appears as HR.EMP_EVAL.

    23. In the Type field, select SYNONYM from the menu.

    24. Click Lookup.

      The names of the synonyms in the HR schema appear in the left frame.

    25. In the left frame, select the name of the synonym that you created for the sample application—EMP.

    26. Click >>.

      The selected synonym moves to the right frame.

    27. In the Type field, select VIEW from the menu.

    28. Click Lookup.

      The names of the views in the HR schema appear in the left frame.

    29. In the left frame, select the name of the view that you created for the sample application—EMP_LOCATIONS.

    30. Click >>.

      The selected view moves to the right frame.

    31. Click Next.

      The Specify Data window appears. The lower frame lists the objects that you specified in the Specify Objects window.

  7. In the Specify Data window:

    1. In the lower frame, select every object except PERFORMANCE_PARTS (the only sample application table to which you added data).

    2. Click the upward arrow icon.

      The selected objects move from the lower frame to the upper frame, indicating that their data is not to be exported.

    3. Click Next.

      The Export Summary window appears.

  8. 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 C:\my_exports\hr_export.sql.

  9. 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.

See Also:

Installing the Sample Application

Note:

The deployment environment must be different from the development environment, and is assumed to be another standard HR schema.

If you created a single installation script file for the sample application, you can install the sample application by connecting to the deployment environment as the user HR and then running the installation script in either SQL*Plus or in the SQL Worksheet of SQL Developer. The command for running the installation script has this syntax:

@full_path_name_of_installation_script_file

To run an installation script file in SQL Developer:

  1. If necessary, create a connection to the deployment environment.

    For Connection Name, type a name that is not the name of the connection to the development environment.

  2. Connect to Oracle Database XE as user HR in the deployment environment.

    For Connection Name, enter the name of the connection to the deployment environment.

    A new pane appears. On its tab is the name of the connection to the deployment environment. The pane has two subpanes, SQL Worksheet and Query Builder.

  3. In the SQL Worksheet pane, type the command for running the installation script:

    @full_path_name_of_installation_script_file

    For example:

    @C:\my_exports\hr_export.sql

  4. Click the icon Run Script.

    The script runs. Its output appears in the Script Output pane, under the SQL Worksheet pane.

    In the Connections pane, if you expand the connection to the deployment environment, and then expand the type of each object that the sample application uses, you see the objects of the sample application.

See Also:

Checking the Validity of an Installation

After installing your application in a deployment environment, you can check its validity in the following ways in SQL Developer:

See Also:

Oracle Database SQL Developer User's Guide for more information about SQL Developer reports

Archiving the Installation Script Files

After verifying that the installation of your application is valid, you might want to archive your installation script file or files in a source code control system. Before doing so, add comments to each file, documenting its creation date and purpose. If you ever must deploy the same application to another environment, you can use these archived files.

See Also:

Oracle Database Utilities for information about Oracle Data Pump, which enables very high-speed movement of data and metadata from one database to another
Go to previous page
Previous		 Go to next page
Next
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Feedback page
Contact Us