My company has a Cobol application written to support multiple relational databases. It uses fixed length working storage variables within the program and currently uses CHAR datatypes across all databases. Our Oracle customers would prefer to use VARCHAR2 datatypes because of the space savings. We attempted to use the Oracle Pro/Cobol precompiler PICX=VARCHAR2 option to provide this option. (Basically this option removes trailing spaces from all data to be inserted/updated to VARCHAR2 datatypes and pads with spaces on retireval of data.) However we found that this option turns data of all spaces into a NULL, which violates our NOT NULL column constraints. This behaviour is contrary to SQL92 standard that NULL values and zero-length or empty strings are not equivalent. It is also contrary to Oracle's own documentation. However Oracle aren't interested in making their product compliant with the standard so I need to find another solution. I also don't have the option to change the SQL to do my own RTRIM and RPAD.
I would be very interested to know anybody else has faced a similar challenge and how they might have solved it.
All suggestions welcomed. Thanks.
I would be very interested to know anybody else has faced a similar challenge and how they might have solved it.
All suggestions welcomed. Thanks.