6. Conversion Script Generation Tool

6.1 Introduction

You need to apply a set of conversion scripts on the target schema to upgrade the data after the production data is imported. This is necessary to make the target database compatible with the front end application deployed.

The new features introduced in the target version application may necessitate application of some data conversion/upgrade scripts. Apart from this the schematic differences in the database and constraints would necessitate certain scripts to be run in the back end before the application is opened to the Bank's users.

The conversion utility is a set of scripts that includes repository of data upgrade scripts and PL/SQL utility to generate the scripts dynamically to address functional enhancements and the schema differences.

This chapter discusses the method to use the Dynamic Script Generation tool.

6.2 Generating and Executing Scripts

Following steps are involved in the generation and of execution of scripts.

• Setup parameter
• Generate the dynamic scripts and spool the module wise spool files/control file
• Generate the dynamic script for a specific modules
• Generate the dynamic script for a specific script identifier
• Generate the dynamic script for aborted script identifiers
• Spool the module wise spool files and control file for a run number

6.2.1 Setting up Parameters

Set the appropriate values for the parameters in the table ‘CVTB_PARAM’ before generating dynamic scripts. You need to set the following parameters.

• SITE_VERSION: This refers to the Oracle FLEXCUBE version installed at the customer site. The scripts for data migration are picked up based on this parameter as the repository master ‘CVTM_REPOS_MASTER’ contains all the scripts.
• MIGRATION_TYPE: You can generate spool files for different table types depending on the time of data migration. For example, conversion scripts should not be applied for P-data during live cut-over.
• PARSE_STATEMENT: You can have 'Y' or 'N' as the value. If the value is ‘Y’, you can check the correctness of syntax of the dynamic scripts by parsing the statement.
• WORK_AREA: This is the valid folder which is accessible and provided ‘Write’ permission. The module wise spool of the conversion scripts and the control file is generated in this folder.
• RUN_NUMBER: It is used to identify the execution of the scripts end-to-end. if the scripts are already executed and applied onto the database, then you need to change the run_number. Otherwise, the same run_number can be retained.
• GENERATE_SPOOL_FILES: You can generate the scripts and spool the module files and control file by setting the parameter GENERATE_SPOOL_FILES to 'Y'. If it is set to 'N', the scripts are generated, but not spooled.

For scripts to be spooled later, See “Spooling Module-wise Spool Files and Control File for a Run Number” on page 3.

6.2.2 Generating Dynamic Scripts and Spooling Files

Before you generate the dynamic scripts, ensure that the data in the parameter table ‘cvtb_param’ is set as per the requirement.

In order to generate and spool the scripts, execute the stub ‘call_cvpks_full_generation.sql’ in the SQL prompt. The stub will generate the code for the script_identifier and spool the module wise script files/control file in the folder specified in the ‘WORK_AREA’ parameter.

6.2.3 Generating Dynamic Script for Specific Modules

You can generate scripts for all the script_identifiers of a one or more specific modules. In order to generate the scripts for specific modules, you need to execute the stub ‘call_cvpks_specific_generation.sql’.

The parameters for this file are ‘FLEXCUBE’, 'M' and the list of modules. The parameter ‘M’ denotes that it is module specific. You need to provide the list of modules separated by comma as the third argument. The modules will be validated against the maintenance in ‘cvtm_module_seq’.

Example 1

The SQL call to generate scripts for the modules BC and SI is as follows:

cvpks_dynamic_script_gen.pr_generate_scripts ('FLEXCUBE', 'M', 'BC, SI');

In order to execute it in SQL prompt, you need to use the command EXECUTE. If the prompt is SQL>, then the screen will have the following text:

SQL> Execute cvpks_dynamic_script_gen.pr_generate_scripts ('FLEX­CUBE', 'M', 'BC, SI');

You can execute the same statement as a PL/SQL block within begin/end as follows:

Begin

cvpks_dynamic_script_gen.pr_generate_scripts ('FLEXCUBE', 'M', 'BC, SI');

Exception

When others then

Dbms_output.put_line ('Error :' ||sqlerrm);

End;

Example 2

The SQL call to generate scripts for the module CA is as follows:

cvpks_dynamic_script_gen.pr_generate_scripts ('FLEXCUBE', 'M', 'CA');

For generating spool files, See “Spooling Module-wise Spool Files and Control File for a Run Number” on page 3.

6.2.4 Generating Dynamic Script for Specific script_identifier

You can generate scripts for specific script_identifiers. This is done by executing the stub ‘CALL_CVPKS_SPECIFIC_GENERATION.SQL’.

The parameters for this file are ‘FLEXCUBE’, 'S' and the list of script identifiers. The parameter ‘S’ denotes that it is script_identifier specific. You need to provide the list of script identifiers separated by comma as the third argument.

Example 1

The SQL call to generate scripts for the script identifiers CA_007, LD_001 and SI_009 is as follows:

cvpks_dynamic_script_gen.pr_generate_scripts ('FLEXCUBE', 'S', 'CA_007, LD_001, SI_009');

Example 2

The SQL call to generate scripts for MS_009 is as follows:

cvpks_dynamic_script_gen.pr_generate_scripts ('FLEXCUBE', 'S', 'MS_009');

For generating spool files, See “Spooling Module-wise Spool Files and Control File for a Run Number” on page 3.

6.2.5 Generating Dynamic Script for Aborted Script Identifiers

You can regenerate all the scripts that were aborted for a specific run_number. You can do this by executing the stub ‘call_cvpks_specific_generation.sql’.

The parameters for this file are ‘FLEXCUBE’, 'A' and the run number. The parameter ‘A’ denotes that it is for the aborted script identifiers. The run number is the third argument.

Example 1

The SQL call to generate the scripts for aborted script identifiers is as follows:

cvpks_dynamic_script_gen.pr_generate_scripts ('FLEXCUBE', 'A', 2);

For generating spool files, See “Spooling Module-wise Spool Files and Control File for a Run Number” on page 3.

6.2.6 Spooling Module-wise Spool Files and Control File for a Run Number

You can generate module-wise spool files and control file by executing the stub call_cvpks_generate_spools.sql’. This stub creates the spool files for the code blocks that are already generated. The files would be generated in the path maintained in CVTB_PARAM.

Yo can generate the scripts for different run numbers by specifying it in the stub itself.

The input to this stub is source_code (FLEXCUBE by default) and then the run_number.

For every run_number, the stub generates the module-wise spool files and control files separately.