OutSystems platform using Oracle Database – VARCHAR2 to CLOB

door Marcel Lambrechts Tags: , ,
Quobell

When I was developing an application in OutSystems, I changed the size of one of the “Text” entity attributes of one of my entities from 500 to 5000. Service Studio just let me do this and there were no errors or warnings in TrueChange. Then I clicked the publish button: Database Upgrade Error

Different Oracle data types used for OutSystems “Text” data type

OutSystems will use Oracle’s VARCHAR2 data type for “Text” entity attributes up to (including) 2000 characters, and it will use Oracle’s CLOB data type for “Text” attributes with a length of 2001 characters and more.

OutSystems Data Type Length Oracle Data Type Length
Text <= 2000 VARCHAR2 Length in OutSystems
Text > 2000 CLOB

Getting into trouble

A problem arises when you alter the length of your “Text” entity attribute in such a way that the data type of the column in the Oracle database needs to be changed from VARCHAR2 to CLOB or vice versa. OutSystems will return an “Database Upgrade” Error when you publish.

The error you will get (when changing the “Text” attribute’s length from <= 2000 to a length of > 2000) is:

Column ‘<database tablename>.<column name>’ exists in database as Text (VARCHAR2(0)) but the new version is defined as Text (CLOB). Database VARCHAR2(0) columns can not be changed to CLOB. This is because the column is now defined with a length greater than 2000.

How to solve this situation

The procedure I write in this paragraph is based on a pretty simple model, where there is no index defined on the “text” column and the table is pretty small (does not contain a lot of data). If your table is not that basic, it might be necessary to rebuild indexes or use another method for copying the data from one column to another. Also make sure you have a good database backup before playing with the data Outside of the OutSystems system and dropping table columns.

To be able to change the length of the “Text” attribute, you can use the following procedure.

What you will need:

  • Being able to publish the application in Service Studio
  • Access to OSADMIN schema in the Oracle database

OutSystems Service Studio

  1. Rename the entity attribute
  2. Set the length of the entity attribute to the required value
  3. Publish the application

The above actions will create a new column in the database table with the new name of the renamed entity attribute with the correct (CLOB) data type. No data will be available in this new column. The table column with the original name of the entity attribute still exists in the table, containing the original data.

The application will be published, but you will receive an “Database Integrity Suggestion” (informational) message like this:

Inconsistent database table and entity definitions: column ‘<database tablename>.<old column name>’ exists in database, but there is no corresponding attribute in entity ‘<OutSystems Entity Name>’.

With this message (that you will get everytime you publish the application) OutSystems tells you that there is a column in the table that is not coupled to an entity attribute. By renaming the entity attribute, we implicitly created a new table column that was coupled to the renamed entity attribute, leaving the old table column uncoupled.

SQL*Plus/SQL Developer

In SQL Developer/SQL*Plus or any other tool that will allow you to connect directly to the database, execute the following steps:

  1. Copy the original data from the old column to the new column by using the following UPDATE statement:
    UPDATE osadmin.<tablename> SET <new columnname> = <old columnname> WHERE <old columnname> != ‘ ‘;
    COMMIT;
  2. Make sure all data is copied correctly by querying the table directly or by using your application
  3. Remove the old column from the database table
    ALTER TABLE osadmin.<tablename> DROP COLUMN <old columnname>;