There are two main areas were the CDB file is used; development and support. Most of the guidelines are equal for both areas, but it also exist differences on how to write a CDB file in support compared to how to write it in the development phase.
A CDB file should have the following sections:
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.
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.
Use of 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. 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.
When interacting with other components the dependency type must be considered
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.
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.
The name standard to use is <YYMMDD>_<JIRA ID>_<Component>.cdb.
The reason for using CDB files during development is mainly to keep the environments up to date on a daily basis. The CDB files will not be part of a core release. Customers will either upgrade with UPG files or perform a fresh installation with the CRE files, so any update in a CDB file must be merged manually to the UPG files and added to the Storage files, which will be the source for the generated CRE files.
The name standard to use is <YYMMDD>_<Project Name>_<Component>.cdb where Project Name should be Market if applicable. If not a shortened Project Name should be used.
This is how to merge database changes from a sub project or an extension project into a core project. All database changes should be merged into one single CDB file per component. Don't forget that all changes also must be added in UPG and Storage, since the CDB files only are used to update the development environments and will not be part of the core release.
The name standard to use is <YYMMDD>_<Bug ID>_<Component>.cdb.
The support specific guidelines could be found in the Support Guidelines.
The name standard to use is <YYMMDD>_<Market>_<Component>.cdb.
For Industry Extensions the CDB files are used to apply all the database changes for all installation scenarios like fresh installation, upgrade and to apply the extension as a delivery. If it is a new component is introduced the database changes should be part of the generated CRE files. All other database changes should be merged into one single CDB file per component.
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.