| Oracle® Database 2 Day Developer's Guide, 11g Release 1 (11.1) Part Number B28843-01 |
|
|
View PDF |
| Oracle® Database 2 Day Developer's Guide, 11g Release 1 (11.1) Part Number B28843-01 |
|
|
View PDF |
This chapter describes how to package and install the database objects that support an application. For examples, it uses objects that you created if you followed the instructions earlier in this guide. It takes you through the process of gathering object definitions and data to deploy on another system. In a real-world environment, the exercise would probably not be simple as laid out in this guide, but the steps and considerations discussed would be the same.
Oracle recommends that you use a consistent prefix for all object names. This makes the objects easy to identify. It groups them together in the SQL Developer Connections navigator display and when you are reviewing SQL Developer reports and performance queries against the Oracle Database data dictionary.
This chapter contains the following sections:
Deployment of an application is typically not complete unless the database objects that support the application are also deployed. You can deploy these objects by creating scripts that create both the database objects and any necessary data, such as seed data for lookup tables. The database objects include tables, views, functions, packages, and others that you created to implement the application logic.
When you deploy an application for Oracle Database, you should create the following system environments:
Step 1: Create a Test Environment
You should always have a test environment, for the initial deployment, for thorough testing of the application before it is deployed in any other environment, and perhaps also for training of application users.
Testing checks both the functionality of your application and whether you have packaged it correctly. If you have missed an object that your application depends upon, you can catch it during testing rather than after it is deployed to actual users in the production environment.
Step 2: Create a Quality Assurance (QA) Environment
If the application is sufficiently complex and if you have the resources, create a QA environment in which changes to the system can be checked in a rigorous manner.
Step 3: Create an Education Environment
An education environment enables you to provide training and practice, for internal or external users, without affecting any of the other environments. You can create the education environment before or after the production environment, and you can update it independently of updates to other environments.
Step 4: Create the Production Environment
The production environment contains the actual data and database objects for the normal operation of your organization. Test any objects in the test environment before you move them into the production environment.
Regardless of how the number of environments to which you deploy, the deployment process is the same.
Before you deploy an application, you must understand the dependencies between the database objects. You must create the objects in the correct order, so that if any objects depend on other objects, the dependent objects exist in each case. If a dependent object is missing, an error or problem such as the following will occur:
The CREATE statement will fail, such as with constraints.
The object will be created but left in an invalid state, such as with functions, procedures, and packages.
To deploy data, you can take one of the following approaches with the data for each table, depending on how confident you are of the validity of the data:
Load the data without worrying about any possible validity issues.
You can use this approach if you are confident you are of the following: the data will not violate any constraint, no duplicate values exist in primary key and unique key columns, all foreign key references will already exist, and the data in any column governed by a check constraint meets that constraint. For example, if you are simply loading lookup data from your development environment, and load your data in a proper order, the constraints will not need to be disabled because the constraints will not be violated.
Disable all constraints before you load the data, and then enable the constraints after the data loading is complete.
If you want to load data without needing to sequence it (such as when you have many tables to load and much dependent data) or if you will be loading data from an outside source (such as an older application or from a flat file or spreadsheet), you should disable the constraints before loading the data.
If any data fails to meet the constraints, that constraint will not be enabled and you will need to correct the data and try again.
The following is a general guideline for the order in which to run the installation scripts for different types of database objects:
Package specifications
Tables (with constraints and indexes) in proper order
Sequences (because they are most often used by triggers)
Triggers
Synonyms
Views (because they may reference functions, procedures, or synonyms)
Package bodies
Data (optionally disabling all constraints before loading the data and re-enabling them afterwards)
Package specifications are listed first because they will always be valid and other objects might refer to them. Package bodies should be the last object type created because they will probably refer to other object types. Because of dependency issues, you are encouraged to put functions and procedures into packages.
If you followed the instructions in other sections of this guide, you created objects in the sample Oracle HR schema. This section assumes that you are deploying them to another standard HR schema.
In the tables you have created, scores has foreign keys to both performance_parts and evaluations. This means that you cannot create those foreign keys until the primary keys for the referenced tables are created. You will first create the evaluations table and constraints, then the performance_parts table, and then the scores table. You will have just one script for tables, sequences, and triggers. This will minimize the manual editing for this exercise. You will also create just create one script for the function and the package because you only have one of each. The last script you will create will be for the synonym and view.
This is obviously a simplistic deployment example. For real-world applications, you will need to consult with the database designer and map out the order for creating the objects. If you have a diagram of the design, such as an Entity Relationship Diagram, it can be very useful during this phase
This section demonstrates how to export the database objects.
If you have maintained scripts to create your database objects, you can use those. If you have not maintained scripts, you should generate the data definition language (DDL) statements for each object based on its definition in the database. To generate the DDL for your database objects, you can use Oracle SQL Developer, specifically the Export DDL (and Data) feature. This feature generates DDL statements to create specified objects and types of objects, and it can generate INSERT statements to insert exported data into the new tables that are created.
To export DDL statements and table data:
Create a directory in which to export the DDL statements and table data.
Create this directory separate from the Oracle installation directory, for example, C:\my_exports.
From the SQL Developer main menu, select Tools, then Export DDL (and Data).
Click the Options tab (it should be selected by default).
In the File field, specify the name and location of the export file to be created that will contain the SQL statements to create the objects and insert data. For example: C:\my_exports\hr_export.sql
Under Options, select from the following options to specify objects within object types, or to specify options for the generated SQL statements.
Show Schema: If this option is checked, the schema name is included in CREATE statements. If this option is not checked, the schema name is not included in CREATE statements, which is convenient if you want to re-create the exported objects under a schema that has a different name.
Storage: If this option is checked, any STORAGE clauses in definitions of the database objects are preserved in the exported DDL statements. If you do not want to use the current storage definitions (for example, if you will re-create the objects in a different system environment), uncheck this option.
Terminator: If this option is checked, a line terminator character is inserted at the end of each line.
Pretty Print: If this option is checked, the statements are attractively formatted in the output file, and the size of the file will be larger than it would otherwise be.
Include BYTE Keyword: If this option is checked, column length specifications refer to bytes; if this option is not checked, column length specifications refer to characters.
Add Force to Views: If this option is checked, the FORCE option is added to any CREATE VIEW statements, causing each view to be created even if it contains errors.
Constraints as Alters: If this option is checked, constraints for each table are defined in separate ALTER TABLE statements instead of in the CREATE TABLE statement.
Export Data: If this option is checked, statements are included to insert the data for an exported table or view. If this option is not checked, statements are not included to insert the data for an exported table or view; that is, only the DDL statements are included.
Include Drop Statements: If this option is checked, DROP statements are included before the CREATE statements, to delete any existing objects with the same names.
Click the Objects tab.
In the Objects tab:
Select hr_conn from the Connection list.
Under Objects, select All, and then select All My Objects to display the available objects in the hr_conn connection.
Ensure that the types of objects (Constraints, Database Links, Functions, and so on) to be exported are checked. If you want INSERT statements created to insert table data, ensure that Data is checked. If you want certain object types or the table data not to be exported, uncheck the appropriate options.
Click Apply to generate the script.
Sequences and triggers require special consideration when you export them. For sequences, the DDL generated will start your sequence relative to the current value. If you have a sequence that is used to populate a primary key and the data for that table will be loaded, keep the sequence as it is. However, if you will not be loading data, you might want to edit your script, after creation, to reset the START WITH value.
For triggers, if you have a before-insert trigger on a table and plan to load data, you must examine that trigger and decide if you want the actions specified in the trigger to occur. For example, primary key values are often populated in triggers, and if you want to preserve the primary key from the INSERT statement, ensure that your trigger only populates the primary key value if it is null, as in the following example:
IF :new.evaluation_id IS NULL
THEN SELECT evaluations_seq.Nextval
INTO :new.evaluation_id
FROM dual;
END IF;
However, if the trigger is not written as in the preceding example, you must either rewrite your trigger, or disable the trigger before loading the data and then enable it after the data load is complete. Also check that the current value of the sequence is greater than the maximum value in the primary key column.
If any auditing columns (such as CREATED_ON or CREATED_BY) are populated by a trigger, you must preserve the data from your source table by having the trigger set a new value only if the current value is null, as in the following example:
if :new.created_on is null then :new.created_on := sysdate; end if;
The tables that you created using the instructions in this guide did not use any triggers, so you will not need to edit the code generated if you follow the instructions in the related topics for generating scripts.
Generate a script to create the sequence and tables that you created if you followed the instructions earlier in this guide. This script will also create any necessary constraints, indexes, and triggers.
To generate a script for creating the sequence and tables:
Create a directory in which to export the DDL statements and table data.
Create this directory separate from the Oracle installation directory, for example, C:\my_exports.
From the SQL Developer main menu, select Tools, then Export DDL (and Data).
In the Export dialog box, specify the name and location of the to be created that will contain the SQL statements to create the objects and insert data. For example: C:\my_exports\2day_tables.sql
Under Options, specify the following options for creating the tables for this example:
Show Schema: Uncheck. In this example, if you check this option, it might be inappropriate because the schema name in your test or production environment might not be the same as that in your development environment.
Storage: Uncheck. If you have specified specific storage clauses for your tables that you want to preserve, check this option; however, if you have not specified storage clauses or if you have specified storage clauses that are specific to your development or test environment (that will be different in the production environment), uncheck this option.
Terminator: Check. This is necessary because you want to be able run the resulting script.
Pretty Print: Check. If you check this option, the output is more readable.
Include BYTE Keyword: Uncheck, unless you are working with a multibyte character set, in which case you should check this option.
Add Force to Views: Uncheck, because views will be added in another script for this example.
Constraints as Alters: Check, to have constraints added in separate SQL statements after each CREATE TABLE.
Export Data: Uncheck, because you will export the data separately for this example.
Include Drop Statements: Uncheck. If you want these objects to replace any existing objects with the same name, you can include this. However, a better practice is to have a separate drop script that can be run to remove an older version of your objects before creation. This avoids the chance of accidentally removing an object you did not intend to drop.
Click the Objects tab.
In the Objects tab:
Select hr_conn for the database connection to be used.
For the type of objects to be exported, expand All My Objects. Then expand Sequences, and select EVALUATIONS_SEQ. Expand Tables, and select EVALUATIONS, PERFORMANCE_PARTS and SCORES. Uncheck the other object types.
Click Apply to generate the script.
Object definitions are added to the file in alphabetic order by object type. The sequence will be the first object created, which is good because it is referenced by the trigger on evaluations. The table dependencies require that evaluations be created first, then performance_parts, and then scores. These happen to be in alphabetical order, so you do not need to make any changes. However, if you did need to make changes, you could edit the generated script with any text editor or by opening it within SQL Developer.
Generate a script to create the package (including the function) that you created if you followed the instructions earlier in this guide.
To generate a script for creating the PL/SQL objects:
From the SQL Developer main menu, select Tools, then Export DDL (and Data).
In the Export dialog box, specify the name and location of the to be created that will contain the SQL statements to create the objects and insert data. For example: C:\my_exports\2day_plsql.sql
Under Options, specify the following options for creating the tables for this example:
Show Schema: Uncheck. In this example, if you check this option, it might be inappropriate because the schema name in your test or production environment might not be the same as that in your development environment.
Storage: Uncheck, because this does not apply here.
Terminator: Check. This is necessary because you want to be able run the resulting script.
Pretty Print: Check. If you check this option, the output is more readable.
Include BYTE Keyword: Uncheck, unless you are working with a multibyte character set, in which case you should check this option.
Add Force to Views: Uncheck, because this does not apply here.
Constraints as Alters: Uncheck, because this does not apply here.
Export Data: Uncheck, because you will export the data separately for this example.
Include Drop Statements: Uncheck. If you want these objects to replace any existing objects with the same name, you can include this. However, a better practice is to have a separate drop script that can be run to remove an older version of your objects before creation. This avoids the chance of accidentally removing an object you did not intend to drop.
Click the Objects tab.
In the Objects tab:
Select hr_conn for the database connection to be used.
For the type of objects to be exported, expand All My Objects. Expand Functions, and then select CALCULATE_SCORE. Expand Packages, and then select EMP_EVAL. Uncheck the other object types.
Click Apply to generate the script.
Generate a script to create the synonym and the view that you created if you followed the instructions earlier in this guide.
To generate a script for creating a synonym and a view:
From the SQL Developer main menu, select Tools, then Export DDL (and Data).
In the Export dialog box, specify the name and location of the to be created that will contain the SQL statements to create the objects and insert data. For example: C:\my_exports\2day_other.sql
Under Options, specify the following options for creating the tables for this example:
Show Schema: Uncheck. In this example, if you check this option, it might be inappropriate because the schema name in your test or production environment might not be the same as that in your development environment.
Storage: Uncheck, because this does not apply here.
Terminator: Check. This is necessary because you want to be able run the resulting script.
Pretty Print: Check. If you check this option, the output is more readable.
Include BYTE Keyword: Uncheck, unless you are working with a multibyte character set, in which case you should check this option.
Add Force to Views: Check. This will cause your views will be created, even if they are invalid. If any views are invalid, you can correct problems later and then compile these views.
Constraints as Alters: Uncheck, because this does not apply here.
Export Data: Uncheck, because you will export the data separately for this example.
Include Drop Statements: Uncheck. If you want these objects to replace any existing objects with the same name, you can include this. However, a better practice is to have a separate drop script that can be run to remove an older version of your objects before creation. This avoids the chance of accidentally removing an object you did not intend to drop.
Click the Objects tab.
In the Objects tab:
Select hr_conn for the database connection to be used.
For the type of objects to be exported, expand All My Objects. Expand Synonyms, and select POSITIONS. Expand Views, and select EMP_LOCATION. Uncheck the other object types.
Click Apply to generate the script.
After you have generated the scripts to create the tables, PL/SQL objects, synonym, and view, you can generate the script that retrieves any data that you want to bring to the target database.
To export the data, you capture the existing table data for insertion into the deployed tables. As mentioned in Planning for Deployment, you can either insert data into your target schema if you are confident that all dependent data exists and there are no validity problems, or you can disable constraints and then enable them again after loading the data.
If you choose to disable and then enable the constraints, then you have the following options:
Review the tables and constraints using SQL Developer, and disable and enable them one at a time.
Create a copy of the 2day_tables.sql file, find the name of each constraint, and edit the file so that it contains the SQL statements to 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 the following 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');
If you followed the instructions in this guide, the only table to which you added data was performance_parts. Use the Export DDL (and Data) feature to export data, but this method outputs the DDL along with the data. You can also select a table from within the tree under Connections in the Connections navigator, and then right-click and select Export Data, and then INSERT. This option enables you to restrict your export to just select columns and to include a WHERE clause to control which data is exported. This is the method you will use.
To create INSERT statements for the data:
In the Connections navigator in SQL Developer, expand the database connection that you used for generating scripts (hr_conn) for the database objects.
Expand Tables under the hr_conn connection.
Right-click the PERFORMANCE_PARTS table name and select Export Data, and then INSERT.
In the Export dialog box, in the File field, enter C:\my_exports\2day_data.sql to specify the export file name.
Click Apply.
If you need to create INSERT statements for another table, rather than exporting the data for the other tables to separate files, you can also export the data to the Clipboard and then paste it into your first file. To do this, specify Clipboard for Output, which causes the statements to be placed on the Clipboard, so that you can add them to a file.
At this stage, you now have all the script files necessary to create the objects in another schema. These scripts must be executed in the following order (the order in which you created them in this exercise), to ensure that the tables exist before you load the data:
2day_tables.sql
2day_plsql.sql
2day_other.sql
2day_data.sql
If you want, you can create a master script to execute these scripts in the proper order and log the results to a file. This kind of master script is typically run using SQL*Plus. The master script for this exercise looks like the following example:
spool my_dir/create_log.txt @my_dir/2day_tables.sql @my_dir/2day_plsql.sql @my_dir/2day_other.sql @my_dir/2day_data.sql commit; spool off
You can also use SQL Developer to execute the scripts that you created. If a master script fully specifies the file path and name (for example, C:\my_dir\2day_tables.sql), you can open and execute the master script. Alternatively, you can open and execute each script individually.
To run installation scripts in SQL Developer:
Right-click in the SQL Worksheet window and select Open File.
Find and open the 2day_tables.sql file, which is located in the C:\my_exports directory.
You now see the DDL statements for creating the tables. In the next step you run these statements as a script, because you want to monitor the success or failure of each statement.
Click the Run Script icon, or press F5 on your keyboard.
The results of each statement are displayed in the Script Output pane. The results should show the successful execution of each statement.
Click the Clear icon to erase the contents of the SQL Worksheet.
Perform the preceding three steps (finding and opening a file, running its contents as a script, and clearing the SQL Worksheet entry area) for each of the following additional script files:
2day_plsql.sql
2day_other.sql
Find and open the 2day_data.sql file.
You now see the DDL statements for inserting the table data. In the next step you run these statements as a script, because you want to monitor the success or failure of each statement.
Click the Run Script icon (or press F5).
Note that in Oracle, DML statements are not automatically committed, which means that you can roll back any INSERT, UPDATE, or DELETE statements. To store the data in the database, you must commit the transaction, which you will do in the next step.
Click the Commit icon to commit the data to the database.
See Also:
After you have created all the database objects that support an application, you can access the definitions of the new objects, using SQL Developer. You can also use SQL Developer reports to see information to help you determine whether or not the installation is valid. These reports include the following:
All Objects: For each object, lists the owner, name, type (table, view, index, and so on), status (valid or invalid), the date it was created, and the date when the last data definition language (DDL) operation was performed on it. The Last DDL date can help you to find if any changes to the object definitions have been made on or after a specific time.
Invalid Objects: Lists all objects that have a status of invalid.
Object Count by Type: For each type of object associated with a specific owner, lists the number of objects. This report can be useful in checking that two schemas match. It might help you to identify users that have created an especially large number of objects, particularly objects of a specific type.
To display reports to check the validity of an installation:
In the Reports navigator in SQL Developer, expand Data Dictionary Reports, and then expand All Objects.
Click each report that you want to see, such as All Objects, Invalid Objects, and Object Type by Count.
For each report that you specify, select the database connection to use, and click Apply when you are asked for any bind variables (unless you want to restrict the display).
After you have deployed the database application and validated the installation, if you have creates installation scripts for your application, consider archiving them in a source code control system, with comments describing the purpose of each script and when it was created. This archive will be useful if you ever need to deploy to another environment, because you can use the scripts to re-create a clean installation. To archive data, you can use Oracle Data Pump.
See Also:
Oracle Database Utilities for detailed information on Oracle Data Pump
