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 Applications it might be required to execute several upgrade files per component in order to upgrade to the latest CORE version. Eg. IFSAPP 7.5 to IFSAPP 9.
An UPG file should have the following sections:
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.
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.
Note: SET SERVEROUTPUT OFF
statement should
not be placed anywhere in the file. The IFS Installer will add SET
SERVEROUTPUT ON
in the beginning of the merged UPG file and a SET
SERVEROUTPUT OFF
in 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.
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.
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. 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 TRUE
to show_info_
parameter(s) enables
this information.
When interacting with other components the dependency type must be considered
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.