Lifecycle Management - Expert Oracle Application Express - page 409

To illustrate the concept, we'll revisit the patch script from Listing 9-10. For version 1.0.0.3, you
modified the package FM BOOKING . The package consists of a package header as well as a package body.
Thus, you create two empty files with the names fm booking.pks and fm booking.pkb and store them in
the directory patch\1.0.0.3\sql\fm .
Next, register the two scripts in the corresponding sections of your patch script:
patch\1.0.0.3\sql\ patch fm.sql :
prompt **********************************************************************
prompt ** Package Headers
prompt **********************************************************************
prompt *** FM BOOKING
@@fm/fm booking.pks
@@lib/ pause
prompt **********************************************************************
prompt ** Package Bodies
prompt **********************************************************************
prompt *** FM BOOKING
@@fm/fm booking.pkb
@@lib/ pause
When you finalize the patch later, these files will be generated from the database and copied here.
At this point you only need to register the package as being part of the patch. You can keep modifying it
until you are done; you don't have to store all intermediate versions here.
The incremental changes are more difficult. You need a script which implements the incremental
change. You can either write it manually or have it generated by the development environment. For
example, when you add the column BOOK CAN BE CANCELED UNTIL to the table FM BOOKINGS you can
modify the table (after exclusively locking it) directly using Quest Toad (or many other IDEs) and then
have the corresponding statement generated by pressing the button Show SQL :
ALTER TABLE FM BOOKINGS
ADD (BOOK CAN BE CANCELED UNTIL DATE)
/
Now you can save that statement in the file patch\1.0.0.3\sql\fm\fm bookings.sql . Next, register
the script in the corresponding section of your patch script patch\1.0.0.3\sql\ patch fm.sql :
prompt **********************************************************************
prompt ** Tables
prompt **********************************************************************
prompt *** FM BOOKINGS
@@fm/fm bookings.sql
@@lib/ pause
■ Note All script files are registered manually in the patch <db schema> .sql script.