Upgrade (UPG) files are used when upgrading from a previous version (track) to a new version. These files are only executed during an upgrade process and are not used during a fresh installation. Depending on the current version of IFS Cloud it might be required to execute several upgrade files per component in order to upgrade to the latest CORE version. Eg. Apps 7.5 to IFS Cloud 21.2.
An UPG file should have the following sections:
- File name
The file name should correspond to the component version that the upgrade will end up in. For example, if an UPG file should be added for a component that in the previous release had version 2.0.0 and will in the next release have 2.1.0 then the name should be 210.upg
- Design History For new files, design history should be entered in date order (i.e. new entries first). In some of the older files this may have been done in the reverse order. In such an instance the convention used in the file should be followed. Every change done should have an entry for design history. JIRA ID should be mentioned as a reference to trace the reason for DDL or DML statements.
SET SERVEROUTPUT ON
This statement allows the information messages that are generated by Database_SYS public interfaces to be visible in the installation log.
SET SERVEROUTPUT ON statement should be included at the beginning of the file after the design history section. SIZE should not be specified, since default SIZE is unlimited and therefore it will not be necessary to manually override any SIZE setting.
SET SERVEROUTPUT OFFstatement should not be placed anywhere in the file. The IFS Installer will add
SET SERVEROUTPUT ONin the beginning of the merged UPG file and a
SET SERVEROUTPUT OFFin the end.
Creation/modification of all database objects (DDL statements) and all changes of data (DML statements) should be done under this section. Each group of statements which affect one table should be placed, if possible, in separate subsections.
If possible everything regarding one table i.e. table creation, alterations, data updates etc. should be grouped under one section. It is easier to figure out all the changes for a particular table without having to scan through the complete file. If this is not possible, a properly worded comment about the reason(s) should be added. Each section should be started with a comment line (i.e. set of dashes) and an empty line.
Every PL/SQL block inside a section should have a proper PROMPT. These prompts are shown in the installation log files and are very useful for locating errors.
Remove Obsolete Objects Section
This section should be placed at the end of the UPG file. Here obsolete views, packages, report tables, temporary tables, indexes, constraints and sequences can be dropped. Developers should never 'drop' any columns or tables that could cause data loss for the customer. The removal of obsolete columns should be handled in the <Component-short-name>cl.sql file (clear-script). If it is a NOT NULL column, then the column should be made nullable in the UPG file. Tables that contain data should be renamed in the UPG file and removed in the clear script. The renaming standard is to change the _TAB to the version number of the component. If assuming the table EXAMPLE_TAB has become obsolete in version 2.1.0, then it should be renamed to EXAMPLE_210.
SQL/PLSQL keywords should always be in upper case.
Each PL/SQL block should be preceded by a timestamp. In core projects timestamps are added via a tool in the end of the project. Therefore it is not mandatory to add these manually.
Each PL/SQL block should be preceded by a describing
PROMPT. This will be written in the installation log and are together with the timestamp a good tool to find performance bottlenecks and to easily locate installation errors.
ACCEPT and '&' Character
The use of the
ACCEPT keyword is not allowed since it halts the execution waiting for user input. The use of the
& character should only be used for defined values (either locally or in deploy.ini file of the component). Even in comments the IFS Installer will try to replace the value with a predefined value and if it doesn't exist the execution will halt waiting for user input. Most of the time upgrade scripts are executed unattended. Thus executions should not be halt awaiting for user interaction.
For the DDL statements the public interfaces in the package Database_SYS should be used (except when your are in component Fndbas, where package Installation_SYS should be used instead). The parameter
show_info should be set to
TRUE when calling the methods within this package. Information regarding the success or failure of the execution method will be displayed in the installation logs.
References to views/packages (even to static components) is not allowed, since the may not been created or may have become invalid during the installation. If it is a need for interacting through package methods that code should be located in an automatic post-script. It is possible to create temporary indexes in UPG files to support efficient execution of DML statements. Once the DML statements are executed, these indexes can be dropped.
All DML statements should be followed by a
COMMIT statement. If any PL/SQL Block consists purely of DDL statements, then the
COMMIT statement is not required. DDL statements are implicitly committed and therefore the
COMMIT statement is unnecessary. It is not necessary to include partial commits for large transactions, because Oracle automatically extends the rollback segments.
Note: If there are several DML statements that should be treated as a single transaction, then the COMMIT statement may be included after the last of such statements. Any un-committed DML transactions will also get committed when a DDL statement gets executed
When SQL statements or PL/SQL blocks are written, care should be taken to avoid the statements being executed more than once. If a pure SQL statement ending with a semicolon is also followed by slash (‘/’) the statement will be executed twice.
It is also a risk that a pure SQL statement will not be executed at all, if the statement is ending with a semi colon followed by a line comment (within the same line).
It is possible to include output statements such as dbms_output.put_line in PL/SQL blocks for giving relevant technical information. It is not necessary to include additional information messages for DDL statements executed via Database_SYS since the public interfaces in this package take care of this. Passing
show_info_ parameter(s) enables this information.
Interactions to other Components
When interacting with other components the dependency type must be considered
- Static dependency - It is possible to directly read from a table of a static component, but data should never be manipulated in another component.
- Dynamic dependency - If there is a need to interact with a dynamic component, then the logic needs to be moved to a post section. The reason is because components with dynamic dependencies can get deployed in parallel; this means that at the deployment time the existence of database objects that are being referred to, cannot be guaranteed
Conditional compilation is not allowed in installation scripts, such as UPG files. The constants have not yet got the correct value and conditional compilation does not support conditional execution. Instead Database_SYS functions should be used to check the existence of other components or database objects. Dictionary_SYS is also not reliable during installation since the cache may not be up to date.
UPG files will generally not get deployed more than once, However, it is mandatory that the statements in an UPG file should be re-deployable without generating errors or corrupting existing data.
Examples, see here.