SQL files are mainly used when manually deploy to the database. The exception is the automatic post-scripts which the IFS Installer will deploy during the installation process. Manual steps in an installation should be avoided as much as possible, so introducing a new manual Pre- or Post-script should be the absolutely last option.
Manual pre-scripts are deployed before the upgrade is done.
The name standard to use is PRE_<Component>_<Upgrade_to>_<Description>.sql and the file should be located in the folder manualdeploy.
- <Upgrade_to> is the IFS Applications version to upgrade to when the script is introduced.
All manual pre-scripts must be documented in the Installation Roadmap.
The most common operation in a pre-script is to use DML (Data Manipulation
Language) statements, but it is possible to have DDL (Data Definition Language)
statements. For example a temporary index could be created to increase the
performance of the DML statements. When creating a temporary index NULL
should be passed as the TABLESPACE. Drop any temporary indexes in
the end of the file.
Since the pre-scripts are to be executed before the actual upgrade, the state of the framework code is doubtful - after all, the script should be able to be executed on any version of IFS Applications. Also, any Oracle upgrades that has been done before the script is executed might have influenced the framework code. Therefore, it is not allowed to use any kind of business logic in the manual pre-scripts, not even methods in the Installation_SYS/Database_SYS packages.
If required it is possible to spool the output to file. If the content only are information a log (.log) file should be the output format. In case the content is executable PL/SQL code it should be spooled to a sql (.sql) file.
An automatic pre-scripts is deployed before all the UPG files.
The name standard to use is PRE_<Component>_<Description>.sql and the file should be located in the source/<component>/database folder. A reference to the file should be added in the section [<component>PreUpgrade] in deploy.ini.
An automatic pre-script could be used when it is necessary to execute statements using the existing packages in the database.
Manual post-scripts are deployed after the upgrade is done.
The name standard to use is POST_<Component>_<Upgrade_to>_<Description>.sql and the file should be located in the folder manualdeploy.
- <Upgrade_to> is the IFS Applications version to upgrade to when the script is introduced.
All manual post-scripts must be documented in the Installation Roadmap.
If required, it is possible to spool the output to file. If the content only are information a log (.log) file should be used as output format. In case the content is executable PL/SQL code it should be spooled to a sql (.sql) file.
The automatic post-scripts are deployed by the IFS Installer after all the UPG/CRE, CDB, API, APY, RDF and INS files for all components and after the recompilation of invalid objects and refreshing of caches. Therefore it is possible to include references to business logic.
The name standard to use is POST_<Component>_<Description>.sql and the file should be located in the source/<component>/database folder. A reference to the file should be added in the section [PostInstallationData] or [PostInstallationDataSeq] in deploy.ini if it contains DML (Data Manipulation Language) and to [PostInstallationObject] if it contains DDL (Data Definition Language).
An automatic post-script must be re-executable without corrupting data or generate 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 should halt
waiting for user input. Most of the time upgrade scripts are executed
unattended. Thus executions should not be halt awaiting user interaction.
The clear scripts are used to remove obsolete database object that contains
data. Those object can't be removed automatically in the UPG file. Tables are
renamed in the UPG file and then removed in this script. NOT NULL
columns should be made nullable in the UPG and then removed in this script.
The name standard to use is <Component>cl.sql and the file should be located in the folder manualdeploy.
A clear script should have following sections in the header:
- File name
- Module
- Purpose
- Design History
In the top of the code section an EXIT should be placed with
preceding information that this file must be edited before usage.
Each block should be preceded with a PROMPT describing which
type of objects that are removed. In the end a PROMPT with
information that removal of obsolete objects are done.
The public interfaces in the package
Database_SYS should be used for the removal. 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.
Note: While executing the clear scripts all the background jobs needs to be stopped.
The drop script is used when removing a component. All the database objects
owned by the component will be removed. Basic data inserted in other components
database objects will still remain. This file is generated via a tool and should
not be created or modified manually. It will have an EXIT generated
that needs to be removed manually before executing.
The file should be located in the component's folder manualdeploy. A drop script for an obsolete component will be located in the PRODIFSApplications component.
Note: While executing the drop scripts all the background jobs needs to be stopped.