Gen DB2 application migration to Oracle and CHAR v VARCHAR2

Products

• Gen
• Gen - Workstation Toolset

Issue/Introduction

Planning to move Gen application developed for z/OS and COBOL / DB2 to Windows .NET / Oracle (C /Oracle for batch programs).

One of the first questions is CHAR vs VARCHAR2 columns in Oracle and how Gen will generate code for them.
Most of the model text domain attributes are not marked as Varying Length in the ERD and will therefore generate DDL for their columns as CHAR data types.
The recommendation in Oracle is to mostly use VARCHAR2 column data types instead of CHAR.
The question is therefore:
If the attributes and column definitions are left as they are in the model (text, fixed length) and the columns in the database tables are defined as VARCHAR2, will that cause any issues at runtime running generated code expecting fixed length data when the table data is in variable length columns ?
For instance, will the code generated define variables in the program as char() for the fixed length columns rather than string if they were variable length columns which might cause issues at execution ?

Resolution

To assist with a mass CHAR to VARCHAR2 conversion, there is a "To VARCHAR2" feature that enables quick conversion of all CHAR format attributes to VARCHAR2. See Edit in Data Structure List.

Gen Engineering researched the impact on code generation.
The "To VARCHAR2" action is supported for generated C code but is not supported generated C# and Java code.
This is because the generated C code contains embedded SQL that is known to be for the Oracle database. Therefore Oracle-specific code can be generated.
The generated C# and Java code is supposed to be able to work with any database just by pointing the datasource to a different database. Therefore no database-specific code is generated in the C# and Java code.  

Some older Oracle PL/SQL version notes have been found which should still be valid (PL/SQL User's Guide and Reference > Appendix C > CHAR versus VARCHAR2 Semantics > Inserting Character Values):

• When you insert a character value into a CHAR database column, Oracle does not strip trailing blanks. If the value is shorter than the defined width of the column, Oracle blank-pads the value to the defined width. As a result, information about trailing blanks is lost. If the character value is longer than the defined width of the column, Oracle aborts the insert and generates an error.
  
  
• When you insert a character value into a VARCHAR2 database column, Oracle does not strip trailing blanks. If the value is shorter than the defined width of the column, Oracle does not blank-pad the value. Character values are stored intact, so no information is lost. If the character value is longer than the defined width of the column, Oracle aborts the insert and generates an error.
   

Since the generated C# code uses the FixedStringAttr object for fixed-length Text attributes, it seems like a VARCHAR2 database column would accept all of the trailing blanks which is not desired behaviour.
By changing the Text attributes to be Varying Length, the generated C# code will use the StringAttr object which should not contain the trailing blanks.

In summary it is suggested that changing all model text attributes to be Varying Length is the best approach.
After making the changes an existing Oracle Data Structure List will show the format as "INCONSISTENT" for the attributes that have had their Varying Length value changed from N to Y.
Then use the "To VARCHAR2" feature so the format of those attributes will change to Varchar2 and the Data Structure List & the Data Model will be in sync.
After generate/install the DDL, then the database should also be in sync.

Additional Information

For further guidance on this subject please contact Gen Support by creating a new support case on the Broadcom Support Portal.

Gen EDGE Community thread: CHAR vs VARCHAR2 in Oracle using Gen