Skip Headers
Oracle® Database 2 Day Developer's Guide
11g Release 2 (11.2)

Part Number E10766-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master 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 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, INSERT statements, or both. When you run your installation script files, 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 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 Installation Script Files

This topic explains how to use SQL Developer to create an installation script file, 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 as the user HR from SQL Developer. For instructions, see "Connecting to Oracle Database as 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 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.

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

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

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

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

Installing the Sample Application

To install the sample application, you run the installation script files that you created in the tutorials in "Creating Installation Script Files", in this order:

  1. 2day_tables.sql

  2. 2day_package.sql

  3. 2day_other.sql

  4. 2day_data.sql

You can either run the files one at a time, or you can create and run a master SQL script file that runs them in order.

The following master SQL script runs the files in order, commits the changes, and writes server messages to a log file:

spool C:\my_exports\create_log.txt
@C:\my_exports\2day_tables.sql
@C:\my_exports\2day_package.sql
@C:\my_exports\2day_other.sql
@C:\my_exports\2day_data.sql
commit;
spool off

The SQL*Plus command @file_name.sql runs a file.

Typically, you run the master script file in SQL*Plus. However, if the master script specifies the full path names of the individual files (as in the preceding example), you can run it in SQL Developer.

The following procedure uses SQL Developer to install the sample application by running the installation script files one at a time, in order.

Note:

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

To install the sample application using SQL Developer:

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

    For instructions, see "Connecting to Oracle Database as User HR from SQL Developer". For Connection Name, enter a name other than hr_conn (for example, hr_conn_2).

  2. In the navigation frame, click the tab Files.

    The Files pane appears, showing the directories on your computer.

  3. Navigate to the directory C:\my_exports.

  4. Expand my_exports.

    The list of installation script files appears.

  5. Right-click 2day_tables.sql.

    A list of choices appears.

  6. Select Open.

    In the SQL Worksheet, a new 2day_tables.sql pane appears, showing the content of the 2day_tables.sql file.

  7. Click the icon Run Script.

    The Select Connection window opens.

  8. For Connection, on the drop-down menu, select the connection to the deployment environment (created in step 1).

  9. Click OK.

    The result of each statement in 2day_tables.sql is displayed in the Script Output pane.

  10. Click the icon Clear.

    The title of the 2day_tables.sql pane is now in italic font, indicating that the changes have not been committed, and the SQL Worksheet is blank.

  11. Repeat steps 5 through 10 for 2day_package.sql.

  12. Repeat steps 5 through 10 for 2day_other.sql.

  13. Repeat steps 5 through 10 for 2day_data.sql.

  14. Click the icon Commit.

    The changes are committed.

When you are sure that the individual installation script files run without errors, you can create a master SQL script (.sql) file that runs them in order, commits the changes, and writes the results to a log file. For example:

spool C:\my_exports\create_log.txt
@C:\my_exports\2day_tables.sql
@C:\my_exports\2day_package.sql
@C:\my_exports\2day_other.sql
@C:\my_exports\2day_data.sql
commit;
spool off

Typically, you run the master file in SQL*Plus. However, if the master file specifies the full path names of the individual files (as in the preceding example), you can open and run it in SQL Developer.

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