CDB file are used in and support only, even though the forcecoming upg file will be named
A CDB file should have the following sections:
- File name
See name standard in each section below
Not mandatory, but in support it is good to summarize the purpose of the file, if not mentioned in the Design History
- Design History
A CDB file must be possible to deploy more than once, so the statements in a CDB file should be re-deployable without generating errors or corrupting data.
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 not necessary to manually override the setting. For CDB files it is recommended to included the
SET SERVEROUTPUT OFF.
SQL/PLSQL keywords should always be in upper case.
Each PL/SQL block should be preceded by a timestamp. At the end of the last PL/SQL block, Timestamp ‘Done’ should be placed.
ACCEPT and '&' Charachter¶
ACCEPT keyword is not allowed since it halts the execution waiting for user input. Use of ‘&’ 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.
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 method execution 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 CDB 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
COMMIT statement is not required. DDL statements are implicitly committed and therefore
COMMIT statement is unnecessary. It is not necessary to include partial commits for large transactions, because Oracle will automatically extend 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 that the statement are 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 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 are a need of interacting with a dynamic component, the logic needs to be moved to a post section. The reason is because components with dynamic dependency gets deployed in parallel, therefore 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 CDB files. The constants have not yet got the correct value and conditional compilation doesn't support conditional execution. Instead Database_SYS functions should be used to check existence of another components or database objects. Dictionary_SYS is also not reliable during installation since the cache may not be up to date.
Remove obsolete objects¶
Obsolete views, packages, report tables, temporary tables, indexes, constraints and sequences can be removed. Never drop any columns or tables that could cause data loss for the customer. Removal of obsolete columns are handled in the <Component-short-name>cl.sql file (clear-script), but if it is a NOT NULL column, then the column should be made null-able in the CDB file. Tables that contain data should be renamed in the CDB 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.
CDB Files in Development¶
Database Changes During Development¶
The name standard to use is <YYMMDD>_<JIRA ID>_<Component>.cdb.
The reason for using CDB files during development is mainly to correct irregularities in the database that cannot be done through the
CDB Files in Support¶
The name standard to use is <Core version>-<SU version>.cdb, typically 21R1-SU03.cdb. Note that only one cdb per service update is created.
The support specific guidelines could be found in the Support Guidelines.
DB Patch Registration¶
If there are several PLSQL blocks each block is identified as a sub section and should only be deployed if it's not already installed. This is achieved by introducing an IF statement surrounding the sub section and check with Db_Script_Register_Detail_API.Is_Sub_Section_Registered. In the end of the block, the sub section should, if successful, be registered using Db_Script_Register_Detail_API.Register_Sub_Section.
In case of errors the EXCEPTION should be handled and the error text shold be registered in Db_Script_Register_Detail_API.Register_Sub_Section. The RAISE command should be used in each EXCEPTION in order to throw the exception out of the block.
At the end of the file, error messages are spooled to the component installation log file using dbms_output.put_line. In addition to that, raised error messages are also prompted to _ERROR_install.txt located in the database installation log folder.