OutSystems platform using the Oracle database

VARCHAR2 to CLOB (author: Marcel Lambrechts)

inSystems databases

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>;

Share this

Contact

Heb je vragen of opmerkingen? Aarzel niet om contact op te nemen. Dat kan via de algemene contactgegevens, rechtstreeks met de genoemde contactpersoon op een van de detailpagina’s, of via onderstaande knop.

7 april 2025

VoxxedDays Amsterdam 2025

Op donderdag 3 april 2025 bezocht Marcel Lambrechts de eerste VOXXEDDAYS in Amsterdam. Het was een dag vol interessante sessies, waarbij het soms moeilijk was een keuze te maken welke sessie te volgen.

12 maart 2025

Bijna 30 jaar ervaring met Oracle

inSystems, fka Quobell, en de software development tools van Oracle hebben een lange historie samen. Voordat Quobell in 1996 het levenslicht zag, realiseerden de oprichters al applicaties met de producten van Oracle.

Opdrachten uitgevoerd voor

Ahold
Darling
Gemeente Gouda
Provincie Utrecht
VTTI
Oracle University
Ziggo
Greenchoice
Alphabet
Stedin
Arval BNP Paribas
Multi Tank Card
UBR Uitvoeringsorganisatie Bedrijfsvoering Rijk
Fokker
DICTU
Syntrus Achmea Real Estate & Finance
Politie
Allianz
Rechtspraak
Altran
inSystems KLM
CB Logistics
IBM
Rendo
TU Eindhoven
ING Bank
Gemeente Den Haag
Van Iperen Groeispecialisten
Cannock
ASR Nederland
RIGD LOXIA
GVB
SVB
VION Food Group
VAA ICT Consultancy
TenneT
Universiteit Tilburg
Leaseplan
CBR
BMW
Nederlandse Spoorwegen
JUVA
MN Services
Thales
RAVU
Bank Mendes Gans
Eneco
Telegraaf Media Groep
PC Uitvaart
Rabobank
Ministerie van Economische Zaken, Landbouw & Innovatie
WVDB Adviseurs Accountants
Gemeente Apeldoorn