For the benefit of other community users who may want to do a similar DBMS migration from DB2 to Oracle in the future here is a new knowledge article that was written following the research done during Stacy's support case:
Gen DB2 application migration to Oracle and CHAR v VARCHAR2
Original Message:
Sent: Feb 03, 2025 09:39 AM
From: STACY PICKETT
Subject: CHAR vs VARCHAR2 in Oracle using Gen
Lynn ..... Thanks for the very in depth look at how Gen would generate code in this situation. The "to VARVHAR2" and "to CHAR" functions would seem to be very handy if we decide to use the recommended VARCHAR2 data types for the columns. We have 10,000+ text attributes in our fully implemented data model so a function like will definitely be a time saver.
We have 700+ batch jobs in the application which will be generated as Windows C code with no display. and 1700+ application servers which will be implemented in C#.
If I'm reading it correctly, it seems from the examples you shared that the C code **** is only looking at the table definitions in the Data Structure diagram and the C# **** is taking the entity definition information from ERD into account also. That seems like it might need a fix so they generate the same or similarly with the same results. Thanks for opening the case for me. Will be interested in seeing what support says in this situation.
Ideally, if we end up deciding to implement all or nearly all the text attributes as VARCHAR2, then hopefully we can also find a quick solution to turn on the attribute property of "Varying Length" for all of the text attributes so the ERD and Data Structure objects will be in sync.
------------------------------
Thanks
Original Message:
Sent: Feb 02, 2025 09:32 PM
From: Lynn Williams
Subject: CHAR vs VARCHAR2 in Oracle using Gen
Hi Stacy,
I was just putting this reply together when Su responded :)
From a Gen perspective to avoid encountering any problems we would normally advise to have the attribute format defined in the Data Structure List (used to generate code) matching the target database column type which it will be by default if Gen is used to also generate the DDL of course.
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.
Example screenshots below.
I did do a quick generated code test for ORACLE targeting C and C# (.NET) before and after using "To VARCHAR2" on a copy of the sample model.
In Window Code Generation generating for DBMS (TD)=ORACLE:
- For C, the generated MODEMP.sqc file for MODIFY_EMPLOYEE did change from "static char" to "static VARCHAR".
- However for C# (.NET that you will be using) the generated code did not change. The code does show the information about whether the field is defined as varying length or not. All the EMPLOYEE entity text attributes are actually fixed length and only when I changed one to varying length did the generated code for C# change. (This difference to C probably relates to the way a C# application would be connecting to ORACLE via ADO.NET at runtime and there is no precompile done at build time.)
So it appears using "To VARCHAR2" would not help for C# generated code.
Coming back to your original question.
I have since been running some tests where I generated DDL for the sample model after using "To VARCHAR2" and created the new Oracle schema with it.
Then I reverted to the original model with CHAR and generated code for it for both C and C#.
Initial testing indicates both applications function OK.
When creating an EMPLOYEE using the C application connection direct to Oracle I do see the NAME get stored with the length I used and not padded to full 30 character length.
When creating an EMPLOYEE using the C# application connecting via an ODBC DSN (System.Data.Odbc) to Oracle I see the NAME get stored with the full 30 character length.
*****
SQL> desc EMPLOYEE
Name Null? Type
----------------------------------------- -------- ----------------------------
NUMBER0 NOT NULL NUMBER(6)
NAME NOT NULL VARCHAR2(30)
ADDRESS_LINE_1 VARCHAR2(30)
ADDRESS_LINE_2 VARCHAR2(30)
ADDRESS_LINE_3 VARCHAR2(30)
ADDRESS_LINE_4 VARCHAR2(30)
ADDRESS_LINE_5 VARCHAR2(30)
HOME_PHONE VARCHAR2(16)
WORK_PHONE VARCHAR2(16)
PAY_TYPE VARCHAR2(2)
PAY_RATE NUMBER(10,2)
MAIL_STOP VARCHAR2(4)
SERVICE_DATE DATE
FULL_TIME_STATUS NOT NULL VARCHAR2(2)
FK_DIVISIONNUMBER0 NUMBER(3)
FK_DEPARTMENTNUMBER0 NUMBER(4)
FK0DEPARTMENTNUMBER0 NUMBER(4)
FK_TEAMFK_PROJECTNUMBER0 NUMBER(3)
FK_TEAMNUMBER0 NUMBER(2)
SQL> select name, length(name) from employee;
NAME LENGTH(NAME)
------------------------------ ------------
EMP_FROM_C 10
EMP_FROM_C# 30
SQL>
*****
I will research further on this with our internal SMEs.
As Su mentioned a support case may be more appropriate and after I collect further info I will probably create one for you and contact you again from there.
Regards
Lynn
------------------------------
Lynn Williams
Senior Principal Support Engineer
Broadcom Software
Australia
Original Message:
Sent: Feb 02, 2025 09:10 PM
From: Su Brude
Subject: CHAR vs VARCHAR2 in Oracle using Gen
Hi Stacy, I am sure on Monday you will get a few more responses from the Gen team on this subject. I would encourage you to reach out to our support team and open a ticket. They can help guide you through this scenario that you are facing!
------------------------------
Su Brude
Sr Principle Product Manager -- Gen
Broadcom
Original Message:
Sent: Jan 31, 2025 10:52 AM
From: STACY PICKETT
Subject: CHAR vs VARCHAR2 in Oracle using Gen
The shop I'm at is about to undertake moving our application developed in Gen off of mainframe COBOL / DB2 onto Windows .NET / Oracle. We will be using Gen to regenerate the code for the new architecture. One of the first questions is CHAR vs VARCHAR2 columns in Oracle and how Gen will generate code for them. Most of our text domain attributes are not marked as varying length in the ERD and will create DDL for the columns as CHAR data types. The recommendation in Oracle is to mostly use VARCHAR2 column data types instead of CHAR. The question is --> If we leave the attributes and column definitions in the model defined as they are (text, fixed length) and define the columns in the database tables 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 **** 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 ?
------------------------------
Thanks
------------------------------