时间：2021-07-01 10:21:17 帮助过：21人阅读
DeployingCustomizations in Oracle E-Business Suite Release 12.2 This documentdescribes how to deploy customizations in an Oracle E-Business Suite Release12.2 environment. Follow these instructions to ensure that your customizationsare hand
DeployingCustomizations in Oracle E-Business Suite Release 12.2
This documentdescribes how to deploy customizations in an Oracle E-Business Suite Release12.2 environment. Follow these instructions to ensure that your customizationsare handled appropriately in conjunction with the Online Patching featureintroduced in Release 12.2.
There is a change log at the end of this document.
In This Document
This document isdivided into the following divs:
div 1: Working with Editions
Note: This divreplaces the div "Working with Editions" in Chapter 6,"Developer Guidelines for Customizations in an Online Patching-EnabledEnvironment," of the Oracle E-Business Suite Developer's Guide,Part No. E22961, in the Release 12.2.2 documentation library.
An OracleE-Business Suite Release 12.2 installation now includes two editions (versions)of the application code and seed data. The file system contains two completecopies of the Oracle E-Business Suite and technology files. In the database, weuse the Edition-based Redefinition feature to create a new database edition foreach online patching cycle.
The "RunEdition" is the code and data used by the running application. The RunEdition includes a complete application-tier file system along with all objectsand data visible in the default edition of the database. As a developer, youwill connect to the Run Edition whenever you are engaged in normal developmentactivity on the system.
The "PatchEdition" is an alternate copy of Oracle E-Business Suite code and seeddata that is updated by Online Patching. The Patch Edition includes a completecopy of the application-tier file system and editioned database code objects.The Patch Edition is only usable when an Online Patching session is inprogress. End users cannot access the Oracle E-Business Suite Patch Edition,but as a developer you may need to connect to the Patch Edition of a systemwhen applying patches or debugging problems with Online Patch execution.
The OracleE-Business Suite application-tier files are installed in a root directory ofthe customer's choosing. Within that root directory you will now find threeimportant sub-directories:
The fs1 and fs2directories contain the Run Edition and Patch Edition files for OracleE-Business Suite. The "run" and "patch" file systemdesignation will switch back and forth between fs1 and fs2 for each patchingcycle. To find out which file system is the Run Edition you must look at thevalue of FILE_EDITION environment variable in the environment script for eachfile system:
$ cd /u01/R122_EBS
$ grep FILE_EDITION= */EBSapps/appl/*.env
fs1/EBSapps/appl/gbzd122x_slc04axp.env:FILE_EDITION="patch"
fs2/EBSapps/appl/gbzd122x_slc04axp.env:FILE_EDITION="run"
In the aboveexample, 'fs2' is the Run Edition file system, and 'fs1' is the Patch Edition.
div 1.1: Connecting to the Run Edition
The Run Editionfile system and database edition are used by the running application. Normaldevelopment activity (writing and testing new code) will also take place in theRun Edition of a development environment.
Oracle E-BusinessSuite Release 12.2.2 and higher includes a script to set the run or patchedition environment by name. The script is called "EBSapps.env" andis found in the root directory of an Oracle E-Business Suite application-tierinstallation.
$ source /u01/R122_EBS/EBSapps.envrun
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u01/R122_EBS/fs2/EBSapps/appl
PATCH File System : /u01/R122_EBS/fs1/EBSapps/appl
Non-Editioned File System : /u01/R122_EBS/fs_ne
DB Host: slc04axp.us.oracle.com Service/SID: gbzd122x
Sourcing the RUN File System ...
$ echo $FILE_EDITION
run
$ sqlplus /
SQL> select ad_zd.get_edition_type fromdual;
GET_EDITION_TYPE
----------------
RUN
div 1.2: Connecting to the Patch Edition
The Patch Editioncontains a copy of the application code that can be modified by OnlinePatching. A developer may need to connect to the Patch Edition of an OracleE-Business Suite installation in order to apply patches by hand, or toinvestigate problems with Online Patch execution.
Warning: It is onlysafe to connect to the patch edition while an Online Patching session is inprogress. Specifically, the Patch Edition is created during the "adopphase=prepare" operation, and persists until the cutover or abortoperation is run.
Connect to thepatch edition using the EBSapps.env script as follows:
$ source/u01/R122_EBS/EBSapps.env patch
E-Business Suite Environment Information
----------------------------------------
RUN File System : /u01/R122_EBS/fs2/EBSapps/appl
PATCH File System : /u01/R122_EBS/fs1/EBSapps/appl
Non-Editioned File System : /u01/R122_EBS/fs_ne
DB Host: slc04axp.us.oracle.com Service/SID: gbzd122x
Sourcing the PATCH File System ...
$ echo $FILE_EDITION
patch
$ sqlplus apps/apps
SQL> select ad_zd.get_edition_type from dual;
GET_EDITION_TYPE
----------------
PATCH
Theapplication-tier Patch Edition environment is configured to connect to thedatabase patch edition by default. If a database patch edition is not active,then attempting to connect to the database from the application-tier patchedition environment will fail.
div 1.3: Displaying Edition Status
To help keep trackof what environment and edition you are connected to, it can be helpful to setthe TWO_TASK or FILE_EDITION environment variable as your shell prompt.
$PS1='$TWO_TASK> '
zd122_patch>
You can find outwhether a system is in an Online Patching cycle using the "adop-status" command.
$ adop-status
Enter the APPS username: apps
Enter the APPS password:
Current Patching Session ID: 60
Node Name Node Type Phase Status Started Finished Elapsed
----------- ---------- ---------- -------- ------------------------- ------------------------- ------------
slc04axp master PREPARE COMPLETED 02-JUL-13 04:03:25 -07:00 02-JUL-1305:03:32 -07:00 1:00:07
APPLY COMPLETED 09-JUL-1312:20:45 -07:00 09-JUL-13 01:23:00 -07:00 1:02:15
CUTOVER COMPLETED 10-JUL-13 09:11:41-07:00 10-JUL-13 09:18:47 -07:00 0:07:06
CLEANUP COMPLETED 10-JUL-13 09:29:53-07:00 10-JUL-13 09:52:50 -07:00 0:22:57
If the CUTOVERphase status in not COMPLETED, then an online patching session is in progressand it is valid to connect to the patch edition of the environment.
You can also seethe names and status of past and present database editions using theADZDSHOWED.sql script.
$ sqlplusapps/apps @ADZDSHOWED
"---- Editions ----"
Edition Name Type Status Current?
--------------- -------- -------- --------
ORA$BASE RETIRED
V_20120510_1507 OLD RETIRED
V_20120510_1547 RUN ACTIVE CURRENT
V_20120511_1528 PATCH ACTIVE
The script liststhe existing database editions and identifies the OLD, RUN, and PATCH editions.The Status indicates whether you can connect to the edition (you may onlyconnect to an ACTIVE edition). The Current flag indicates which edition you arecurrently in.
From SQL*Plus itis possible to change your current edition.
SQL> execad_zd.set_edition('PATCH')
div 1.4: Tools and Scripts for Edition-basedDevelopment
The examples inthis guide use various SQL*Plus scripts and command line tools like adop,xdfgen.pl and xdfcmp.pl. The scripts and tools used in Online Patching areoften dependent on a specific code level in the rest of the system, so whenusing an Oracle E-Business Suite environment for development make sure to usethe scripts and tools that come with that environment. Connect to theapplication-tier host for your development environment and source the RunEdition environment file.
$ source/u01/R122_EBS/EBSapps.env run
...
$ which adop
/u01/R122_EBS/fs_ne/EBSapps/appl/ad/bin/adop
$ which xdfgen.pl
/u01/R122_EBS/fs2/EBSapps/appl/fnd/12.0.0/bin/xdfgen.pl
$ which xdfcmp.pl
/u01/R122_EBS/fs2/EBSapps/appl/fnd/12.0.0/bin/xdfcmp.pl
There are a numberof SQL*Plus scripts that can provide useful information about the state of youreditioned development environment. All ADZD* scripts are found under $AD_TOP/sql.For convenience, you can add this directory to the SQLPATH environment variableso that you can refer to the scripts by simple name.
$SQLPATH=$AD_TOP/sql; export SQLPATH
The followingscripts are for experts:
div 2: Applying Online Patches
Note: This divshould follow the div "Working with Editions" in Chapter 6,"Developer Guidelines for Customizations in an Online Patching-EnabledEnvironment," of the Oracle E-Business Suite Developer's Guide,Part No. E22961.
Before developingon an editioned application system, you should understand how online patchesare applied to that system. Application development is done on the Run Editionof a development system, while an online patch is always applied to the PatchEdition of a target system. The online patch may take the form of a ManualPatch or an ARU patch (Oracle patch).
div 2.1: TheOnline Patching Cycle
All patches to aneditioned system are applied within the context of an Online Patching Cycle.The patching cycle has several phases which proceed in order.
Online PatchingCycle phases are executed using the new "adop" command line tool.Syntax for each of the phases is described below. At any time you can get adopcommand line help by running "adop -help". You can check the statusof the patching cycle by running "adop -status".
The followingdivs describe how to progress through each phase in detail.
div 2.2:Prepare
Before applying apatch, you must start an Online Patching Cycle. This is done using the adop"prepare" command. Connect to the primary application-tier node ofyour target system and source the run edition environment. Then execute theprepare command.
$ source/u01/R122_EBS/EBSapps.env run
...
$ adop phase=prepare
The adop utilitymay first execute the cleanup phase from the previous cycle if needed, and willthen proceed to prepare the patch edition for a new Online Patching Cycle. Toprepare the patch edition, adop will:
File systemsynchronization may be done by applying the delta (changes) from the previouspatching cycle, or by re-creating the entire patch edition file system as a freshcopy of the run edition (called "fs_clone"). When complete, check theexiting status code (success is '0'):
adop exiting withstatus = 0 (Success)
If there were anyproblems with the prepare phase, check div 7: Troubleshooting and resolvethe problem. Then run the prepare command again.
After a successfulprepare phase, the database and file system patch edition will contain a copyof the run edition code and seed data. You can now apply ARU patches and manualpatches to the patch edition.
div 2.3:Apply
Once the PatchEdition is prepared, you can apply any number of ARU patches or manual patchesto the patch edition. Changes to the patch edition are isolated from the runedition, which is still available for use.
Apply an ARUPatch
Before applying anARU patch, you must first download the patch bundle from ARU through the webuser interface (support.oracle.com). The downloads will be in the form of ZIPfiles. Place the ZIP files in the "fs_ne/EBSapps/patch" directory onthe application-tier installation of your target application system, and thenunzip all ZIP files.
ARU patches areapplied to the patch edition using the "adop phase=apply" command.The command accepts a "patches=..." parameter where you can specify asingle patch or a comma-separated list of patches.
$ adop phase=applypatches=16605855
...
$ adop phase=apply patches=15111111,15222222
...
Note that the adopcommand will apply patches to the patch edition no matter what edition yourcurrent environment is set to.
If the adop applycommands fail, check div 7: Troubleshooting and correctthe problem, then run the adop apply command again, adding the"restart=yes" option.
$ adop phase=applypatches=16605855 restart=yes
...
Apply a ManualPatch
Manual patchesmust be applied to the patch edition of a target system "by hand". Dothis by changing to the patch edition environment and manually executing thepatching actions necessary to install the update. The manual patch actions areidentical to those you would take when applying manual patches to anon-editioned system; the only difference is that on an editioned system, theseactions take place in the patch edition.
Manual patchingactions normally involve the following steps:
The exact commandsneeded to apply a manual patch vary by the type of files or database objectsbeing patched. These required deployment commands for each file and object typeare discussed later in this document.
The following is asimple example of installing a new server PL/SQL package.
$ source/u01/R122_EBS/EBSapps.env patch
...
$ cd $NE_BASE/EBSapps/patch/manual_000
$ apply_fs.sh
# apply patch to file system
cp fnd/patch/115/sql/* $FND_TOP/patch/115/sql
$ apply_db.sh
# apply patch to database
sqlplus apps/apps @$FND_TOP/patch/115/sql/XYZUTILS.pls
sqlplus apps/apps @$FND_TOP/patch/115/sql/XYZUTILB.pls
After applying anARU patch or a manual patch you can look at the patch edition file system ordatabase status to verify that the patching actions were successful and thatthe resulting patch edition code and seed data are as expected. When you havesourced the patch edition environment, the default database connection goes tothe patch edition. Although you cannot run the application user interface orprogram code in the patch edition, it is possible to connect to the database viaSQL*Plus or other tools and confirm that the desired changes have beensuccessfully implemented. To confirm the updates of the previous manualpatching example, you could do the following:
$ source/u01/R122_EBS/EBSapps.env patch
...
$ sqlplus apps/apps
SQL> show errors package XYZ_UTIL
SQL> show errors package body XYZ_UTIL
SQL> quit
Once all patchingactions are complete and validated, you may proceed to the finalize phase.
div 2.4:Finalize
The finalize phaseis used by the Online Patching system to perform any final actions needed tomake the system ready for the fastest possible cutover. The finalize command isrun as follows:
$ adopphase=finalize
If the finalizecommand returns an error, the system is not ready for cutover. In this case,check div 7: Troubleshooting, correct theproblem and run the finalize command again.
After successfulcompletion of the finalize phase, the system is ready for cutover, but you donot need to execute the cutover right away; you can delay executing cutoveruntil a convenient or predetermined time in the future. You may also applyadditional patches if needed, but you will need to run the finalize phase againafter doing so.
div 2.5:Cutover
The cutover phasewill configure the patch edition to become the new run edition, and restart theapplication on this new run edition.
$ adop phase=cutover
...
$ source /u01/R122_EBS/EBSapps.env run
If cutover fails,check div 7: Troubleshooting, resolve theproblem, and try the cutover command again. One common failure on under-poweredenvironments is that the application startup does not happen quickly enough andthe startup script times out. In this case, just run the cutover command againand adop will retry the startup script.
After successfulcompletion of the cutover phase, the application will be up and running on thenew edition, ready for use. Since the run/patch designation of the dual filesystems are swapped during cutover, you must re-source the run editionenvironment directly after cutover.
Important: Remember tore-source the run edition environment directly after cutover.
div 2.6:Cleanup
The cleanup phasewill remove unnecessary code and data from old editions that are no longerneeded by the running application. Cleanup should be run after cutover, at anytime before the next prepare phase. It is best to run cleanup immediately aftercutover so that there is no delay when preparing the next online patching cycle.There are two levels of cleanup available:
Quick cleanup isthe default, and is all that is necessary after normal patching.
$ adopphase=cleanup
Use full cleanupperiodically or after major updates to restore the system to optimal spaceusage. Warning: full cleanup can take many hours and shouldonly be done when there is no immediate need to start a new patching cycle.
$ adopphase=cleanup cleanup_mode=full
Note: Due to a knownissue in Release 12.2.2, full cleanup is is currently available only in Release12.2.3 and higher
div 2.7:Special Patching Actions
For completeness,the following actions are also listed here. Refer to the OracleE-Business Suite Maintenance Guide, Part No. E22954 for more information onthese commands.
FS Clone
$ adopphase=fs_clone
Abort
$ adopphase=abort
$ adop phase=cleanup
$ adop phase=fs_clone
Note: Due to a known issuein Release 12.2.2, abort is is currently available only in Release 12.2.3 andhigher
div 3: Developing Customizations
div 3.1: Setting Up an Environment for Customizations
If you aredeveloping customizations for the first time, begin by setting up your customapplication on your development environment. See: Overview of Setting Up YourApplication Framework, Oracle E-Business Suite Developer's Guide.
As part of settingup your application, use the AD Splicer utility (adsplice) to register yourcustom application as a product within Oracle E-Business Suite. Forinstructions on running adsplice, see: Applications DBA System ConfigurationTools, Oracle E-Business Suite Setup Guide, and Applications DBASystem Maintenance Tasks and Tools, Oracle E-Business Suite MaintenanceGuide.
Note: In Release 12.2,you should use adsplice to register your application in order to ensure thatthe application is set up for online patching. Do not use the Applicationswindow to register applications in this release.
Note: Wheninstalling or upgrading to Release 12.2, do not run adsplice until you haveapplied the 12.2.2 Release Update Pack. Running adsplice before your instanceis at the 12.2.2 code level may cause file synchronization issues.
You can use Patch 3636980, "SupportDiagnostics (IZU) patch for AD Splice", to help you create your customapplication. See: Creating a Custom Application in Oracle E-BusinessSuite Release 12.2, Document 1577707.1.
On yourdevelopment environment, you should invoke adsplice from the run file system.Connect to the run file system as described in div 1.1: Connecting to the Run Edition. Then runthe adsplice command.
In OracleE-Business Suite Release 12.2, adsplice performs the following steps:
When you start thenext online patching cycle, the prepare phase will run fs_clone to synchronizethe two file systems.
Note: If you upgradedyour environment from an earlier release to Release 12.2, then you should runadsplice for your custom application again after the upgrade, using the sameapplication ID and application name as when you originally added your customapplication. Running the Release 12.2 version of adsplice after the upgradehelps ensure that the custom top folder for your application will be includedwhen the two file systems are synchronized during online patching.
If yourcustomizations will include custom Java or BC4J code or extensions, apply thefollowing patches to your development environment in hotpatch mode using the ADOnline Patching utility (adop). For instructions on running adop, see: The adopUtility, Oracle E-Business Suite Maintenance Guide.
div 3.2: Building Customizations
After setting upyour development environment, build your customizations according to thedeveloper's guide for the product or component you are customizing, as well asany guidelines in div 6: Component-Specific Steps for ApplicationTier Objects.
For customizationsdeveloped directly in the Oracle E-Business Suite instance, you should downloadthe custom object files that you will deploy to your production environment.
For customizationsdeveloped in a tool outside the Oracle E-Business Suite instance, you shouldsave the custom object files from that tool. To deploy the custom object filesin your development environment for testing, perform the following steps:
div 4: Developing and DeployingCustom Database Objects
For moreinformation on database object development standards, see: Database Object DevelopmentStandards for Online Patching, Oracle E-Business Suite Developer'sGuide.
div 4.1:Editioned Database Objects
Note: This divreplaces the div "Editioned Database Objects" in Chapter 6,"Developer Guidelines for Customizations in an Online Patching-EnabledEnvironment", of the Oracle E-Business Suite Developer's Guide,Part No. E22961, in the Release 12.2.2 documentation library.
Editioned DatabaseObjects may have a different definition in each database edition. This meansthat such objects can be created or replaced in the Patch Edition withoutaffecting the running application. Editioned Database Object Types are:
For moreinformation on these objects, refer to the Oracle DatabaseAdministrator's Guide 11g Release 2 (11.2).
Step 1: Createor Replace Editioned Database Objects in your development database:
An applicationdeveloper can create or replace editioned database objects in the run editionof a development database using whatever scripts or tools they normally use.Typically this involves editing SQL scripts that contain DDL statements, andthen applying the scripts to the development database. For example:
sqlplus/ @XYZUTILS.pls"
sqlplus / @XYZUTILB.pls"
sqlplus /
exec ad_zd.compile
quit
If yourapplication changes will cause significant object invalidation in thedevelopment database, you may wish to call the "ad_zd.compile"procedure to recompile invalid objects in the run edition.
Test your changesin the running application. When satisfied, make note of the changed DDLscripts and proceed to the next step.
Step 2: Createthe patch
Patch files in theabove example would be:
The manual applyactions for the file system would be:
cpfnd/patch/115/sql/* $FND_TOP/patch/115/sql
The manual applyactions for the database would be:
sqlplus/@$FND_TOP/patch/115/sql/XYZUTILS.pls
sqlplus /@$FND_TOP/patch/115/sql/XYZUTILB.pls
div 4.2:Effectively-Editioned Database Objects
Note: This div replacesthe divs "Tables" and "Materialized Views" in Chapter6, "Developer Guidelines for Customizations in an Online Patching-EnabledEnvironment", of the Oracle E-Business Suite Developer's Guide,Part No. E22961, in the Release 12.2.2 documentation library.
div 4.2.1:Tables
Since theapplication is still running during an online patch (and the application datais continuously changing), it is not possible to upgrade application data usinga one-time update script. Instead we will need to use a new technique involvingEditioning Views and Crossedition Triggers, described below.
Note: This divdescribes how to develop and patch ordinary application data tables. But thereare some special types of tables that have additional or alternate standardsand procedures. If you are working with one of these special table types,please consult that div of the guide instead.
Create a New Table
This example willshow how to develop and patch a new table on an editioned developmentenvironment. Suppose we want to create a table that holds "serviceinformation" per user account for some application with the followinglogical table structure:
XYZ_USER_SERVICE
Name Null? Type
------------------------------------------------- --------------
USER_ID NOT NULLNUMBER
-- PK, FK to FND_USER.USER_ID
SERVICE_TYPE NOT NULLVARCHAR2(8)
-- 'BASIC' - normal service
-- 'PREMIUM' - premium service
COMMENTS VARCHAR2(1000)
In this example weuse SQL*Plus to execute the required DDL. This step includes creation of any requiredindexes, storage properties, and so on. As with all development, you should beconnected to the Run Edition of your EBS development environment.
create tableAPPLSYS.XYZ_USER_SERVICE
(
USER_ID NUMBER(15) not null,
SERVICE_TYPE VARCHAR2(8) not null,
COMMENTS VARCHAR2(1000)
)
tablespace APPS_TS_TX_DATA
/
create unique index APPLSYS.XYZ_USER_SERVICE_U1
on APPLSYS.XYZ_USER_SERVICE ( USER_ID )
tablespace APPS_TS_TX_IDX
/
Please avoid usingofficial database constraints for Primary Key and Unique Key enforcement.Unique indexes achieve the goal and are easier to manage under Online Patching.
This will generatean Editioning View (EV) for the table and then create an APPS synonym thatpoints to the Editioning View.
execad_zd_table.upgrade('APPLSYS', 'XYZ_USER_SERVICE')
The table is nowready for use from the APPS schema. The generated EV is named XYZ_USER_SERVICE#and looks exactly like the table at this point. When the table structure ispatched in the future, the EV will serve to map logical column names (used bythe application code) to the table columns that store the data in each edition.You can see a display of the EV column mapping with the ADZDSHOWEV.sql script:
$AD_TOP/sql/ADZDSHOWEV.sqlXYZ_USER_SERVICE
-- EV ColumnMapping
VIEW_COLUMN -> TABLE_COLUMN
---------------------------------- -------------------
USER_ID = USER_ID
SERVICE_TYPE = SERVICE_TYPE
COMMENTS = COMMENTS
Now we can addsome data to the table for demonstration purposes:
insert intoxyz_user_service (user_id, service_type, comments)
values (0, 'PREMIUM', 'Big Spender');
insert into xyz_user_service (user_id, service_type, comments)
values (2, 'BASIC', 'Mr Prudent');
commit;
Due to a databaserequirement you must first insert at least one row into the table beforeextraction will work.
$ xdfgen.pl /@dbXYZ_USER_SERVICE
This produces afile called 'xyz_user_service.xdf' that contains the definition of the tablealong with any related indexes, sequences, and policies.
Patch Files:
· fnd/patch/115/xdf/xyz_user_service.xdf
Manual apply phaseactions for the file system:
cpfnd/patch/115/xdf/* $FND_TOP/patch/115/xdf
Manual apply phaseactions for the database:
xdfcmp.pl/@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_user_service.xdf/
When the patch isapplied, XDF will create the table and index, and will automatically call theAD_ZD_TABLE.UPGRADE procedure to generate the editioning view and APPS tablesynonym.
Add a new column to a table
This stepdemonstrates adding a new logical column to a table (as opposed to revising anexisting logical column, which we will cover in a later div). Todemonstrate this procedure, will add a new flag to our example service tablethat indicates whether the service is enabled. The desired logical tablestructure is as follows:
XYZ_USER_SERVICE
Name Null? Type
------------------------------------------------- --------------
USER_ID NOT NULLNUMBER
-- PK, FK to FND_USER.USER_ID
SERVICE_TYPE NOT NULLVARCHAR2(8)
-- 'BASIC' - normal service
-- 'PREMIUM' - premium service
COMMENTS VARCHAR2(1000)
==> SERVICE_STATUS NOT NULLVARCHAR2(8)
==> -- 'ENABLED' - service is active
==> -- 'DISABLED' - service is notactive.
We can do this inSQL*Plus as follows:
alter tableAPPLSYS.XYZ_USER_SERVICE
add (SERVICE_STATUS varchar2(8) default 'ENABLED' not null)
/
Note: When addinga NOT NULL column, it is recommended to choose a default value. Even if thecolumn value will be populated through application logic you should stillspecify a default value for a NOT NULL column. The default value will allowXDF/ODF to create the column with the NOT NULL constraint in a single pass.Populating a new or revised column during online patching is done using acrossedition trigger which will be explained later.
3. execad_zd_table.patch('APPLSYS', 'XYZ_USER_SERVICE')
4.
5. @ADZDSHOWEV XYZ_USER_SERVICE
6. -- EV Column Mapping
7.
8. VIEW_COLUMN -> TABLE_COLUMN
9. ---------------------------------- ------------------------------
10. USER_ID = USER_ID
11. SERVICE_TYPE = SERVICE_TYPE
12. COMMENTS = COMMENTS
13. SERVICE_STATUS = SERVICE_STATUS
The new column isnow present in the Editioning View.
xdfgen.pl/@$TWO_TASK XYZ_USER_SERVICE
Patch Files:
Manual apply phaseactions for the file system:
cpfnd/patch/115/xdf/* $FND_TOP/patch/115/xdf
Manual apply phaseactions for the database:
xdfcmp.pl/$FND_TOP/patch/115/xdf/xyz_user_service.xdf
When the patch isapplied, XDF will add the new column and automatically call theAD_ZD_TABLE.PATCH procedure on the target system.
Add a new index
This divdemonstrates how to add a new index to an existing table. In the following example,we add a non-unique index on the SERVICE_TYPE attribute of our example table.The logical table structure is unchanged.
create indexAPPLSYS.XYZ_USER_SERVICE_N1 on APPLSYS.XYZ_USER_SERVICE
( SERVICE_TYPE )
tablespace APPS_TS_TX_IDX
/
When adding anindex it is not necessary to call the AD_ZD_TABLE.PATCH procedure, as the tablestructure has not changed.
xdfgen.pl/@$TWO_TASK XYZ_USER_SERVICE
When extracting atable definition, XDF also extracts any related index definitions.
Patch Files:
Manual apply phaseactions for the file system:
cp fnd/patch/115/xdf/*$FND_TOP/patch/115/xdf
Manual applyactions for the database:
xdfcmp.pl/@$TWO_TASK$FND_TOP/patch/115/xdf/xyz_user_service.xdf
When XDF appliesthe table definition, it will detect that the target database is missing thenew index, and it will create the new index. Note that when the XDF is appliedin the Patch Edition of a target system, the new index is initially createdwith an alternate name, which will then be updated to the correct index nameduring cutover.
Update an existing column
This div showshow to update an existing logical column. To update existing data withoutdisturbing the running application we must create a new physical column (calleda revised column) to hold the updated data. In this example, we upgradeSERVICE_TYPE codes from the original two-value scheme (‘BASIC’, ‘PREMIUM’) to athree-value scheme (‘BRONZE’, ’SILVER’, ’GOLD’). Since the new values are notcompatible with the existing application, we must use a revised physical columnto hold the new data. The logical name of the column (as exposed through theeditioning view) remains the same. The desired logical table structure is asfollows:
XYZ_USER_SERVICE
Name Null? Type
------------------------------------------------- --------------
USER_ID NOT NULLNUMBER
-- PK, FK to FND_USER.USER_ID
SERVICE_TYPE NOT NULL VARCHAR2(8)
==> -- 'BRONZE' - cheap service (was 'BASIC')
==> -- 'SILVER' - new mid-level service
==> -- 'GOLD' - best service (was 'PREMIUM')
COMMENTS VARCHAR2(1000)
SERVICE_STATUS NOT NULLVARCHAR2(8)
-- 'ENABLED' - service is active
-- 'DISABLED' - service is notactive.
Revised columnsuse a naming standard of COLUMN_NAME#REVISION, where a later REVISION tag mustbe alphabetically greater than the earlier revision. Since this is the firstrevision of the column, start with revision ‘1’. The data upgrade logic will beplaced in a Forward Crossedition Trigger described later. Alter the table inyour development database to add the new revised column, and remember to callthe AD_ZD_TABLE.PATCH procedure whenever you change the table structuremanually:
alter tableAPPLSYS.XYZ_USER_SERVICE
add (SERVICE_TYPE#1 varchar2(8) default '*NULL*' not null)
/
exec ad_zd_table.patch('APPLSYS', 'XYZ_USER_SERVICE')
Since the revisedcolumn is not null, specify a default value so that the column can be createdwith the not null constraint in a single operation. The actual value of thecolumn will be populated by a crossedition trigger, so the default value doesnot matter, but it is useful to specify a default value which clearly indicatesthat the column is not yet populated.
@ADZDSHOWEVXYZ_USER_SERVICE
-- EVColumn Mapping
VIEW_COLUMN -> TABLE_COLUMN
------------------------------ ----------------------------------
USER_ID = USER_ID
SERVICE_TYPE ===> SERVICE_TYPE#1
COMMENTS = COMMENTS
SERVICE_STATUS = SERVICE_STATUS
Notice that afterexecuting the PATCH procedure the SERVICE_TYPE column in the EV (the logicalcolumn) is now mapped to the revised physical column. Also notice that this newcolumn is not yet populated with data. That comes next.
A ForwardCrossedition Trigger (FCET) is a table trigger with a special rule about how itfires: During online patching, the FCET is created in the Patch Edition, but(being a crossedition trigger) it will only fire on changes made in the parent(Run) edition. The upgrade logic is implemented as a trigger instead of asimple update statement so that the upgrade logic can be re-executed on rowsthat are inserted or changed by the running application.
Although the FCETis intended to be installed in the Patch Edition during an online patch, youcan create and test an FCET in the Run Edition of a development database. Tocreate an FCET, start with the Forward Crossedition Trigger Template and addthe data upgrade logic to the trigger body.
The ForwardCross-edition Trigger Template is as follows:
REM ---- CreateFCET ----
REM dbdrv: sql ~PROD ~PATH ~FILE \
REM dbdrv: none none none sqlplus &phase=ccet \
REM dbdrv: checkfile:~PROD:~PATH:~FILE&un_
REM ---- Apply FCET ----
REM dbdrv: sql ad patch/115/sql AD_ZD_TABLE_APPLY.sql \
REM dbdrv: none none none sqlplus &phase=acet \
REM dbdrv: checkfile:~PROD:~PATH:~FILE
REM Copyright (c) 2013 Oracle, All Rights Reserved
REM $Header$
REM
REM
SET VERIFY OFF;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
create or replace trigger
before insert or update on &1..
for each row forward crossedition
/* follows
begin
end;
/
commit;
exit;
For our example,the FCET looks like the following:
REM ---- CreateFCET ----
REM dbdrv: sql ~PROD ~PATH ~FILE \
REM dbdrv: none none none sqlplus &phase=ccet \
REM dbdrv: checkfile:~PROD:~PATH:~FILE &un_fnd
REM ---- Apply FCET ----
REM dbdrv: sql ad patch/115/sql AD_ZD_TABLE_APPLY.sql \
REM dbdrv: none none none sqlplus &phase=acet \
REM dbdrv: checkfile:~PROD:~PATH:~FILE XYZ_USER_SERVICE_F1
REM Copyright (c) 2013 Oracle Corporation, All Rights Reserved
REM $Header$
REM XYZ_USER_SERVICE_X1.sql
REM Update XYZ_USER_SERVICE SERVICE_TYPE toBRONZE/SILVER/GOLD
SET VERIFY OFF;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
create or replace trigger XYZ_USER_SERVICE_F1
before insert or update on &1..XYZ_USER_SERVICE
for each row forward crossedition
disable
begin
if :new.service_type = 'BASIC' then
:new.service_type#1 := 'BRONZE';
elsif :new.service_type = 'PREMIUM' then
:new.service_type#1 := 'GOLD';
end if;
end;
/
commit;
exit;
Create the triggerwith the following naming standards: