User Tools

Site Tools


supported_patch_file_changes

Supported Patch File Changes

The kind of changes that can be implemented with patch files depend on the object type of the patch file. The following table summarizes the options available:

Object Type Delta Patch Files Versioned Patch Files
CREATE UPDATE DROP CREATE UPDATE DROP
Table
Global Temporary Table
Materialized View
Materialized View Log
Index
Constraint
Cluster

A few things to note from the above table:

  1. Versioned patch files are only supported for tables.
  2. Delta patch files can only be used to update existing objects. They cannot be used to create new objects or to drop existing object (except for table patch files which support drops as well). Lure executes creates and drops efficiently and correctly and allowing users to write custom patch scripts for creating and dropping objects will not be beneficial in any way.  
  3. Even if a delta patch file exists for an object Lure will create or drop the object (if required) before executing the patch file. In fact Lure will not even execute the patch file unless at least one object in the matching source file has been identified for update. In that case Lure will not drop and re-create the object but instead count on the delta patch file to bring the object op to date.
  4. In order to protect the data Lure never drops tables. Hence delta patch files for tables also support drops.

 

Restrictions on patch file statements

Delta patch files (excluding patch files for tables):

  1. Should not contain any CREATE or DROP statements, only ALTER statements.
  2. Should only contain ALTER statements to update the objects that map to the corresponding source file. (In other words the patch file AAA.idx.lsql.lpatch should not update indexes that are in file BBB.idx.lsql.)
  3. May contain anonymous PL/SQL blocks but only if such PL/SQL blocks comply with the above rules.

Delta patch files for tables:

  1. Should not contain any CREATE TABLE statements, only ALTER TABLE and DROP TABLE statements.
  2. Should only contain ALTER TABLE statements to update tables that map to the corresponding source file. (In other words the patch file AAA.table.lsql.lpatch should not update a table with name BBB.)
  3. May contain anonymous PL/SQL blocks but only if such PL/SQL blocks comply with the above rules.
  4. May contain any DML statements (i.e. INSERT, UPDATE, etc.)

Versioned patch files for tables:

  1. May contain any combination of CREATE TABLE, ALTER TABLE and DROP TABLE statements.
  2. May update tables corresponding to other source files. (In other words the patch file AAA.table.lsql.r45.lpatch may update a table with name BBB.)
  3. May not contain statements that explicitly update any other object types. (For example, by dropping a column Oracle will automatically drop an index on the column as well. However, do not explicitly add the DROP INDEX statement within the versioned patch file.)
  4. May contain anonymous PL/SQL blocks but only if such PL/SQL blocks comply with the above rules.
  5. May contain any DML statements (i.e. INSERT, UPDATE, etc.)

It is necessary to allow versioned patch files to update other tables as well since often table changes are related. For example when changing the relationship between two tables from one to many to a many to many relationship, it is necessary to first create a new table, then populate it with the foreign key and primary key columns of the “many” table and then to drop the foreign key column from the “many” table. These changes have to happen in a specific order and putting the statements in the same patch file guarantees the order.

Lure does not currently enforce these restrictions but by ignoring this guidance you are likely to break the integrity of the import process.

Next: Patch Files Execution

supported_patch_file_changes.txt · Last modified: 2013/07/30 07:50 by admin

Page Tools