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. Special care must be taken when changing data types, column lengths etc. where the target database columns may already contain data which would result in an Oracle error. Therefore it is highly recommended to test cdb scripts in a database containing data in the columns that are being changed by the cdb.
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. Developers should never 'drop' any columns or tables that could cause data loss for the customer. Instead, there are methods in Database_SYS for obsoleting these two objects, Obsolete_Column and Obsolete_Table. These methods drop constaints etc. and renames them with a certain pattern. Columns are prefixed with OBSOLETE_COLUMN and tables with OBSLETE, but also suffixed with the component name. The removal of the renamed objects should be handled in the
CDB Files in Development¶
Database Changes During Development¶
All database changes should be done in one cdb file named after the comming version, e.g. 2210.cdb
There could be a need for other CDB files during development due irregularities in the database that cannot be done through the version cdb file. The naming standard for such files is <YYMMDD>_<JIRA ID>_<Component>.cdb. Note that no CDB files will not be part of a core release.
CDB Files in Support¶
After the Early Access version is released, we are in support mode. DDL changes are not allowed in the
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.